Recursive CTE 遞迴查詢 (SQL Recursive Common Table Expression)

遞迴 CTE 是一種可以參照自身的 CTE，特別適合處理階層式資料（如組織架構、分類目錄、樹狀結構）和序列生成。

遞迴 CTE 語法 (Syntax)

WITH RECURSIVE cte_name AS (
    -- 錨點成員 (Anchor Member)：起始查詢，定義初始資料集
    SELECT columns FROM table WHERE initial_condition

    UNION ALL

    -- 遞迴成員 (Recursive Member)：參照 CTE 自身，定義遞迴邏輯
    SELECT columns FROM table
    JOIN cte_name ON recursive_condition
    WHERE termination_condition
)
SELECT * FROM cte_name;

遞迴 CTE 的兩個部分：

1. 錨點成員 (Anchor)：定義遞迴的起點，只執行一次
2. 遞迴成員 (Recursive)：參照 CTE 自身，重複執行直到沒有新結果

💡 注意： SQL Server 不需要 RECURSIVE 關鍵字，直接使用 WITH 即可進行遞迴查詢。

範例 1：查詢組織階層

employees 資料表：

範例： 從總經理開始，查詢完整的組織階層。

WITH RECURSIVE org_hierarchy AS (
    -- 錨點：從最高層開始（manager_id 為 NULL）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 遞迴：找出下屬員工
    SELECT e.id, e.name, e.manager_id, oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT id, name, manager_id, level
FROM org_hierarchy
ORDER BY level, id;

結果：

範例 2：查詢特定員工的所有上級

從某位員工向上追溯，找出其所有主管：

WITH RECURSIVE managers AS (
    -- 錨點：從指定員工開始
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 4  -- 陳工程師

    UNION ALL

    -- 遞迴：往上找主管
    SELECT e.id, e.name, e.manager_id, m.level + 1
    FROM employees e
    JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

結果：

範例 3：產生數字序列

遞迴 CTE 也可以用來產生數字序列：

WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n FROM numbers;

結果： 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

範例 4：展開日期範圍

產生指定日期範圍內的所有日期：

-- MySQL 語法
WITH RECURSIVE date_range AS (
    SELECT DATE('2024-01-01') AS date

    UNION ALL

    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < DATE('2024-01-07')
)
SELECT date FROM date_range;

結果： 2024-01-01, 2024-01-02, ..., 2024-01-07

範例 5：分類目錄的展開路徑

categories 資料表：

範例： 查詢每個分類的完整路徑。

WITH RECURSIVE category_path AS (
    SELECT id, name, parent_id,
           CAST(name AS VARCHAR(255)) AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           CONCAT(cp.path, ' > ', c.name)
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, path FROM category_path;

結果：

避免無限迴圈

⚠️ 警告： 遞迴 CTE 如果沒有正確的終止條件，會造成無限迴圈！

常見防護措施

1. 加入終止條件：

WHERE n < 100  -- 數字序列的上限
WHERE level < 10  -- 階層深度的上限

2. 設定最大遞迴次數：

-- SQL Server
SELECT * FROM recursive_cte
OPTION (MAXRECURSION 100);

-- MySQL / PostgreSQL：預設限制 1000 次

3. 使用 DISTINCT 或條件避免重複處理：

-- 追蹤已訪問的節點
WITH RECURSIVE cte AS (
    SELECT id, CAST(id AS VARCHAR(1000)) AS visited_ids
    FROM nodes WHERE ...

    UNION ALL

    SELECT n.id, CONCAT(c.visited_ids, ',', n.id)
    FROM nodes n
    JOIN cte c ON ...
    WHERE FIND_IN_SET(n.id, c.visited_ids) = 0  -- 避免重複
)

遞迴 CTE vs CONNECT BY

Oracle 傳統上使用 CONNECT BY 語法處理階層式查詢：

-- Oracle CONNECT BY 語法
SELECT id, name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- 等效的遞迴 CTE
WITH RECURSIVE org AS (...)

從 Oracle 11g R2 開始也支援標準的 WITH RECURSIVE 語法。

資料庫支援

相關主題

• CTE (WITH) - CTE 基礎
• Self Join - 另一種處理階層資料的方式
• 子查詢 - 巢狀查詢