SQL Server CTE 遞歸查詢處理樹狀、圖狀與層次結構完整指南

CTE (Common Table Expressions) 簡介

• SQL Server 2005+ 引入，定義暫時命名結果集，僅在單一陳述式範圍內有效。
• 不儲存為物件，類似衍生表，但可自我引用 (遞歸)、同一查詢中可多次引用。
• 提升可讀性、模組化，不損效能。

遞歸 CTE 語法結構

WITH CTE_Name (Col1, Col2, ...) AS (
  -- 定點成員
  SELECT Col1, Col2, ... FROM BaseTable WHERE RootCondition
  UNION ALL
  -- 遞歸成員
  SELECT T.Col1, T.Col2, ... 
  FROM BaseTable T
  JOIN CTE_Name C ON C.Col1 = T.ParentCol
)
SELECT * FROM CTE_Name;

關鍵要素

1. 定點成員：提供遞歸起始資料列集，不得引用 CTE 名稱。
2. 遞歸成員：必須引用 CTE 名稱，邏輯上代表上一輪結果集。
3. UNION ALL：合併兩成員結果，遞歸成員引用的是前一輪結果 (非累積全集)。
4. 終止條件：遞歸成員回傳空集合時自然停止；或達到 MAXRECURSION 上限 (預設 100，0=無限制)。

實戰範例：員工組織架構遞歸

USE AdventureWorks;
GO

WITH OrgCTE (EmployeeID, ManagerID, Title, Level) AS (
  -- 定點：頂層管理者 (無上級)
  SELECT EmployeeID, ManagerID, Title, 0 AS Level
  FROM HumanResources.Employee
  WHERE ManagerID IS NULL
  UNION ALL
  -- 遞歸：尋找下屬
  SELECT E.EmployeeID, E.ManagerID, E.Title, C.Level + 1
  FROM HumanResources.Employee E
  JOIN OrgCTE C ON C.EmployeeID = E.ManagerID
)
SELECT EmployeeID, ManagerID, Title, Level
FROM OrgCTE
OPTION (MAXRECURSION 0);  -- 移除 100 層限制

防止無限遞歸：MAXRECURSION

-- 限制最多 50 層
SELECT * FROM OrgCTE OPTION (MAXRECURSION 50);

-- 偵測循環 (圖結構) 需自行加欄位追蹤路徑
WITH GraphCTE (NodeId, Path, Depth) AS (
  SELECT NodeId, CAST(NodeId AS VARCHAR(MAX)), 0 FROM Graph WHERE IsRoot = 1
  UNION ALL
  SELECT G.NodeId, C.Path + '->' + CAST(G.NodeId AS VARCHAR(MAX)), C.Depth + 1
  FROM Graph G
  JOIN GraphCTE C ON C.NodeId = G.ParentId
  WHERE C.Path NOT LIKE '%' + CAST(G.NodeId AS VARCHAR(10)) + '%'  -- 簡易循環檢測
)
SELECT * FROM GraphCTE OPTION (MAXRECURSION 0);

效能與最佳實務

• 索引支援：遞歸 JOIN 欄位 (如 ManagerID, ParentId) 必須有索引。
• 避免寬欄位：CTE 選取欄位越少、資料型別越窄，記憶體/Tempdb 壓力越小。
• 大深度改用迴圈/暫存表：超過數萬層遞歸可能耗盡堆疊，考慮 WHILE + #Tmp 迭代。
• 圖結構循環檢測：路徑字串法 (上例) 或位元組陣列/雜湊集合 (CLR) 記錄已訪問節點。

適用場景

• 組織架構、BOM (物料清單)、分類樹、論壇回覆串、檔案目錄、圖遍歷 (最短路徑、拓撲排序)。

結語

遞歸 CTE 是 SQL Server 處理層次/圖結構的標準工具，語法簡潔、符合 ANSI SQL。善用 MAXRECURSION 控制深度、索引加速 JOIN、窄欄位減輕資源，即能高效解決樹狀/圖狀查詢需求。