SQL Server 遞歸 CTE (WITH AS) 查詢層級關系數據：組織架構、BOM、分類樹完整實戰

遞歸 CTE 語法結構

WITH RecursiveCTE AS (
    -- 錨點成員：根節點
    SELECT ID, ParentGUID, ECode, EName, 1 AS Level
    FROM Employee
    WHERE ParentGUID = '00000000-0000-0000-0000-000000000000'

    UNION ALL

    -- 遞歸成員：子節點
    SELECT e.ID, e.ParentGUID, e.ECode, e.EName, r.Level + 1
    FROM Employee e
    INNER JOIN RecursiveCTE r ON e.ParentGUID = r.ID
)
SELECT * FROM RecursiveCTE ORDER BY Level, ECode;

關鍵要素解析

• 錨點成員：返回層級 0 的行（根節點）
• 遞歸成員：引用 CTE 自身，通過 `ParentGUID = ID` 向下遍歷
• UNION ALL：合併結果，遞歸直到無匹配行
• MAXRECURSION：默認 100 層，深層結構需 `OPTION (MAXRECURSION 0)` 移除限制

實戰場景：員工組織架構樹

表結構：ID (PK)、ParentGUID (FK→ID)、ECode、EName

1. 完整樹形輸出（含層級、路徑）

WITH OrgTree AS (
    SELECT ID, ParentGUID, ECode, EName, 
           1 AS Level,
           CAST(EName AS NVARCHAR(MAX)) AS Path
    FROM Employee
    WHERE ParentGUID = '00000000-0000-0000-0000-000000000000'

    UNION ALL

    SELECT e.ID, e.ParentGUID, e.ECode, e.EName,
           o.Level + 1,
           o.Path + N' > ' + e.EName
    FROM Employee e
    INNER JOIN OrgTree o ON e.ParentGUID = o.ID
)
SELECT Level, Path, ECode, EName
FROM OrgTree
ORDER BY Path
OPTION (MAXRECURSION 0);

2. 查詢指定節點的所有下屬（含間接）

DECLARE @ManagerID UNIQUEIDENTIFIER = '5b82d74d-c5c7-4cbb-af28-0518bdc257d9'; -- 張志軍
WITH Subordinates AS (
    SELECT ID, ParentGUID, EName FROM Employee WHERE ID = @ManagerID
    UNION ALL
    SELECT e.ID, e.ParentGUID, e.EName
    FROM Employee e
    INNER JOIN Subordinates s ON e.ParentGUID = s.ID
)
SELECT * FROM Subordinates WHERE ID <> @ManagerID;

3. 查詢指定節點到根的路徑（向上遞歸）

DECLARE @EmpID UNIQUEIDENTIFIER = '6eccd0ec-11ad-45e3-98b6-2457cf61da2e'; -- 苗英叡
WITH UpwardPath AS (
    SELECT ID, ParentGUID, EName, 1 AS Level FROM Employee WHERE ID = @EmpID
    UNION ALL
    SELECT e.ID, e.ParentGUID, e.EName, u.Level + 1
    FROM Employee e
    INNER JOIN UpwardPath u ON e.ID = u.ParentGUID
)
SELECT * FROM UpwardPath ORDER BY Level DESC;

性能優化建議

• 在 ParentGUID 上建立非聚集索引 (INCLUDE ID, EName)
• 大數據量考慮 物化路徑 或 嵌套集合 模型替代遞歸查詢
• 避免在遞歸成員中使用複雜 JOIN/聚合
• 必要時用 OPTION (MAXRECURSION n) 防止無限遞歸併發癱瘓

擴展：HierarchyID 類型 (SQL 2008+)

-- 更高效的原生層級結構支持
ALTER TABLE Employee ADD HierarchyPath HIERARCHYID;
UPDATE Employee SET HierarchyPath = 
    CASE WHEN ParentGUID = '000...0' THEN hierarchyid::GetRoot()
         ELSE (SELECT HierarchyPath FROM Employee WHERE ID = ParentGUID).GetDescendant(NULL, NULL) END;

-- 查詢子樹
SELECT * FROM Employee 
WHERE HierarchyPath.IsDescendantOf((SELECT HierarchyPath FROM Employee WHERE ID = @ManagerID)) = 1;