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

SQL Server CTE 遞歸查詢實戰：階層資料遍歷與效能優化

編輯：關於SqlServer

CTE (Common Table Expression) 核心價值

可讀性：將複雜查詢拆解為命名邏輯塊，類似「暫時 View」但不佔元資料。
遞歸能力：原生支援自我引用 (`UNION ALL` 遞歸項)，取代舊版 `WHILE` 迴圈 + 暫存表/游標的笨重模式。
範圍限定：僅在單一陳述式 (`SELECT/INSERT/UPDATE/DELETE/CREATE VIEW`) 生命週期內存在。

遞歸 CTE 語法結構


WITH RecursiveCTE (Col1, Col2, ..., Level) AS (
    -- 1. 錨點成員 : 起始集合 (Level = 0)
    SELECT Col1, Col2, ..., 0 AS Level
    FROM   BaseTable
    WHERE  

    UNION ALL

    -- 2. 遞歸成員 : 關聯上一層結果
    SELECT t.Col1, t.Col2, ..., r.Level + 1
    FROM   BaseTable t
    INNER JOIN RecursiveCTE r ON t.ParentID = r.ID  -- 關鍵關聯條件
    WHERE  r.Level < @MaxDepth  -- 可選：防止無限遞歸
)
-- 3. 外部查詢引用 CTE
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 100); -- 預設 100，0 為無限 (慎用)

實戰案例：多級分類樹完整遍歷

資料模型 (自關聯表)


CREATE TABLE Category (
    PKID      INT IDENTITY PRIMARY KEY,
    C_Name    NVARCHAR(50) NOT NULL,
    C_Level   INT NOT NULL,           -- 階層深度 (冗餘欄位，加速查詢)
    C_Code    NVARCHAR(255) NULL,     -- 路徑編碼 '1/4/6/' (關鍵優化欄位)
    C_Parent  INT NOT NULL DEFAULT 0, -- 父節點 PKID
    -- ... 其他欄位
);
-- 測試資料：1(根)-4(子)-6(孫)-9/10/11(曾孫) ; 2-5-8-12/13

方案一：標準遞歸 CTE (通用、無冗餘欄位需求)


WITH CatTree AS (
    SELECT C_Name, PKID, C_Code, C_Parent, 0 AS Lvl
    FROM Category WHERE PKID = 2  -- 從 '分類2' 開始

    UNION ALL

    SELECT c.C_Name, c.PKID, c.C_Code, c.C_Parent, p.Lvl + 1
    FROM Category c
    INNER JOIN CatTree p ON c.C_Parent = p.PKID
)
SELECT ct.C_Name AS NodeName,
       p.C_Name AS ParentName,
       ct.C_Code AS PathCode
FROM CatTree ct
LEFT JOIN Category p ON ct.C_Parent = p.PKID
OPTION (MAXRECURSION 0); -- 深度未知設 0

輸出：分類5(父:分類2) → 分類8(父:分類5) → 分類12/13(父:分類8)

方案二：限制遞歸深度 (僅查前 N 層)

錯誤示範：`OPTION (MAXRECURSION 2)` 若實際深度 > 2 會直接報錯終止 (Msg 530)。

正確做法：在 CTE 內部累加層級欄位，外層 `WHERE` 過濾。


WITH CatTree AS (
    SELECT C_Name, PKID, C_Code, C_Parent, 0 AS Lvl
    FROM Category WHERE PKID = 2

    UNION ALL

    SELECT c.C_Name, c.PKID, c.C_Code, c.C_Parent, p.Lvl + 1
    FROM Category c
    INNER JOIN CatTree p ON c.C_Parent = p.PKID
    WHERE p.Lvl < 2  -- 在遞歸成員內部終止，不產生第 3 層資料
)
SELECT ct.C_Name, p.C_Name AS ParentName, ct.C_Code
FROM CatTree ct
LEFT JOIN Category p ON ct.C_Parent = p.PKID
WHERE ct.Lvl <= 2; -- 雙重保險

方案三：路徑編碼法 - 效能王者 (無遞歸、純索引 Seek)

利用冗餘欄位 `C_Code` (如 `2/5/8/`) 儲存完整祖先路徑。新增/移動節點時由應用層/Trigger 維護此欄位。


-- 查詢 PKID=2 節點的所有後代 (含無限深度)
SELECT c.C_Name,
       (SELECT TOP 1 C_Name FROM Category WHERE PKID = c.C_Parent) AS ParentName,
       c.C_Code
FROM Category c
WHERE c.C_Code LIKE '2/%'; -- 索引範圍掃描，極快

-- 僅查前 3 層 (C_Level 冗餘欄位輔助)
SELECT c.C_Name, ...
FROM Category c
WHERE c.C_Code LIKE '2/%' AND c.C_Level <= 3;

效能對比與選型建議

方案	優點	缺點	適用場景
遞歸 CTE	標準 SQL、無冗餘欄位、邏輯清晰、支援動態深度	大樹 (萬節點+) 效能指數級下降 (Spool/遞歸迭代開銷)	組織架構、BOM 爆炸、臨時分析、深度 < 100 層、節點 < 5000
路徑編碼 + LIKE	單一 Index Seek/Range Scan、效能 O(log n)、支援覆蓋索引	寫入維護成本 (Trigger/應用層)、編碼長度限制 (900 bytes 索引鍵上限)	高頻讀取、大規模樹 (十萬節點)、固定深度查詢、報表/即時 API
HierarchyID (SQL 2008+)	原生型別、緊湊二進位、內建 `GetAncestor`/`IsDescendantOf`、索引支援	學習曲線、舊版不支援、字串轉換略繁瑣	新系統設計、標準化階層建模、需頻繁移動子樹
Nested Sets (左/右值)	單查詢取得整棵子樹 (BETWEEN)	寫入極慢 (需重算全樹)、並發差	幾乎唯讀、靜態分類 (如電商類目)

CTE 進階技巧與避坑

大小寫敏感：CTE 內部欄位名稱區分大小寫 (CLR 基礎)，`C_Code` ≠ `c_code`。
多重 CTE 串聯：`WITH CTE1 AS (...), CTE2 AS (SELECT * FROM CTE1 ...) SELECT * FROM CTE2;` 邏輯分層更清晰。
遞歸終止條件：務必在遞歸成員 `WHERE` 限制深度或循環偵測 (`WHERE NOT EXISTS (SELECT 1 FROM CatTree WHERE PKID = c.PKID)`)，防止資料髒污導致無限遞歸。
MAXRECURSION：預設 100。生產環境建議顯式設定合理上限 (如 1000) 而非 0，作為安全閥。
索引建議：遞歸關聯欄位 (`C_Parent`, `PKID`) 必須有索引；路徑編碼欄位 (`C_Code`) 必須建立索引 (前綴查詢 `LIKE 'prefix%'` 可用)。