表結構:ProductCategory(CategoryID, Level, ParentCategoryID)
資料範例:1,1,-1(根)、3,2,1(子)、4,3,3(孫)...
WITH CategoryTemp (CategoryID, ParentCategoryID) AS (
-- Anchor: 根節點
SELECT CategoryID, ParentCategoryID
FROM ProductCategory
WHERE ParentCategoryID <= 0
UNION ALL
-- Recursive: 逐層向下
SELECT pc.CategoryID, pc.ParentCategoryID
FROM ProductCategory pc
INNER JOIN CategoryTemp ct ON pc.ParentCategoryID = ct.CategoryID
WHERE pc.ParentCategoryID > 0
)
SELECT CategoryID, ParentCategoryID FROM CategoryTemp;
優點:標準語法、可讀性高、最佳化器支援良好。
擴充:若查詢特定節點(如 ParentCategoryID=2),將 Anchor 條件改為 WHERE CategoryID = 2。
CREATE FUNCTION f_id(@id INT)
RETURNS @re TABLE (id INT, lvl INT)
AS
BEGIN
DECLARE @l INT = 0;
INSERT @re SELECT id, @l FROM 表 WHERE 上級id = @id;
WHILE @@ROWCOUNT > 0
BEGIN
SET @l += 1;
INSERT @re
SELECT a.id, @l
FROM 表 a INNER JOIN @re b ON a.上級id = b.id AND b.lvl = @l - 1;
END
RETURN;
END;
GO
-- 呼叫
SELECT a.* FROM 表 a JOIN f_id(35) b ON a.id = b.id;
優點:相容舊版本、封裝邏輯可復用。
缺點:逐層迴圈效能較 CTE 差,大資料量需注意。
-- 建表與測試資料
CREATE TABLE 表(ID INT, 是否為部門 CHAR(1), 部門名 VARCHAR(10), 上級ID INT);
INSERT 表 VALUES
(1,'y','部門0',1),
(31,'y','部門1',1),
(32,'n','張三',31),
(33,'n','李二',31),
(34,'y','部門2',31),
(35,'n','王五',34),
(35,'y','部門3',34),
(36,'n','小三',35);
-- 執行函數查詢 ID=35 之下所有下層人員
SELECT a.* FROM 表 a JOIN f_id(35) b ON a.id = b.id;
-- 結果:ID=36 (小三)
| 模式 | 適用版本 | 效能 | 維護性 | 建議場景 |
|---|---|---|---|---|
| 遞歸 CTE | 2005+ | 優 | 高 | 現代專案首選 |
| UDF + WHILE | 2000+ | 中 | 中 | 舊版本維護、需封裝為函數 |
| 應用層遞歸 | 所有 | 視實作 | 高 | 極深層級、複雜業務邏輯 |
注意:CTE 預設最大遞歸層數 100,深層結構需加 OPTION (MAXRECURSION 0) 移除限制(慎用,防無限遞歸)。