SQL Server CTE 遞迴查詢實戰：階層結構樹狀資料完整遍歷

業務場景：行政區域樹狀結構

資料表 t_tree 儲存三層行政區域：省 → 市 → 區。

CREATE TABLE t_tree (id INT PRIMARY KEY, node_name NVARCHAR(50), parent_id INT);
INSERT INTO t_tree VALUES
(1, N'遼寧省', 0),
(2, N'瀋陽市', 1), (3, N'大連市', 1),
(4, N'大東區', 2), (5, N'瀋河區', 2), (6, N'鐵西區', 2);

需求：**查詢「遼寧省」及其所有下層市、區 (含自身)**。

遞迴邏輯抽象

1. **錨點成員** ：查詢根節點 WHERE node_name = N'遼寧省'。
2. **遞迴成員** ：以已找到節點的 id 作為 parent_id 查詢下一層。
3. **終止條件** ：遞迴成員回傳空集合 (@@ROWCOUNT = 0)。
4. **結果累積** ：UNION ALL 逐層合併。

圖 1：遞迴流程示意圖

CTE 遞迴語法標準形式

WITH cte_name (column_list) AS (
    -- 錨點成員
    SELECT ... FROM ... WHERE root_condition
    UNION ALL
    -- 遞迴成員 (引用 cte_name)
    SELECT ... FROM source_table JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

完整查詢腳本

WITH district AS (
    -- 1. 錨點：取得根節點
    SELECT id, node_name, parent_id
    FROM t_tree
    WHERE node_name = N'遼寧省'
    
    UNION ALL
    
    -- 2. 遞迴：依 parent_id 向下展開
    SELECT t.id, t.node_name, t.parent_id
    FROM t_tree t
    INNER JOIN district d ON t.parent_id = d.id
)
SELECT * FROM district
ORDER BY parent_id, id;

執行結果

圖 2：完整遍歷結果

進階應用：僅查詢非葉子節點 (有下層的節點)

WITH district AS (
    SELECT id, node_name, parent_id FROM t_tree WHERE node_name = N'遼寧省'
    UNION ALL
    SELECT t.id, t.node_name, t.parent_id FROM t_tree t INNER JOIN district d ON t.parent_id = d.id
),
district_non_leaf AS (
    SELECT d.* FROM district d
    WHERE d.id IN (SELECT parent_id FROM district)
)
SELECT * FROM district_non_leaf;

圖 3：非葉子節點 (遼寧省、瀋陽市)

遞迴 CTE 限制與規範 (必讀)

效能優化建議

• 索引支援：parent_id **必須建立索引** (非叢集即可)，避免每層掃描全表。
• 欄位最小化：CTE 僅選取必要欄位 (id, parent_id, node_name)，減少記憶體/TempDB 壓力。
• MAXRECURSION：依樹深度設定 (如 OPTION (MAXRECURSION 1000))，防止錯誤資料導致無限迴圈佔用資源。
• 大量資料替代方案：超過 10 萬節點、頻繁查詢，考慮 **Closure Table (關係閉包表)** 或 **HierarchyID** (SQL 2008+) 或 **遞迴預存程序 + 暫存表**。

結語

CTE 遞迴查詢以 **聲明式 SQL** 优雅解決樹狀結構遍歷問題，取代游標/暫存表/迴圈等程序式寫法。掌握「錨點→遞迴→UNION ALL→終止」四步曲，並嚴守 10 大限制規範，即可安全高效處理組織架構、產品分類、BOM 物料清單、帳號推薦關係等層級資料。搭配 MAXRECURSION 守門與索引優化，讓遞迴查詢在生產環境穩定可控。