SQL Server 2008 CTE 遞歸查詢完整指南：語法結構、執行機制與階層資料實戰模式

CTE (Common Table Expression) 基礎

WITH CTE_Name (Col1, Col2, ...) AS (
    SELECT ... -- 定位點成員
    UNION ALL
    SELECT ... -- 遞歸成員 (引用 CTE_Name)
)
SELECT * FROM CTE_Name;

遞歸 CTE 結構限制 (MSDN)

• 至少一個 **定位點成員** (Anchor) + 一個 **遞歸成員** (Recursive)；可多個，但定位點必須在前。
• 定位點之間用 `UNION ALL` / `UNION` / `INTERSECT` / `EXCEPT`；**最後一個定位點與遞歸成員間必須用 `UNION ALL`**；遞歸成員之間也必須 `UNION ALL`。
• 定位點與遞歸成員 **欄位數量、順序、資料型別完全一致**。
• 遞歸成員 `FROM` 子句**只能引用一次 CTE 本身**。
• 遞歸成員**禁止**：`DISTINCT`、`GROUP BY`、`HAVING`、標量彙總、`TOP`、`LEFT/RIGHT/FULL OUTER JOIN` (允許 `INNER JOIN`)、子查詢。
• 預設最大遞歸層數 **100**；可用 `OPTION (MAXRECURSION 0)` 移除限制 (小心無限遞歸)。

實戰範例：多層選單樹狀結構

建立測試資料

CREATE TABLE Menu (
    Name NVARCHAR(50) NOT NULL PRIMARY KEY,
    Parent NVARCHAR(50) NULL
);
INSERT INTO Menu VALUES
(N'檔案', NULL),
(N'新增', N'檔案'),
(N'專案', N'新增'),
(N'使用目前連線查詢', N'新增');

層級結構

檔案
 └─ 新增
      ├─ 專案
      └─ 使用目前連線查詢

遞歸 CTE 查詢每個節點層級深度

WITH LMenu (Name, Parent, Level) AS (
    -- 定位點：根節點 (Parent IS NULL)
    SELECT Name, Parent, 0
    FROM Menu
    WHERE Parent IS NULL

    UNION ALL

    -- 遞歸成員：子節點連接父節點
    SELECT M.Name, M.Parent, L.Level + 1
    FROM Menu M
    INNER JOIN LMenu L ON M.Parent = L.Name
)
SELECT * FROM LMenu
OPTION (MAXRECURSION 0);

結果

Name              Parent    Level
檔案              NULL      0
新增              檔案      1
專案              新增      2
使用目前連線查詢  新增      2

執行機制深度解析：逐行遞歸展開

SQL Server 對 **外層查詢 `SELECT * FROM LMenu` 的每一行輸入**，獨立執行遞歸展開：

1. **第 1 行 (檔案)**：符合定位點條件 (`Parent IS NULL`)，直接輸出 Level=0，**不進入遞歸**。
2. **第 2 行 (新增)**：不符合定位點 → 進入遞歸成員，帶入 `Parent='檔案'`。
   遞歸成員 `INNER JOIN LMenu L ON M.Parent = L.Name` → `L.Name='檔案'` → 再次判斷：'檔案' 符合定位點 → 終止遞歸，輸出 Level=1。
3. **第 3/4 行 (專案/使用目前連線查詢)**：`Parent='新增'` → 遞歸 → `L.Name='新增'` (Level=1) → 仍不符合定位點 → **再遞歸一次** → `L.Name='檔案'` (Level=0) → 符合定位點 → 終止，輸出 Level=2。

等價於 **巢狀子查詢展開**：

-- 第 3 行實際展開邏輯
SELECT M.Name, M.Parent, L2.Level + 1
FROM Menu M
INNER JOIN (
    SELECT M2.Name, M2.Parent, L1.Level + 1 AS Level
    FROM Menu M2
    INNER JOIN (
        SELECT Name, Parent, 0 AS Level
        FROM Menu
        WHERE Parent IS NULL AND Name = N'檔案'
    ) L1 ON M2.Parent = L1.Name
    WHERE M2.Parent = N'檔案' AND M2.Name = N'新增'
) L2 ON M.Parent = L2.Name
WHERE M.Parent = N'新增' AND M.Name = N'專案';

常見進階模式

1. 路徑串聯 (Root → Leaf)

WITH Tree AS (
    SELECT Name, Parent, CAST(Name AS NVARCHAR(MAX)) AS Path, 0 AS Level
    FROM Menu WHERE Parent IS NULL
    UNION ALL
    SELECT M.Name, M.Parent, T.Path + N' > ' + M.Name, T.Level + 1
    FROM Menu M INNER JOIN Tree T ON M.Parent = T.Name
)
SELECT * FROM Tree ORDER BY Path;

2. 子樹查詢 (給定節點，找所有後代)

WITH SubTree AS (
    SELECT Name, Parent FROM Menu WHERE Name = N'新增'
    UNION ALL
    SELECT M.Name, M.Parent FROM Menu M INNER JOIN SubTree S ON M.Parent = S.Name
)
SELECT * FROM SubTree;

3. 祖先鏈 (給定節點，找所有祖先)

WITH Ancestors AS (
    SELECT Name, Parent FROM Menu WHERE Name = N'專案'
    UNION ALL
    SELECT M.Name, M.Parent FROM Menu M INNER JOIN Ancestors A ON M.Name = A.Parent
)
SELECT * FROM Ancestors;

4. 遞歸刪除 / 更新 (配合 OUTPUT)

-- 刪除節點及其所有後代
WITH ToDelete AS (
    SELECT Name FROM Menu WHERE Name = N'新增'
    UNION ALL
    SELECT M.Name FROM Menu M INNER JOIN ToDelete T ON M.Parent = T.Name
)
DELETE FROM Menu WHERE Name IN (SELECT Name FROM ToDelete);

效能優化與避坑指南

HierarchyID (SQL 2008+) — 原生階層資料類型

ALTER TABLE Menu ADD Hid HIERARCHYID;
-- 根節點
UPDATE Menu SET Hid = HIERARCHYID::GetRoot() WHERE Parent IS NULL;
-- 子節點
UPDATE Menu SET Hid = ParentHid.GetDescendant(NULL, NULL) 
FROM Menu M JOIN Menu P ON M.Parent = P.Name
WHERE M.Parent IS NOT NULL;

-- 查詢後代
SELECT * FROM Menu WHERE Hid.IsDescendantOf(@ParentHid) = 1;
-- 查詢祖先
SELECT * FROM Menu WHERE @NodeHid.IsDescendantOf(Hid) = 1;
-- 取得層級
SELECT Hid.GetLevel() FROM Menu;

優勢： 索引支援、位元組比較極快、內建 `GetAncestor`/`GetDescendant`/`GetLevel`/`IsDescendantOf`、`ToString`/`Parse` 序列化，**大幅優於遞歸 CTE**。

總結

CTE 遞歸查詢是 SQL Server 2005+ 處理階層/圖資料的標準工具，語法宣告式、可讀性強。掌握 **定位點/遞歸成員結構、逐行獨立展開機制、限制條件、MAXRECURSION 控制**，即可解決 90% 樹狀查詢需求。對於高頻、大量、深層級的階層資料，**`HierarchyID` 或物化路徑 (Path Enumeration) 是效能優化的終極選擇**。