使用触发器(给要查询的表添加触发器)
假如有表single、mcq、judge
要把这三个表的数据查询出来并放到一个新表中
新表:lists
步骤1:创建一个新表,并在新表中插入字段,如下:
步骤2 :给single、mcq、judge几个表分别添加触发器(添加触发器的作用:在single、mcq、judge中添加数据时,也会在新表lists中同时添加数据)
解释:after:在single插入数据之后,再在新表lists中添加数据
insert into (id)values (NEW.id)中
id为要在新表中添加的字段名(single表中的id)
NEW.id:新生成的id
注意:这两个id都要与要查询的表(例如:single)中的id名称对应
步骤3:查询语句为
select lists.id,single.`subject`,single.questionType,single.question ,single.score,single.difficulty,single.createtime
from lists
INNER JOIN
single
on single.id=lists.sid
UNION
select lists.id,mcq.`subject`,mcq.questionType,mcq.question ,mcq.score,mcq.difficulty,mcq.createtime
from lists
INNER JOIN
mcq
on mcq.id=lists.mid
union
select lists.id,judge.`subject`,judge.questionType,judge.question ,judge.score,judge.difficulty,judge.createtime
from lists
INNER JOIN
judge
on judge.jid=lists.jid
ORDER by id desc