您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 遞迴 CTE 查詢部門所有子部門完整教學：WITH 關鍵字實戰

編輯：關於SqlServer

遞迴 CTE (Common Table Expression) 核心概念

SQL Server 2005 引入 WITH 子句支援遞迴查詢，完美解決階層式資料（組織架構、物料清單、分類樹）的子孫/祖先查詢問題。SQL Server 2000 不支援此功能。

基本語法結構


WITH cte_name (column_list) AS (
  -- 1. 定錨點成員：起始查詢（非遞迴）
  SELECT ... FROM table WHERE condition
  
  UNION ALL
  
  -- 2. 遞迴成員：引用 CTE 本身
  SELECT ... FROM table 
  INNER JOIN cte_name ON table.parent_col = cte_name.key_col
)
-- 3. 最終查詢
SELECT * FROM cte_name [OPTION (MAXRECURSION n)];

範例 1：查詢指定部門的所有子部門


WITH SubDept (DEPT_ID, DEPT_NAME) AS (
  -- 錨點：起始部門 (DEPT_ID = 1)
  SELECT DEPT_ID, DEPT_NAME
  FROM dbo.DEPARTMENTInfo
  WHERE DEPT_ID = 1
  
  UNION ALL
  
  -- 遞迴：尋找父部門為目前 CTE 中部門的子部門
  SELECT d.DEPT_ID, d.DEPT_NAME
  FROM dbo.DEPARTMENTInfo d
  INNER JOIN SubDept s ON d.SUPERIOR_DEPT = s.DEPT_ID
)
SELECT * FROM SubDept;

範例 2：完整可執行測試資料與樹狀顯示


-- 建立測試資料
SELECT * INTO #t FROM (
  SELECT 1 AS ID, 'Food' AS Product, 0 AS ParentID UNION ALL
  SELECT 2, 'Sport', 0 UNION ALL
  SELECT 3, 'Drink', 1 UNION ALL
  SELECT 4, 'Ball', 2 UNION ALL
  SELECT 5, 'Fruit', 1 UNION ALL
  SELECT 6, 'Apple', 5 UNION ALL
  SELECT 7, 'Banana', 5 UNION ALL
  SELECT 8, 'Football', 4 UNION ALL
  SELECT 9, 'Basketball', 4 UNION ALL
  SELECT 10, 'Pepsi', 3 UNION ALL
  SELECT 11, 'Wahaha', 3
) AS a;

-- 遞迴 CTE 產生樹狀結構與排序鍵
WITH ShowTree (ID, Product, OrderBy) AS (
  -- 錨點：根節點
  SELECT ID, Product, CAST(ID AS VARCHAR(50))
  FROM #t
  WHERE ParentID = 0
  
  UNION ALL
  
  -- 遞迴：子節點，OrderBy 累加實現層級排序
  SELECT t.ID, t.Product, CAST(s.OrderBy * 100 + t.ID AS VARCHAR(50))
  FROM ShowTree s
  INNER JOIN #t t ON s.ID = t.ParentID
)
SELECT * 
FROM ShowTree 
ORDER BY OrderBy
OPTION (MAXRECURSION 100); -- 預設 100 層，深層結構需調大

-- 清理
DROP TABLE #t;

關鍵要素解析

元素	說明	注意事項
錨點成員	非遞迴的初始查詢	必須至少傳回一列，定義 CTE 欄位結構
UNION ALL	連接錨點與遞迴成員	不可用 UNION（會去重導致遞迴終止）
遞迴成員	引用 CTE 名稱的查詢	只能引用一次 CTE；不可包含 GROUP BY、TOP、DISTINCT 等
終止條件	遞迴成員不再傳回列時自動停止	需確保資料無循環參考，否則無限遞迴
MAXRECURSION	限制最大遞迴層數（0 = 無限制）	預設 100；防止無限迴圈佔用資源，測試時可設 0

進階應用：路徑串聯與層級編號


WITH OrgTree AS (
  SELECT 
    EmployeeID, 
    ManagerID, 
    FirstName + ' ' + LastName AS Name,
    CAST(EmployeeID AS VARCHAR(MAX)) AS Path,
    0 AS Level
  FROM Employees
  WHERE ManagerID IS NULL
  
  UNION ALL
  
  SELECT 
    e.EmployeeID, 
    e.ManagerID, 
    e.FirstName + ' ' + e.LastName,
    CAST(o.Path + ' > ' + CAST(e.EmployeeID AS VARCHAR(10)) AS VARCHAR(MAX)),
    o.Level + 1
  FROM Employees e
  INNER JOIN OrgTree o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgTree ORDER BY Path;