CTE 一般資料表運算式(Common Table Expression)又稱為子查詢因式分解，被使用的情境基本上有兩個，第一個是希望複雜SQL簡化可以消除 where 子句中對雜亂子查詢的需要），第二個是進行遞迴查詢，生活中最經典的例子就是家族族譜，爺爺的下一代有爸爸、叔叔、姑姑，爸爸的下一代有我、妹妹...延續下去，資料庫中常被舉的例子是員工的主管是誰，主管的主管又是誰...延續到沒有主管就是最頂的主管。

注意

• CTE 用逗號分隔
• 一個 WITH 子句中的後續 CTE 可以查詢任何數據庫表，或任何先前定義的 CTE。

範例 : 列出薪水高於平均的資料

WITH average_salary(avg_sal) AS
	(SELECT CAST(AVG(salary) AS INT) FROM employee)

SELECT *
FROM employee AS e, average_salary AS av
WHERE e.salary > av.avg_sal;

範例 : 查找誰的商店銷售額高於所有商店的平均銷售額

首先使用子查詢(Subqueries)的方式來獲得，會有兩個問題，第一點使用多個子查詢，有點難以閱讀，第二點重復使用相同的SQL多次，對性能不好。(如同第一點，對眼睛也不好)

-- 1. 每家商店的總銷售額
SELECT s.store_id, sum(cost) AS total_sales_per_store
FROM sales AS s
GROUP BY s.store_id;

-- 2. 求所有店舖的平均銷售額
SELECT CAST(AVG(total_sales_per_store) AS INT) AS avg_sales_for_all_stores
FROM (
	SELECT s.store_id, sum(cost) AS total_sales_per_store
	FROM sales AS s
	GROUP BY s.store_id
) AS x;

-- 3. 找出大於所有店家平均的店家總銷售額
SELECT *
FROM (SELECT s.store_id, SUM(cost) AS total_sales_per_store
			FROM sales AS s
			GROUP BY s.store_id) AS total_sales
JOIN (SELECT CAST(AVG(total_sales_per_store) AS INT) AS avg_sales_for_stores
			FROM ( SELECT s.store_id, SUM(cost) AS total_sales_per_store
						 FROM sales AS s
						 GROUP BY s.store_id) AS x) AS avg_sales
ON total_sales.total_saler_per_store > avg_sales.avg_sales_for_all_stores; 

使用CTE簡潔許多，也避免了重覆的Query。

-- 每家商店的總銷售額
WITH cte_total_sales (store_id, total_sales_per_store) AS
		(SELECT s.store_id, sum(cost) AS total_sales_per_store
		FROM sales AS s
		GROUP BY s.store_id),

		-- 所有店舖的平均銷售額
		cte_avg_sales (avg_sales_for_all_stores) AS
		(SELECT CAST(AVG(total_sales_per_store) AS INT) AS avg_sales_for_all_stores
		FROM cte_total_sales)

-- 大於所有店家平均的店家總銷售額
SELECT 
FROM cte_total_sales cts
JOIN cte_avg_sales cav
ON cts.total_sales.total_saler_per_store > cav.avg_sales.avg_sales_for_all_stores; 

遞迴查詢

遞迴查詢觀念較抽象，文字較難描述，我們可以參考 SQL Recursion with CTE 使用Excel解釋當我們執行遞迴查詢的時候到底發生了什麼事，遞迴查詢有一項重要的事情是遞迴需要被停止。(要給停止條件或是某種機制下會停止的語法，例如JOIN)

這個例子用where條件式停止

WITH RECURSIVE cte_table AS
(
    -- 第一次執行這邊
	SELECT 1 AS num
	UNION
    -- 第二次之後執行這邊
	SELECT num + 1 FROM cte_table WHERE num < 10
)
SELECT * FROM cte_table

這個例子使用join停止。(因為join不到就停止)，在這個CASE當中，我希望只找出學校在哪個縣哪個鄉鎮市，所以我使用遞迴次數來過濾想看到的內容。

drop table temp_table;
create table temp_table(id varchar(3) , pid varchar(3) , name varchar(10)); 

insert into temp_table values('001' , 0 , '雲林縣'); 
insert into temp_table values('002' , 0 , '嘉義縣'); 
insert into temp_table values('003' , '001' , '斗六市');  
insert into temp_table values('004' , '001' , '虎尾市') ; 
insert into temp_table values('005' , '001' , '斗南鎮'); 
insert into temp_table values('005' , '001' , '北港鎮'); 
insert into temp_table values('005' , '001' , '西螺鎮'); 
insert into temp_table values('005' , '001' , '土庫鎮'); 
insert into temp_table values('006' , '002' , '太保市') ; 
insert into temp_table values('007' , '002' , '朴子市');  
insert into temp_table values('008' , '002' , '布袋鎮') ; 
insert into temp_table values('009' , '002' , '大林鎮');  
insert into temp_table values('010' , '002' , '民雄鄉') ; 
insert into temp_table values('011' , '003' , '雲林科技大學');
insert into temp_table values('012' , '003' , '環球科技大學');
insert into temp_table values('013' , '004' , '虎尾科技大學');
insert into temp_table values('014' , '010' , '南華大學');
insert into temp_table values('015' , '010' , '中正大學');
insert into temp_table values('016' , '010' , '吳鳳科技大學');

with RECURSIVE children_cte as
(
    -- Anchor record
    -- 第一次執行這邊
		-- 也就是說這邊的資料是兒子
    select a.id,cast(a.name as varchar(100)),1 AS lvl 
    from temp_table a 
    
    union 
    
    -- Recursive Statement 
    -- 第二次之後執行這邊
    -- 也就說這邊是孫輩 (不包含兒子輩)
    select b.id,cast(c.name || '>' || b.name as varchar(100)) as name, lvl + 1 as lvl  
    from temp_table b 
    		-- 使用 CTE 查詢: 父母名稱為資料中孩子名稱的資料
    inner join children_cte c on c.id = b.pid
)
select id,name from children_cte
where lvl = '3';