SQL专栏——sql语句的基础操作（十）CTE

引用致谢：

​​​https://www.bilibili.com/video/BV1H741137As?from=search&seid=14608163483986555242​​

CTE

CTE（common table expression）临时的结果集，可以为后面的select等语句使用

通俗的就是创建一个临时表，可供接下来的一次数据操作（查询）使用，不必太在意，它使用起来其实很方便，就是一个简单的语法句式

创建

有点类似与temp table,cte是个临时结果，所以自动删除

with table cte_name(colum_1,column_2,.....)
As
(cte query)      

使用

Select column_name from cte_name      

具体题目举例：

1.取得平均薪水最高的部门的部门编号

（1）普通sql实现

select * 
from (select avg(sal)
   as avg_sal,deptno 
   from emp 
   group by deptno) 
   as T2 
 where avg_sal 
   in (select max(avg_sal) as avg_sal 
   from (select avg(sal) as avg_sal,deptno 
        from emp group 
      by deptno)
   as T1);      

（2）cte实现

with
a as(select deptno,avg(sal) avg_sal from emp group by deptno),
b as(select max(a.avg_sal) max_sal from a)
select a.deptno from a join b on a.avg_sal=b.max_sal;      

2.取得平均薪水最高的部门的部门名称

（1）普通sql实现

select d.dname,avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno=d.deptno
group by d.dname
having avg(e.sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);      

(2)cte实现

with
a as(select deptno,avg(sal) avg_sal from emp group by deptno),
b as(select max(a.avg_sal) max_sal from a),
c as(select a.deptno from a join b on a.avg_sal=b.max_sal)
select  t.dname from dept t join c on t.deptno=c.deptno;      

3.求平均薪水的等级最低的部门的名称

（1）普通sql实现

(2)cte实现

如需调取cte的结果，我们的select等语句需要在cte后立刻使用