CTE 基礎結構
WITH CTE_Name [(col1, col2, ...)] AS (
-- 定義查詢 (Anchor member)
SELECT ...
)
-- 緊跟單條引用 CTE 的語句
SELECT * FROM CTE_Name;
- CTE **不持久化**,僅在單語句執行期有效。
- 可**自引用**實現遞歸,同一語句可多次引用。
- 列名列表可選,但若指定需與 SELECT 列數/順序一致。
遞歸 CTE 語法規則
WITH RecursiveCTE (cols) AS (
-- 1. 錨點成員:不引用 CTE 自身,放在 UNION ALL 之前
SELECT ... FROM BaseTable WHERE RootCondition
UNION ALL
-- 2. 遞歸成員:引用 CTE 自身,逐層展開
SELECT ... FROM BaseTable b
JOIN RecursiveCTE r ON b.ParentId = r.Id
-- 可選:終止條件 (如層級限制)
WHERE r.Level < 10
)
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 100); -- 默認 100,0=無限制
實戰:組織架構樹查詢
測試數據
CREATE TABLE Dept (Id INT, Name VARCHAR(50), ParentId INT);
INSERT INTO Dept VALUES
(1,'根節點',0),(2,'分支A',1),(3,'分支B',1),
(11,'葉A1',2),(12,'葉A2',2),(111,'葉A11',11),
(22,'葉B1',3),(222,'葉B11',22);
遞歸查詢:根節點 1 及所有後代
WITH Tree (Id, Name, ParentId, Lvl) AS (
SELECT Id, Name, ParentId, 0 FROM Dept WHERE Id = 1
UNION ALL
SELECT d.Id, d.Name, d.ParentId, t.Lvl + 1
FROM Dept d JOIN Tree t ON d.ParentId = t.Id
)
SELECT * FROM Tree ORDER BY Lvl, Id;
結果
| Id | Name | ParentId | Lvl |
| 1 | 根節點 | 0 | 0 |
| 2 | 分支A | 1 | 1 |
| 3 | 分支B | 1 | 1 |
| 11 | 葉A1 | 2 | 2 |
| 12 | 葉A2 | 2 | 2 |
| 22 | 葉B1 | 3 | 2 |
| 111 | 葉A11 | 11 | 3 |
| 222 | 葉B11 | 22 | 3 |
進階模式
路徑拼接 (物化路徑)
WITH Tree AS (
SELECT Id, Name, ParentId, CAST(Name AS VARCHAR(MAX)) AS Path, 0 AS Lvl
FROM Dept WHERE ParentId = 0
UNION ALL
SELECT d.Id, d.Name, d.ParentId, t.Path + ' > ' + d.Name, t.Lvl + 1
FROM Dept d JOIN Tree t ON d.ParentId = t.Id
)
SELECT * FROM Tree;
葉節點標記
WITH Tree AS (...), LeafMark AS (
SELECT *, CASE WHEN Id NOT IN (SELECT ParentId FROM Dept WHERE ParentId IS NOT NULL) THEN 1 ELSE 0 END AS IsLeaf
FROM Tree
)
SELECT * FROM LeafMark;
廣度優先編號
WITH Tree AS (...), Numbered AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY Lvl, Id) AS BFS_Seq FROM Tree
)
SELECT * FROM Numbered;
避坑指南
- MAXRECURSION:默認 100 層,深層樹需
OPTION (MAXRECURSION 0) 或具體大數,防無限遞歸佔滿 TempDB/棧溢出。
- 錨點必須在 UNION ALL 之前,且只能有一個錨點成員(多個用 UNION ALL 合併)。
- 遞歸成員只能引用 CTE 一次,不能自連接多次。
- 數據類型對齊:錨點與遞歸成員對應列類型必須兼容(隱式轉換或顯式 CAST)。
- 性能:遞歸 CTE 本質是迭代循環,大規模圖/樹(>10萬節點)考慮 **物化路徑**、**Closure Table** 或 **CLR/圖數據庫**。