SQL CTE

SQL CTE

作者:

CTE 簡介 (SQL CTE)

這篇 SQL CTE 教學將帶你認識 CTE（Common Table Expression，通用表格運算式）的基本概念與用法。CTE 透過 WITH 關鍵字定義一個暫時的結果集，讓你可以在後續的查詢中直接引用，大幅提升複雜查詢的可讀性與維護性。

你可以把 CTE 想像成一個「暫時的虛擬表」，它只在當次查詢中有效，不會真正儲存在資料庫中。相較於 Subquery（子查詢），CTE 將複雜的子查詢抽出來獨立命名，不僅更容易閱讀，還能在同一查詢中被多次引用。

以下是 Subquery 與 CTE 的主要差異：

比較項目SubqueryCTE
定義位置嵌套在查詢內部用 WITH 定義在查詢之前
可讀性複雜時較難閱讀結構清晰，易於理解
可重複引用不行，需重複寫可以在同一查詢中多次引用
遞迴支援不支援支援遞迴 CTE

CTE 語法 (SQL CTE Syntax)

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

說明：

關鍵字說明
WITH用來定義 CTE 的開頭關鍵字
cte_name自訂的 CTE 名稱，可以在後續查詢中引用
AS (…)CTE 的查詢定義，括號內放入 SELECT 查詢
SELECT … FROM cte_name主查詢，引用 CTE 的結果集進行後續操作

CTE 用法 (Example)

假設我們有一個員工資料表 employees 如下（若需了解如何建立資料表，請參考 CREATE TABLE 教學）：

EmployeeIDNameDepartmentSalary
1張一業務部35000
2王二資訊部42000
3李三業務部38000
4趙四資訊部45000
5陳五人資部40000
6林六業務部36000
7黃七資訊部48000

基本 CTE 用法

使用 CTE 查詢各部門的平均薪資，並篩選出平均薪資超過 40000 的部門。

WITH dept_avg AS (
    SELECT Department, AVG(Salary) AS avg_salary
    FROM employees
    GROUP BY Department
)
SELECT Department, avg_salary
FROM dept_avg
WHERE avg_salary > 40000;

查詢結果如下：

Departmentavg_salary
資訊部45000

在這個範例中，我們先用 CTE 定義了 dept_avg，計算各部門的平均薪資，然後在主查詢中直接引用 dept_avg 來篩選結果。這樣的寫法比嵌套子查詢更容易理解。

CTE 搭配 JOIN

CTE 的結果集可以像一般資料表一樣與其他表進行 JOIN。例如，先用 CTE 計算各部門的平均薪資，再與原始資料表 JOIN，找出薪資高於部門平均的員工。

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

查詢結果如下：

NameDepartmentSalaryavg_salary
李三業務部3800036333
趙四資訊部4500045000
黃七資訊部4800045000

透過 CTE，我們將「計算部門平均薪資」的邏輯抽出來獨立命名，主查詢只需專注在篩選條件，程式碼更加清晰。

多個 CTE 定義

你可以在同一個 WITH 子句中定義多個 CTE，各個 CTE 之間用逗號分隔。後方的 CTE 可以引用前方已定義的 CTE。

WITH dept_stats AS (
    SELECT Department,
           COUNT(*) AS headcount,
           AVG(Salary) AS avg_salary
    FROM employees
    GROUP BY Department
),
high_salary_depts AS (
    SELECT Department, avg_salary
    FROM dept_stats
    WHERE avg_salary > 38000
)
SELECT * FROM high_salary_depts;

查詢結果如下：

Departmentavg_salary
資訊部45000
人資部40000

第一個 CTE dept_stats 計算各部門的人數與平均薪資，第二個 CTE high_salary_depts 引用前者的結果來篩選平均薪資超過 38000 的部門。這種層層拆解的方式讓查詢邏輯更容易理解。

遞迴 CTE (Recursive CTE)

CTE 最強大的功能之一就是支援遞迴查詢。遞迴 CTE 可以在定義中引用自己，適合用於處理階層式資料（如組織架構、樹狀結構）或產生序列資料。

遞迴 CTE 的基本結構包含兩個部分：

部分說明
錨點查詢（Anchor）遞迴的起始條件，不引用 CTE 自身
遞迴查詢（Recursive）引用 CTE 自身，每次執行會產生新的資料列
UNION ALL將錨點查詢與遞迴查詢的結果合併
WITH RECURSIVE cte_name AS (
    -- 錨點查詢（起始條件）
    SELECT ...
    UNION ALL
    -- 遞迴查詢（引用自己）
    SELECT ...
    FROM cte_name
    WHERE 終止條件
)
SELECT * FROM cte_name;

注意：在 MySQL 中使用 WITH RECURSIVE，而 SQL Server 只需要 WITH 即可支援遞迴。

產生數字序列

使用遞迴 CTE 產生 1 到 5 的數字序列：

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

查詢結果如下：

n
1
2
3
4
5

錨點查詢 SELECT 1 AS n 產生第一筆資料 n=1，遞迴查詢每次將 n 加 1，直到 n < 5 條件不成立時停止。

階層式資料查詢

假設我們有一個員工與主管的階層資料表 staff：

EmployeeIDNameManagerID
1王總NULL
2李經理1
3張組長2
4陳員工3
5林員工2

使用遞迴 CTE 查詢王總下屬的所有員工及其層級：

WITH RECURSIVE org_tree AS (
    -- 錨點：從王總開始
    SELECT EmployeeID, Name, ManagerID, 0 AS level
    FROM staff
    WHERE ManagerID IS NULL
    UNION ALL
    -- 遞迴：找出下屬員工
    SELECT s.EmployeeID, s.Name, s.ManagerID, ot.level + 1
    FROM staff s
    JOIN org_tree ot ON s.ManagerID = ot.EmployeeID
)
SELECT EmployeeID, Name, level
FROM org_tree
ORDER BY level, EmployeeID;

查詢結果如下：

EmployeeIDNamelevel
1王總0
2李經理1
3張組長2
5林員工2
4陳員工3

遞迴 CTE 從最高層的王總（level=0）開始，每次遞迴找出下一層的員工，直到沒有更多下屬為止。這種查詢在處理組織架構、分類階層等情境非常實用。

CTE 與 Subquery 的比較

同一個查詢通常可以用 CTE 或 Subquery 實現。以下用「查詢平均薪資超過 40000 的部門」來比較兩種寫法。

使用 Subquery：

SELECT Department, avg_salary
FROM (
    SELECT Department, AVG(Salary) AS avg_salary
    FROM employees
    GROUP BY Department
) AS dept_avg
WHERE avg_salary > 40000;

使用 CTE：

WITH dept_avg AS (
    SELECT Department, AVG(Salary) AS avg_salary
    FROM employees
    GROUP BY Department
)
SELECT Department, avg_salary
FROM dept_avg
WHERE avg_salary > 40000;

兩種寫法的結果完全相同，但 CTE 將子查詢抽出來獨立命名，當查詢變得複雜時，CTE 的可讀性明顯更好。特別是當同一個子查詢需要被多次引用時，CTE 可以避免重複撰寫相同的查詢。

CTE 使用注意事項

注意事項說明
作用範圍CTE 只在緊接其後的單一 SQL 敘述中有效
不儲存資料CTE 不會建立實際的資料表，只是暫時的結果集
遞迴限制遞迴 CTE 需設定終止條件，否則可能無限迴圈。各資料庫預設遞迴上限不同
資料庫支援MySQL 8.0+、PostgreSQL、SQL Server、Oracle、SQLite 3.8.3+ 等皆支援

延伸閱讀