SQL 遞歸 CTE 查詢樹狀結構所有父節點

表結構

CREATE TABLE Category (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT -- 0 表示根
);
-- 數據樣本
id | name | parent_id
2  | a    | 0
7  | e    | 2
10 | a1   | 7

標準遞歸 CTE (SQL:1999)

WITH RECURSIVE Ancestors AS (
    -- 錨點：目標節點
    SELECT id, name, parent_id, 0 AS level
    FROM Category
    WHERE name = 'a1'  -- 或 WHERE id = 10
    
    UNION ALL
    
    -- 遞歸：向上查找父節點
    SELECT c.id, c.name, c.parent_id, a.level + 1
    FROM Category c
    INNER JOIN Ancestors a ON c.id = a.parent_id
)
SELECT id, name, parent_id
FROM Ancestors
WHERE level > 0; -- 排除自身

輸出結果

id | name | parent_id
7  | e    | 2
2  | a    | 0

MySQL 8.0+ / PostgreSQL / SQL Server / Oracle 通用寫法

• MySQL 8.0+：WITH RECURSIVE（需去掉 RECURSIVE 關鍵字在某些版本）。
• SQL Server：WITH Ancestors AS (...) 無需 RECURSIVE。
• Oracle：WITH Ancestors AS (...) 支持 CONNECT BY 替代方案。

Oracle CONNECT BY 寫法

SELECT id, name, parent_id
FROM Category
START WITH name = 'a1'
CONNECT BY PRIOR parent_id = id
WHERE LEVEL > 1; -- 排除自身

性能優化

• 索引：CREATE INDEX idx_parent_id ON Category(parent_id);
• 避免無限遞歸：MAX_RECURSION_DEPTH (SQL Server) / CYCLE 子句 (Oracle 12c+)。
• 物化路徑：冗余存儲 path VARCHAR(500) (如 /2/7/10/)，查詢祖先變 LIKE '/2/7/%'，極快但寫入需維護。

**遞歸 CTE 是查詢樹狀結構的標準 SQL 解法**，可移植、可讀、性能足夠（百萬級節點毫秒級）。物化路徑適合「讀多寫少、頻繁查祖先」場景。