SQL Server CTE 遞歸查詢完整指南：語法、執行機制與實戰案例

遞歸 CTE 三要素

1. 定位點成員：不引用 CTE 本身的查詢，構建基準結果集 T0。
2. 遞歸成員：引用 CTE 的查詢，輸入 Ti 輸出 Ti+1，由 UNION ALL 連接。
3. 終止檢查：隱式，當遞歸成員返回空集時停止。

執行流程圖解

1. 拆分定位點與遞歸成員。
2. 執行定位點 → 產生 T0。
3. 執行遞歸成員，以 T0 為輸入產生 T1。
4. 重複步驟 3，直到輸入為空。
5. 最終結果 = T0 ∪ T1 ∪ ... ∪ Tn（UNION ALL）。

實戰案例：查詢費用項目樹（ItemId=2 及所有子節點）

建表與測試數據

CREATE TABLE dbo.Co_ItemNameSet (
    ItemId INT NULL,
    ParentItemId INT NULL,
    ItemName NCHAR(10) NULL
);

INSERT INTO dbo.Co_ItemNameSet VALUES
(2,0,N'管理費用'),(3,0,N'銷售費用'),(4,0,N'財務費用'),(5,0,N'生產成本'),
(35,5,N'材料'),(36,5,N'人工'),(37,5,N'制造費用'),
(38,35,N'原材料'),(39,35,N'主要材料'),(40,35,N'間輔材料'),
(41,36,N'工資'),(42,36,N'福利'),
(43,2,N'管理費用子項'),(113,43,N'管理費用子項的子項');

傳統寫法（臨時表 + WHILE 迴圈）

需建兩張臨時表、手動維護層級、循環刪除/插入，代碼超 50 行，易出錯、難維護。

遞歸 CTE 一行搞定

DECLARE @RootId INT = 2;

WITH Co_ItemNameSet_CTE (ItemId, ParentItemId, ItemName, [Level]) AS (
    -- 定位點：根節點
    SELECT ItemId, ParentItemId, ItemName, 1 AS [Level]
    FROM dbo.Co_ItemNameSet
    WHERE ItemId = @RootId
    
    UNION ALL
    
    -- 遞歸：子節點關聯父節點
    SELECT c.ItemId, c.ParentItemId, c.ItemName, ct.[Level] + 1
    FROM dbo.Co_ItemNameSet c
    INNER JOIN Co_ItemNameSet_CTE ct ON c.ParentItemId = ct.ItemId
)
SELECT * FROM Co_ItemNameSet_CTE;

結果

輸出 5 行：管理費用(L1) → 管理費用子項(L2) → 管理費用子項的子項(L3) 等，完美呈現層級結構。

效能與限制提示

• 預設最大遞歸層數 100，可用 OPTION (MAXRECURSION 0) 移除限制（謹防無限遞歸）。
• 大層級數據建議加索引：CREATE INDEX IX_ParentItemId ON Co_ItemNameSet(ParentItemId);
• 遞歸 CTE 在執行計劃中顯示為 Lazy Spool，記憶體壓力較大，超萬層時考慮改用 WHILE + 臨時表 分批處理。

總結

遞歸 CTE 是處理層級數據的「瑞士軍刀」：語法標準、邏輯清晰、維護成本低。掌握「定位點 + UNION ALL + 遞歸成員」三部曲，即可輕鬆搞定組織架構、BOM 展開、無限分類等經典場景。