SQL 遞歸 CTE 查詢樹狀結構：雙親節點模型查找所有子孫與祖先節點完整指南

關聯式資料庫儲存樹狀結構最常用「雙親節點模型」（Adjacency List）：每列存 parent_id 指向父節點。本文詳細示範如何用 遞歸 CTE (Common Table Expression) 一次查出指定節點的所有後代與祖先，並提供 MySQL 不支援 CTE 時的替代方案。

一、範例資料

CREATE TABLE category (
    id BIGINT PRIMARY KEY,
    parent_id BIGINT REFERENCES category(id),
    name VARCHAR(20)
);
INSERT INTO category VALUES 
(1, NULL, 'Root'),
(2, 1, 'Branch1'),
(3, 1, 'Branch2'),
(4, 3, 'SubBranch1'),
(5, 2, 'SubBranch2');

二、遞歸 CTE 查所有子孫節點

WITH RECURSIVE cte AS (
    -- 錨點成員：起始節點
    SELECT id, parent_id, name, 0 AS lvl
    FROM category
    WHERE id = 1  -- 這裡換成參數 @root_id

    UNION ALL

    -- 遞歸成員：找子節點
    SELECT c.id, c.parent_id, c.name, p.lvl + 1
    FROM category c
    JOIN cte p ON c.parent_id = p.id
)
SELECT * FROM cte ORDER BY lvl, id;

結果

id | parent_id | name       | lvl
---|-----------|------------|----
1  | NULL      | Root       | 0
2  | 1         | Branch1    | 1
3  | 1         | Branch2    | 1
5  | 2         | SubBranch2 | 2
4  | 3         | SubBranch1 | 2

三、遞歸 CTE 查所有祖先節點

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, 0 AS lvl
    FROM category
    WHERE id = 6  -- 目標葉節點

    UNION ALL

    SELECT c.id, c.parent_id, c.name, p.lvl + 1
    FROM category c
    JOIN cte p ON c.id = p.parent_id  -- 反向關聯
)
SELECT * FROM cte ORDER BY lvl DESC;

四、支援遞歸 CTE 的資料庫

• SQL Server 2008+ (WITH cte AS ...)
• PostgreSQL 8.4+ (WITH RECURSIVE)
• SQLite 3.8.3+ (WITH RECURSIVE)
• Oracle 11g R2+ (WITH cte AS ...)
• MySQL 8.0+ / MariaDB 10.2+ (WITH RECURSIVE)

五、MySQL 5.7 / 舊版替代方案

1. 固定層數 LEFT JOIN（最多 N 層，效能佳）

SELECT t1.name AS l1, t2.name AS l2, t3.name AS l3, t4.name AS l4
FROM category t1
LEFT JOIN category t2 ON t2.parent_id = t1.id
LEFT JOIN category t3 ON t3.parent_id = t2.id
LEFT JOIN category t4 ON t4.parent_id = t3.id
WHERE t1.id = 1;

2. 儲存程序遞歸（動態層數）

DELIMITER //
CREATE PROCEDURE GetDescendants(IN root_id BIGINT)
BEGIN
    CREATE TEMPORARY TABLE tmp (id BIGINT PRIMARY KEY, lvl INT);
    INSERT INTO tmp SELECT id, 0 FROM category WHERE id = root_id;
    
    REPEAT
        INSERT IGNORE INTO tmp 
        SELECT c.id, t.lvl + 1
        FROM category c
        JOIN tmp t ON c.parent_id = t.id
        WHERE t.lvl = (SELECT MAX(lvl) FROM tmp);
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    SELECT c.* FROM category c JOIN tmp t ON c.id = t.id ORDER BY t.lvl;
    DROP TEMPORARY TABLE tmp;
END//
DELIMITER ;

3. Closure Table（關係閉包表，寫入稍重、讀取極快）

另建 category_path (ancestor, descendant, depth)，每新增節點寫入所有祖先關係。查詢子孫：SELECT descendant FROM category_path WHERE ancestor = 1 AND depth > 0。

六、實用延伸查詢

-- 直接子節點數
SELECT c.*, (SELECT COUNT(*) FROM category c2 WHERE c2.parent_id = c.id) AS direct_children
FROM category c;

-- 完整路徑（SQL Server STRING_AGG / PostgreSQL string_agg）
WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, CAST(name AS VARCHAR(1000)) AS path, 0 AS lvl
    FROM category WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, p.path || ' > ' || c.name, p.lvl + 1
    FROM category c JOIN cte p ON c.parent_id = p.id
)
SELECT * FROM cte ORDER BY path;

七、效能提示

• 在 parent_id 建立索引：CREATE INDEX idx_category_parent ON category(parent_id);
• 大樹（>10 萬節點）遞歸 CTE 可能觸發 MAXRECURSION 限制（SQL Server 預設 100），可用 OPTION (MAXRECURSION 0) 移除上限。
• 頻繁讀取、極少寫入場景，Closure Table 或物化路徑效能最佳。

掌握遞歸 CTE 與替代方案，即可在任何主流資料庫優雅處理樹狀結構查詢，告別應用層遞歸的 N+1 問題。