CTE 通用表達式（Common Table Expression）

什麼是 CTE？

比喻：CTE 就像先做好便當盒再組裝 🍱

與其把所有食材（子查詢）塞在一個鍋子裡煮， 不如先分別準備好每道菜（CTE），最後再組裝。

CTE 讓你用 WITH 先定義臨時結果集，然後在主查詢中引用。

基本語法

WITH honor_students AS (           -- ← 定義 CTE，取名 honor_students
    SELECT Name, Score
    FROM Students
    WHERE Score >= 90
)
SELECT *                           -- ← 主查詢，引用上面定義的 CTE
FROM honor_students
ORDER BY Score DESC;

逐行解析：

WITH honor_students AS (        -- WITH 開頭，定義一個叫 honor_students 的臨時表
    SELECT Name, Score          -- CTE 的內容：90 分以上的學生
    FROM Students
    WHERE Score >= 90
)                               -- CTE 定義結束
SELECT * FROM honor_students    -- 主查詢可以像用一般表一樣使用 CTE
ORDER BY Score DESC;

多個 CTE

WITH
-- CTE 1：每個科系的統計
dept_stats AS (
    SELECT
        Department,
        COUNT(*) AS cnt,
        AVG(Score) AS avg_score
    FROM Students
    GROUP BY Department
),
-- CTE 2：全校平均
school_avg AS (
    SELECT AVG(Score) AS overall_avg
    FROM Students
)
-- 主查詢：比較各系與全校平均
SELECT
    d.Department,
    d.cnt AS 人數,
    ROUND(d.avg_score, 1) AS 系平均,
    ROUND(s.overall_avg, 1) AS 校平均,
    CASE
        WHEN d.avg_score > s.overall_avg THEN '高於平均'
        ELSE '低於平均'
    END AS 評等
FROM dept_stats d
CROSS JOIN school_avg s          -- ← CROSS JOIN 讓每列都能看到全校平均
ORDER BY d.avg_score DESC;

CTE vs 子查詢

-- 子查詢版本（巢狀，難讀）
SELECT * FROM (
    SELECT Department, AVG(Score) AS avg_score
    FROM Students
    GROUP BY Department
) AS dept_stats
WHERE avg_score > (
    SELECT AVG(Score) FROM Students
);

-- CTE 版本（清楚，好讀）
WITH dept_stats AS (
    SELECT Department, AVG(Score) AS avg_score
    FROM Students
    GROUP BY Department
),
school_avg AS (
    SELECT AVG(Score) AS val FROM Students
)
SELECT d.*
FROM dept_stats d, school_avg s
WHERE d.avg_score > s.val;

遞迴 CTE

遞迴 CTE 可以處理階層結構（組織圖、分類樹等）。

-- 員工組織圖
WITH RECURSIVE org_chart AS (
    -- 起始條件：找到最高主管（沒有 ManagerId）
    SELECT Id, Name, ManagerId, 1 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- 遞迴條件：找每個人的下屬
    SELECT e.Id, e.Name, e.ManagerId, oc.Level + 1
    FROM Employees e
    INNER JOIN org_chart oc ON e.ManagerId = oc.Id
)
SELECT
    REPEAT('  ', Level - 1) || Name AS 組織圖,  -- 縮排顯示
    Level AS 層級
FROM org_chart
ORDER BY Level, Name;

結果：

 組織圖          | 層級
-----------------+------
 張總經理         |    1
   李副總         |    2
   王副總         |    2
     陳經理       |    3
     林經理       |    3
       小明       |    4

CTE 的優點