萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2008 CTE 遞迴查詢完整教學:從語法到執行機制

SQL Server 2005 引入的 Common Table Expression (CTE) 讓遞迴查詢成為可能,極適合處理樹狀、階層結構資料(如選單、組織架構、BOM)。本文從基礎語法到執行流程深度解析。

範例資料:多層選單結構

CREATE TABLE MENU (
    name NVARCHAR(50) NOT NULL PRIMARY KEY,
    senior NVARCHAR(50) NULL  -- 父節點
);

INSERT INTO MENU VALUES
('檔案', NULL),
('新增', '檔案'),
('專案', '新增'),
('使用目前連線查詢', '新增');

階層關係:

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

CTE 基礎語法

WITH CTE_Name (Column_List) AS (
    Query_Definition
)
SELECT * FROM CTE_Name;

遞迴 CTE 結構:兩大核心成員

WITH lmenu(name, senior, level) AS (
    -- 1. 定位點成員:起始層級(根節點)
    SELECT NAME, SENIOR, 0 AS level FROM MENU WHERE SENIOR IS NULL
    
    UNION ALL
    
    -- 2. 遞迴成員:引用 CTE 本身,逐層向下
    SELECT A.NAME, A.SENIOR, B.level + 1 
    FROM MENU A
    INNER JOIN lmenu B ON A.SENIOR = B.NAME
)
SELECT * FROM lmenu;

查詢結果

nameseniorlevel
檔案NULL0
新增檔案1
使用目前連線查詢新增2
專案新增2

遞迴 CTE 限制條件(MSDN)

  • 至少一個定位點成員與一個遞迴成員,定位點必須在前
  • 定位點成員間用 UNION ALL/UNION/INTERSECT/EXCEPT;最後一定點與遞迴成員、遞迴成員間必須用 UNION ALL
  • 所有成員欄位數量、型別必須完全一致
  • 遞迴成員 FROM 子句只能引用 CTE 一次
  • 遞迴成員禁止:DISTINCT, GROUP BY, HAVING, 標量彙總, TOP, LEFT/RIGHT/OUTER JOIN(允許 INNER JOIN), 子查詢

執行機制深度解析

  1. 執行定位點成員,產生基準結果集 T0
  2. 以 T0 為輸入執行遞迴成員,產生 T1
  3. 以 T1 為輸入產生 T2 … 直到某次迭代回傳空集合
  4. 最終結果為 T0 UNION ALL T1 UNION ALL ... UNION ALL Tn

理解「定位點決定終止條件」是掌握遞迴邏輯的關鍵。預設最大遞迴 100 層,可用 OPTION (MAXRECURSION 0) 移除限制(謹慎使用)。

實務應用場景

  • 組織架構:員工-主管層級、完整上下游路徑
  • 物料清單 (BOM):父子階層展開、成本滾算
  • 分類目錄:無限層級分類樹
  • 圖形遍歷:最短路徑、循環偵測

CTE 遞迴讓 T-SQL 具備處理圖狀資料的能力,是進階開發必備技能。

copyright © 萬盛學電腦網 all rights reserved