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),
('新增', '檔案'),
('專案', '新增'),
('使用目前連線查詢', '新增');
階層關係:
檔案
└─ 新增
├─ 專案
└─ 使用目前連線查詢
WITH CTE_Name (Column_List) AS (
Query_Definition
)
SELECT * FROM CTE_Name;
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;
| name | senior | level |
|---|---|---|
| 檔案 | NULL | 0 |
| 新增 | 檔案 | 1 |
| 使用目前連線查詢 | 新增 | 2 |
| 專案 | 新增 | 2 |
UNION ALL/UNION/INTERSECT/EXCEPT;最後一定點與遞迴成員、遞迴成員間必須用 UNION ALLFROM 子句只能引用 CTE 一次DISTINCT, GROUP BY, HAVING, 標量彙總, TOP, LEFT/RIGHT/OUTER JOIN(允許 INNER JOIN), 子查詢T0T0 為輸入執行遞迴成員,產生 T1T1 為輸入產生 T2 … 直到某次迭代回傳空集合T0 UNION ALL T1 UNION ALL ... UNION ALL Tn理解「定位點決定終止條件」是掌握遞迴邏輯的關鍵。預設最大遞迴 100 層,可用 OPTION (MAXRECURSION 0) 移除限制(謹慎使用)。
CTE 遞迴讓 T-SQL 具備處理圖狀資料的能力,是進階開發必備技能。