先将结果查询到临时表中,然后再从临时表中将所需要的字段提出,好处代码清晰,执行效率高!
- 基于with查询
例子有三张表,分别将三张表的结果存到临时表aa,bb,cc,然后将三张表组合查出需要的结果
with aa as (select * from student), bb as (select * from score),cc as (select * from class) select aa.name,age,cc.name from bb,aa,cc where aa.id=bb.id and aa.class=cc.id
- 基于wih插入语句
insert into 表名 上面的with 语句
注:切记不可为with语句加括号