Common Table Expressions (CTE) 自 SQL Server 2005 引入,遞歸 CTE 更是處理層次化數據(組織架構、物料清單、分類樹、圖遍歷)的利器。本文從語法結構、執行邏輯、終止條件、MAXRECURSION 控制、實戰模板五個維度,建立遞歸 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); -- 關鍵:防無限遞歸
OPTION (MAXRECURSION N)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);
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/5/12/OPTION (MAXRECURSION) → 生產環境跑飛佔滿 CPU/TempDBUNION 代替 UNION ALL → 額外去重開銷大、語義錯誤(層次結構不應去重)遞歸 CTE 口訣:
定點起點要精準,遞歸連接要有索,
層級計數防炸彈,MAXRECURSION 必加冠,
只取所需列最精,大數據量換模式。