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後立刻使用