萬盛學電腦網

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

SQL Server 遞迴查詢子孫樹:WHILE 迴圈 + CHARINDEX 字串模擬陣列實現

需求

給定父子關係表 MENU(id, mname, parent),查詢指定節點 (如 id=1「新聞」) 的所有子孫後代。

測試資料

CREATE TABLE MENU (id INT, mname CHAR(50), parent INT);
INSERT INTO MENU VALUES 
(1, '新聞', NULL), (2, '房產', NULL), (3, '科技新聞', 1), 
(4, '社會新聞', 1), (5, 'IT新聞', 3), (6, '航天新聞', 3);

經典解法:字串累積 + WHILE 迴圈

DECLARE @s VARCHAR(1000);

-- 1. 種子:根節點自己
SELECT @s = ',' + CAST(id AS VARCHAR(20)) + ',' FROM MENU WHERE id = 1;

-- 2. 迴圈展開子孫
WHILE @@ROWCOUNT > 0
BEGIN
    SELECT @s = @s + ',' + CAST(id AS VARCHAR(20)) + ','
    FROM MENU
    WHERE 
        -- 尚未在 @s 中
        CHARINDEX(',' + CAST(id AS VARCHAR) + ',', @s + ',') = 0
        -- 且 parent 在 @s 中
        AND CHARINDEX(',' + CAST(parent AS VARCHAR) + ',', @s + ',') > 0;
END

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

-- 清理
DROP TABLE MENU;

邏輯解析

  1. @s 以逗號分隔字串模擬集合,儲存已發現的節點 ID
  2. 每輪迴圈尋找:parent 在 @s 中 且 自己不在 @s 中 的節點
  3. @@ROWCOUNT > 0 表示本輪有新發現,繼續迴圈
  4. 最終 @s 包含根節點及所有後代

現代替代方案:遞迴 CTE (SQL 2005+)

WITH cte AS (
    SELECT id, mname, parent, 0 AS level
    FROM MENU WHERE id = 1
    UNION ALL
    SELECT m.id, m.mname, m.parent, c.level + 1
    FROM MENU m
    JOIN cte c ON m.parent = c.id
)
SELECT * FROM cte;

優勢:標準 SQL、可攜性、可輸出層級、效能更佳 (最佳化器支援)、無字串長度限制。

效能與限制比較

指標WHILE+字串遞迴 CTE
相容版本SQL 2000+SQL 2005+
最大深度受限 @s 長度 (8000/MAX)預設 100 層 (MAXRECURSION 可調)
效能大資料量慢 (字串操作、重複掃描)優化器可索引求助、集合運算
可讀性技巧性強、難維護宣告式、直觀
循環檢測無可加 WHERE level < 100

適用場景

  • SQL Server 2000 老系統維護 → WHILE 字串法
  • SQL Server 2005+ 新開發 → 強制使用遞迴 CTE
  • 超大階層 (萬層以上) → 考慮物化路徑 或 遞迴 CTE + 索引優化

總結

字串累積法是 SQL 2000 時代的經典智慧,展現了集合思維在缺乏遞迴支援時的變通。現代開發請毫不猶豫使用遞迴 CTE,獲得標準、高效、可維護的解決方案。

copyright © 萬盛學電腦網 all rights reserved