SQL Server 遞歸查詢子孫樹經典實現：WHILE 循環 + CHARINDEX 字符串聚合法

場景與表結構

CREATE TABLE Menu (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    ParentID INT NULL REFERENCES Menu(ID)
);

INSERT INTO Menu VALUES
(1, N'新聞', NULL),
(2, N'房產', NULL),
(3, N'科技新聞', 1),
(4, N'社會新聞', 1),
(5, N'IT新聞', 3),
(6, N'航天新聞', 3);

核心算法：字符串聚合 + CHARINDEX 迭代

利用變量 @Path 存儲「已訪問節點 ID 列表」（逗號分隔），循環擴展直到無新節點。

DECLARE @Path VARCHAR(1000) = ','; -- 頭尾逗號便於 CHARINDEX 匹配

-- 1. 初始化：放入根節點
SELECT @Path = @Path + CAST(ID AS VARCHAR) + ','
FROM Menu WHERE ID = 1; -- 假設查詢 ID=1 的子孫樹

-- 2. 迭代擴展
WHILE @@ROWCOUNT > 0
BEGIN
    -- 找出：父節點在 @Path 中，且自身不在 @Path 中的節點
    SELECT @Path = @Path + CAST(ID AS VARCHAR) + ','
    FROM Menu
    WHERE CHARINDEX(',' + CAST(ParentID AS VARCHAR) + ',', @Path) > 0  -- 父在路徑中
      AND CHARINDEX(',' + CAST(ID AS VARCHAR) + ',', @Path) = 0;       -- 自身不在路徑中
END;

-- 3. 輸出結果
SELECT * FROM Menu
WHERE CHARINDEX(',' + CAST(ID AS VARCHAR) + ',', @Path) > 0;

原理解析

• @Path 格式：,1,3,5,6, —— 頭尾逗號確保 CHARINDEX(',3,', @Path) 精確匹配單個 ID，避免 13 誤匹配 3。
• 每輪迭代將「父在路徑、自身不在路徑」的節點加入路徑，相當於 BFS 層序遍歷。
• @@ROWCOUNT > 0 判斷本輪是否有新節點加入，無則結束。

現代替代方案：CTE 遞歸查詢（SQL Server 2005+）

WITH cte AS (
    SELECT ID, Name, ParentID, 0 AS Level
    FROM Menu WHERE ID = 1  -- 根節點
    UNION ALL
    SELECT m.ID, m.Name, m.ParentID, c.Level + 1
    FROM Menu m
    INNER JOIN cte c ON m.ParentID = c.ID
)
SELECT * FROM cte
OPTION (MAXRECURSION 100); -- 防無限遞歸，默認 100 層

優勢：標準 SQL、可攜性強、自動處理層級、可輸出層級欄位、優化器支持並行。
適用性：深度 ≤ 100 層（可用 MAXRECURSION 0 解除限制，慎用）。

性能對比與選型

生產環境建議

1. 新項目首選 CTE 遞歸，代碼簡潔、標準、可維護。
2. 超深層級（>1000）或極高頻查詢：採用物化路徑（存儲 /1/3/5/ 類路徑列，索引前綴查詢）或嵌套集合模型（左/右值編碼）。
3. 舊版本 SQL Server 2000 維護期才使用 WHILE 字符串法。