通用資料表運算式 (SQL Common Table Expression)

CTE (Common Table Expression，通用資料表運算式) 是一種暫時性的查詢結果集，它只在該 SQL 語句執行期間存在。CTE 讓你可以先定義一個暫時的「虛擬資料表」，然後在後續的查詢中參照它。

使用 CTE 可以讓複雜的 SQL 查詢更易讀、更容易維護。

WITH 語法 (SQL WITH Syntax)

WITH cte_name AS (
    SELECT column_name(s)
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

CTE 基本用法 (Example)

假設我們有一個 employees 資料表：

使用 CTE 先計算各部門平均薪資，再找出高於平均的員工：

WITH dept_avg AS (
    SELECT Dept, AVG(Salary) AS AvgSalary
    FROM employees
    GROUP BY Dept
)
SELECT e.Name, e.Dept, e.Salary, d.AvgSalary
FROM employees e
JOIN dept_avg d ON e.Dept = d.Dept
WHERE e.Salary > d.AvgSalary;

查詢結果：

多個 CTE

可以在同一個 WITH 子句中定義多個 CTE，用逗號分隔：

WITH 
    cte1 AS (
        SELECT ...
    ),
    cte2 AS (
        SELECT ...
    )
SELECT * FROM cte1 JOIN cte2 ON ...;

CTE vs 子查詢

相較於子查詢 (Subquery)，CTE 有以下優點：

遞迴 CTE (Recursive CTE)

CTE 可以參照自己，形成遞迴查詢，適合處理階層式資料如組織架構、分類樹等。

WITH RECURSIVE cte_name AS (
    -- 起始查詢 (Anchor)
    SELECT ...
    UNION ALL
    -- 遞迴查詢
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

遞迴 CTE 範例：產生數字序列

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

查詢結果：