CTE (Common Table Expression)

 CTE 是一個「暫存」且「具名」的結果集合。

-- 定義 CTE 的 名稱 和 欄位
WITH sampleCTE (id, name, phoneNumber, age)  
AS
-- 定義 CTE 的 Query  
(  
    SELECT
			id,
			name,
			phoneNumber,
			date_part('year', NOW()) - date_part('year', dob) AS age  
    FROM member
    WHERE phoneNumber IS NOT NULL  
)
-- 使用 CTE
SELECT *  
FROM sampleCTE
WHERE age >= 18 

CTE 會暫時儲存 AS 括號中的 Query 結果，

用在同一個執行中 SELECT 、 INSERT 、 UPDATE 、 DELETE，

或是 CREATE VIEW 的 SELECT 上。

也可以在定義 CTE 的 Query 中使用，今天介紹的遞迴就是一個例子。

遞迴查詢 (Recursive Query)

定義遞迴查詢 CTE 的概念大致上是:

1. 取得所有資料作為第一層
2. 資料 INNER JOIN CTE 取得下一層的資料(INNER JOIN的條件會根據下面兩點，稍微有點不同)
   • 儲存結構 (儲存 Parent 或是 Child)
   • 查詢目標 (查詢 Parent 或是 Child)
3. 步驟 1 UNION 步驟 2

範例

假設資料為某家族的族譜，查詢目標為取得 Grandpa 的所有子孫

預期結果

使用 MSSQL

使用 MSSQL 的話需要注意: 遞迴的部分必須使用 UNION ALL

WITH ChildrenCTE(Name, ChildName, ChildLevel) AS   
(  
	-- 取得所有資料 (每個人的孩子) 作為第一層
    SELECT Name, ChildName, 1 AS ChildLevel
    FROM FamilyTree
    UNION ALL
	-- 遞迴取得每個人的子孫(不含孩子)
	-- CTE 的查詢結果: 選取孫子(孩子的孩子)
    SELECT FT.Name, Children.ChildName, Children.ChildLevel + 1  
    FROM FamilyTree AS FT
		-- 使用 CTE 查詢: 父母名稱為資料中孩子名稱的資料
        INNER JOIN ChildrenCTE AS Children
        ON FT.ChildName = Children.Name
)  
SELECT *   
FROM ChildrenCTE
WHERE Name = 'Grandpa'
ORDER BY ChildLevel 

使用 PostgreSQL

跟 MSSQL 的做法差不多，只是要在 CTE 的名字前面加上 RECURSIVE。
且可以只使用 UNION ， 不一定要 UNION ALL。

WITH RECURSIVE ChildrenCTE(Name, ChildName, ChildLevel) AS   
(  
	-- 取得所有資料 (每個人的孩子) 作為第一層
    SELECT Name, ChildName, 1 AS ChildLevel
    FROM FamilyTree
    UNION
	-- 遞迴取得每個人的子孫(不含孩子)
	-- CTE 的查詢結果: 選取孫子(孩子的孩子)
    SELECT FT.Name, Children.ChildName, Children.ChildLevel + 1  
    FROM FamilyTree AS FT
		-- 使用 CTE 查詢: 父母名稱為資料中孩子名稱的資料
        INNER JOIN ChildrenCTE AS Children
        ON FT.ChildName = Children.Name
)  
SELECT *   
FROM ChildrenCTE
WHERE Name = 'Grandpa'
ORDER BY ChildLevel