SQL Server 2008 CTE 遞歸查詢產生節點完整路徑

前言

在處理階層式資料（如組織架構、分類目錄、BOM 結構）時，常需取得每個節點從根節點到自身的完整路徑。SQL Server 2008 引入的 Common Table Expression (CTE) 配合遞歸查詢，能以簡潔的 T-SQL 完成此需求，本文示範如何利用 CTE 產生節點的 FullPath。

範例資料準備

DECLARE @tbl TABLE (Id int, ParentId int);
INSERT INTO @tbl (Id, ParentId) VALUES
(0, NULL), (8, 0), (12, 8), (16, 12),
(17, 16), (18, 17), (19, 17);

遞歸 CTE 查詢

WITH abcd AS (
    -- Anchor: 根節點
    SELECT id, ParentId, CAST(id AS VARCHAR(100)) AS [Path]
    FROM @tbl
    WHERE ParentId IS NULL

    UNION ALL

    -- Recursive member: 遞歸向下查找子節點
    SELECT t.id, t.ParentId,
           CAST(a.[Path] + ',' + CAST(t.ID AS VARCHAR(100)) AS VARCHAR(100)) AS [Path]
    FROM @tbl AS t
    JOIN abcd AS a ON t.ParentId = a.id
)
SELECT Id, ParentId, [Path]
FROM abcd
WHERE Id NOT IN (SELECT ParentId FROM @tbl WHERE ParentId IS NOT NULL);

執行結果

Id  ParentId  Path
--- --------  ----------------------
18  17        0,8,12,16,17,18
19  17        0,8,12,16,17,19

說明

• Anchor 部分：選取根節點（ParentId IS NULL），初始化路徑為自身 ID。
• Recursive 部分：以 JOIN 方式將子節點接在父節點路徑之後，並以逗號分隔。
• 最終篩選：WHERE Id NOT IN (...) 僅保留葉節點（無子節點者），若需所有節點路徑可移除此條件。

補充

SQL Server 2008 亦提供 HierarchyID 資料型別，能更高效地管理與查詢階層資料，後續文章將詳細介紹。