給定父子關係表 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);
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;
@s 以逗號分隔字串模擬集合,儲存已發現的節點 IDparent 在 @s 中 且 自己不在 @s 中 的節點@@ROWCOUNT > 0 表示本輪有新發現,繼續迴圈@s 包含根節點及所有後代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 2000 時代的經典智慧,展現了集合思維在缺乏遞迴支援時的變通。現代開發請毫不猶豫使用遞迴 CTE,獲得標準、高效、可維護的解決方案。