12、关于临时表和with as子查询部分

小马嘚儿驾

尽可能做到活在当下

把复杂的关联子查询挑出来用临时表或with as进行封装(?不确定这里用封装该词是否正确 )。

目前对我来说是 增加了代码可读性，让难读的一大长篇代码变成几个盒子和主程序。 （当然可以用注释解决阅读性能）

一、临时表

1、创建临时表：

create temporary table table_name    -- 运行该代码建立临时表，才能使用该表
【表内容】；

2、删除临时表：

drop temporary table table_name;

3、关于临时表

临时表和永久表类似，是需要插入到数据库储存在磁盘的临时区中，只有数据库连接断开MySQL自动删除临时表，或者drop 掉，才会消失。
汇总维度相同时，巧用union，不用join。取各个临时表所需的字段，收集齐全后，一次group by。
适用于：有很多复杂的关联子表查询。

二、with as子查询部分（公共表达式CTE）

语法：

with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),

1、基础认知

▶with as短语，也叫做子查询部分(subquery factoring)。并不是真正的临时表,查询结果保存在内存中。

▶ 后面必须直接紧跟使用 with as 的SQL语句，否则失效

▶ 适用于：为了增加代码可读性，且没有很多复杂的关联子查询。

▶可以让逻辑复杂的sql大大减少临时表的数量，提高多次重用效率，提升sql的可读性可维护性。【如可以对特定with子查询取个有意义的名字】

▶with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

▶特别地，对于union all比较有用，它在union all的不同部分，作为提供数据的部分。

因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

2、示例

查询所有课程成绩小于60分的同学的学号、姓名【该数据为下一章习题集中用到的数据】

一般方法 【纯粹为了举例，实际该方法非最简洁】：

select a.s_id,c.s_name 
    (select s_id,count(s_id) as acount from score    -- 1、每个学生小于60的课程数
     where s_score<60
     group by s_id ) as a  
inner join 
     (select s_id,count(s_id) as bcount from score   -- 2、每个学生考过的总课程数
      group by s_id) as b 
on a.s_id=b.s_id
inner join student as c on c.s_id=a.s_id            -- 3、连接学生表方便取姓名 
where acount=bcount

with封装 把a,b拿出来，让主查询简洁易读【记得做好注释】

with 
  (select s_id,count(s_id) as acount from score -- 1、每个学生小于60的课程数
   where s_score<60
   group by s_id ),
  (select s_id,count(s_id) as bcount from score -- 2、每个学生考过的总课程数
   group by s_id)