萬盛學電腦網

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

SQL Server 遞歸查詢三大實作模式:CTE、函數迴圈、聯合查詢

場景:多層級分類/組織架構查詢

表結構:ProductCategory(CategoryID, Level, ParentCategoryID)
資料範例:1,1,-1(根)、3,2,1(子)、4,3,3(孫)...

模式一:遞歸 CTE (SQL Server 2005+ 推薦)

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。

模式二:使用者定義函數 + WHILE 迴圈 (SQL 2000 相容)

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 (小三)

效能與選型建議

模式適用版本效能維護性建議場景
遞歸 CTE2005+優高現代專案首選
UDF + WHILE2000+中中舊版本維護、需封裝為函數
應用層遞歸所有視實作高極深層級、複雜業務邏輯

注意:CTE 預設最大遞歸層數 100,深層結構需加 OPTION (MAXRECURSION 0) 移除限制(慎用,防無限遞歸)。

copyright © 萬盛學電腦網 all rights reserved