SQL Server 遞歸查詢三大實作：CTE、自訂函數、WHILE 迴圈完整教學

場景：階層式資料查詢

典型範例：產品分類表 ProductCategory(CategoryID, Level, ParentCategoryID)，需查詢某節點下所有子孫層級資料。

方法一：CTE 遞歸查詢（SQL Server 2005+ 推薦）

WITH CategoryTemp (CategoryID, ParentCategoryID) AS (
    -- Anchor member：根節點
    SELECT CategoryID, ParentCategoryID
    FROM ProductCategory
    WHERE ParentCategoryID <= 0

    UNION ALL

    -- Recursive member：往下遞歸
    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;

優點：語法標準、效能佳、可攜性高。注意：預設最大遞歸 100 層，可用 OPTION (MAXRECURSION 0) 移除限制。

方法二：自訂資料表值函數 + WHILE 迴圈

CREATE FUNCTION dbo.fn_GetDescendants (@RootID INT)
RETURNS @Result TABLE (ID INT, Level INT)
AS
BEGIN
    DECLARE @Level INT = 0;
    INSERT INTO @Result (ID, Level)
    SELECT ID, @Level FROM DeptTable WHERE ParentID = @RootID;

    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @Level += 1;
        INSERT INTO @Result (ID, Level)
        SELECT a.ID, @Level
        FROM DeptTable a
        INNER JOIN @Result b ON a.ParentID = b.ID AND b.Level = @Level - 1;
    END
    RETURN;
END;
GO

-- 呼叫
SELECT a.* FROM DeptTable a
INNER JOIN dbo.fn_GetDescendants(35) b ON a.ID = b.ID;

適用場景：需自訂邏輯、舊版相容、或不支援 CTE 的環境。

方法三：臨時表 + WHILE 迴圈（程序式寫法）

DECLARE @MaxID INT, @CurrentID INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT MainField, COUNT(*) FROM TableName GROUP BY MainField HAVING COUNT(*) > 1;

OPEN cur;
FETCH NEXT FROM cur INTO @CurrentID, @MaxID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MaxID = @MaxID - 1;
    SET ROWCOUNT @MaxID;
    DELETE FROM TableName WHERE MainField = @CurrentID;
    FETCH NEXT FROM cur INTO @CurrentID, @MaxID;
END
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

缺點：游標效能差、程序碼冗長，不建議大量資料使用。

完整測試腳本（含建表、測資、查詢、清理）

-- 建立測試表
CREATE TABLE DeptTable (
    ID INT, IsDept CHAR(1), DeptName VARCHAR(10), ParentID INT
);
INSERT INTO DeptTable VALUES
(1, 'y', 'Dept0', 1),
(31, 'y', 'Dept1', 1),
(32, 'n', '張三', 31),
(33, 'n', '李二', 31),
(34, 'y', 'Dept2', 31),
(35, 'n', '王五', 34),
(36, 'y', 'Dept3', 34),
(37, 'n', '小三', 35);

-- 方法一：CTE
WITH CTE AS (
    SELECT ID, ParentID, 0 AS Lvl FROM DeptTable WHERE ID = 35
    UNION ALL
    SELECT d.ID, d.ParentID, c.Lvl + 1
    FROM DeptTable d INNER JOIN CTE c ON d.ParentID = c.ID
)
SELECT * FROM CTE;

-- 方法二：函數（同上）
-- 方法三：WHILE 迴圈（同上）

-- 清理
DROP TABLE DeptTable;
DROP FUNCTION dbo.fn_GetDescendants;

效能與選擇建議