萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 遞歸 CTE 完全指南:處理樹、圖、層次結構的標準化遞歸查詢模式

Common Table Expressions (CTE) 自 SQL Server 2005 引入,遞歸 CTE 更是處理層次化數據(組織架構、物料清單、分類樹、圖遍歷)的利器。本文從語法結構、執行邏輯、終止條件、MAXRECURSION 控制、實戰模板五個維度,建立遞歸 CTE 的標準化認知體系。

📐 遞歸 CTE 語法解剖

WITH RecursiveCTE AS (
    -- 1. 定點成員:遞歸起點,非遞歸查詢
    SELECT EmpId, MgrId, EmpName, 0 AS Level
    FROM Employee
    WHERE MgrId IS NULL  -- 根節點
    
    UNION ALL
    
    -- 2. 遞歸成員:引用 CTE 自身,逐層展開
    SELECT e.EmpId, e.MgrId, e.EmpName, r.Level + 1
    FROM Employee e
    INNER JOIN RecursiveCTE r ON e.MgrId = r.EmpId
    -- 可在此加 WHERE 限制遞歸深度/分支
)
-- 3. 外層查詢:消費遞歸結果
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 100); -- 關鍵:防無限遞歸

⚙️ 執行邏輯圖解

  1. 執行定點成員 → 得到初始結果集 R0
  2. 迭代執行遞歸成員:
    第 1 輪:引用 R0 → 產生 R1
    第 2 輪:引用 R1 → 產生 R2
    ...
    第 N 輪:引用 R(N-1) → 產生 RN(空集) → **終止**
  3. UNION ALL 合併 R0 ∪ R1 ∪ ... ∪ R(N-1) → 最終結果集
  4. 外層查詢 處理最終結果集

🛑 終止條件與 MAXRECURSION

  • 隱式終止:遞歸成員返回空結果集 → 自然停止
  • 顯式限制:OPTION (MAXRECURSION N)
    • N = 0:無限制(危險,慎用)
    • N = 1..32767:最大遞歸層數,超過報錯終止
    • 默認 100 層
  • 防死循環模式:在遞歸成員加入 WHERE r.Level < 100 或檢測循環路徑
-- 官方範例:防無限循環
USE AdventureWorks;
GO
WITH OrgCTE (EmpId, MgrId, Title, Level) AS (
    SELECT EmployeeID, ManagerID, Title, 0
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Title, o.Level + 1
    FROM HumanResources.Employee e
    JOIN OrgCTE o ON e.ManagerID = o.EmpId
    WHERE o.Level < 20 -- 額外保險
)
SELECT * FROM OrgCTE
OPTION (MAXRECURSION 100);
GO

🌳 三大經典應用場景模板

場景一:組織架構/員工層級查詢(樹)

WITH EmpTree AS (
    SELECT EmployeeID, ManagerID, LoginID, 0 AS Depth, CAST(LoginID AS VARCHAR(MAX)) AS Path
    FROM HumanResources.Employee WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.LoginID, et.Depth + 1,
           CAST(et.Path + ' > ' + e.LoginID AS VARCHAR(MAX))
    FROM HumanResources.Employee e
    JOIN EmpTree et ON e.ManagerID = et.EmployeeID
)
SELECT * FROM EmpTree ORDER BY Path
OPTION (MAXRECURSION 100);

場景二:物料清單 BOM 展開(圖/有向無環圖)

WITH BOM_CTE (ProductID, ComponentID, Level, Qty, Path) AS (
    SELECT ProductAssemblyID, ComponentID, 1, PerAssemblyQty, 
           CAST(ComponentID AS VARCHAR(MAX))
    FROM Production.BillOfMaterials
    WHERE ProductAssemblyID = @RootProduct AND EndDate IS NULL
    UNION ALL
    SELECT b.ProductAssemblyID, b.ComponentID, c.Level + 1, 
           b.PerAssemblyQty * c.Qty,
           CAST(c.Path + ' > ' + b.ComponentID AS VARCHAR(MAX))
    FROM Production.BillOfMaterials b
    JOIN BOM_CTE c ON b.ProductAssemblyID = c.ComponentID
    WHERE c.Level < 10 -- 防炸彈
)
SELECT * FROM BOM_CTE ORDER BY Path
OPTION (MAXRECURSION 100);

場景三:路徑查找/最短路徑(圖,需防循環)

WITH PathCTE (StartNode, EndNode, Path, Cost, Depth) AS (
    SELECT @Start, Neighbor, CAST(@Start + '->' + Neighbor AS VARCHAR(MAX)), Weight, 1
    FROM GraphEdges WHERE SourceNode = @Start
    UNION ALL
    SELECT p.StartNode, e.TargetNode, CAST(p.Path + '->' + e.TargetNode AS VARCHAR(MAX)),
           p.Cost + e.Weight, p.Depth + 1
    FROM PathCTE p
    JOIN GraphEdges e ON p.EndNode = e.SourceNode
    WHERE p.Depth < 20 
      AND p.Path NOT LIKE '%' + e.TargetNode + '%' -- 簡易防循環
)
SELECT TOP 1 * FROM PathCTE WHERE EndNode = @Target ORDER BY Cost
OPTION (MAXRECURSION 100);

⚡ 性能優化關鍵點

  1. 定點成員極小化:只選必要列,加嚴格 WHERE 過濾根節點
  2. 遞歸成員高效連接:確保連接列有索引(如 Employee.ManagerID)
  3. 盡早過濾:在遞歸成員內部用 WHERE 限制分支擴展
  4. 避免 SELECT *:只輸出必要列,減少中間結果集寬度
  5. 大數據量考慮替代方案:
    • 路徑枚舉法——存儲物化路徑 /1/5/12/
    • 巢狀集合——左/右邊界值
    • 閉包表——存儲所有祖先後代關係
    • 圖數據庫——Neo4j 等原生圖引擎

⚠️ 常見陷阱與避坑

  • ❌ 忘寫 OPTION (MAXRECURSION) → 生產環境跑飛佔滿 CPU/TempDB
  • ❌ 遞歸成員用 UNION 代替 UNION ALL → 額外去重開銷大、語義錯誤(層次結構不應去重)
  • ❌ 循環數據無防禦 → 無限遞歸直到報錯
  • ❌ 在遞歸 CTE 外層再 JOIN 大表 → 將大結果集物化後再 JOIN,應下推條件或改用臨時表分步

遞歸 CTE 口訣:
定點起點要精準,遞歸連接要有索,
層級計數防炸彈,MAXRECURSION 必加冠,
只取所需列最精,大數據量換模式。

copyright © 萬盛學電腦網 all rights reserved