萬盛學電腦網

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

SQL Server CTE 結構與遞歸查詢實戰:層級數據、組織架構、物料清單 (BOM)

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;

結果

IdNameParentIdLvl
1根節點00
2分支A11
3分支B11
11葉A122
12葉A222
22葉B132
111葉A11113
222葉B11223

進階模式

路徑拼接 (物化路徑)

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/圖數據庫**。
copyright © 萬盛學電腦網 all rights reserved