Recursive CTE 遞迴 (SQL Recursive Common Table Expression)

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

遞迴 CTE 語法

WITH RECURSIVE cte_name AS (
    -- 錨點成員 (Anchor Member)：起始查詢
    SELECT columns FROM table WHERE condition
    
    UNION ALL
    
    -- 遞迴成員 (Recursive Member)：參照 CTE 自身
    SELECT columns FROM table
    JOIN cte_name ON ...
)
SELECT * FROM cte_name;

範例：查詢組織階層

假設有一個 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 * FROM org_hierarchy ORDER BY level, id;

結果：

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

WITH RECURSIVE managers AS (
    -- 錨點：從指定員工開始
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 4  -- 陳工程師
    
    UNION ALL
    
    -- 遞迴：往上找主管
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

範例：產生數字序列

遞迴 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

範例：展開日期範圍

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

WITH RECURSIVE date_range AS (
    SELECT DATE('2024-01-01') AS date
    UNION ALL
    SELECT DATE(date, '+1 day')
    FROM date_range
    WHERE date < DATE('2024-01-07')
)
SELECT date FROM date_range;

避免無限迴圈

遞迴 CTE 如果沒有正確的終止條件，可能會造成無限迴圈。大多數資料庫都有預設的遞迴深度限制：

-- SQL Server：設定最大遞迴次數
OPTION (MAXRECURSION 100);

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

各資料庫支援