SQL Server 2005 遞歸 CTE (WITH) 實現階層資料查詢範例

遞歸 CTE 語法結構

WITH cte_name (column_list) AS (
    -- 定點成員：初始結果集
    SELECT ... FROM ...
    UNION ALL
    -- 遞歸成員：引用 CTE 本身
    SELECT ... FROM ... JOIN cte_name ON ...
)
SELECT * FROM cte_name;

• UNION ALL 必須使用（UNION 會去重導致效能差且邏輯錯誤）。
• 遞歸成員**必須**引用 CTE 名稱，且只能引用一次。
• 預設最大遞歸層數 100，可用 OPTION (MAXRECURSION n) 調整（0 表無限制，慎用防無限迴圈）。

實戰範例：查詢申請單遞歸關聯鏈

資料表 fw_requestion_note 欄位：apply_id (當前單號)、old_apply_id (上一關單號)。需從根節點向下追蹤所有子孫單據。

WITH fw_requestion_note_temp (old_apply_id) AS (
    -- 定點：取得根節點（假設根節點 old_apply_id 為特定值或符合條件）
    SELECT old_apply_id
    FROM fw_requestion_note
    -- WHERE old_apply_id = 'e741470e-8b5c-4f91-9b03-c7474d103aef'  -- 可加篩選
    UNION ALL
    -- 遞歸：以目前 CTE 的 old_apply_id 去比對下一層的 apply_id
    SELECT fw.old_apply_id
    FROM fw_requestion_note fw
    INNER JOIN fw_requestion_note_temp temp ON fw.apply_id = temp.old_apply_id
)
SELECT old_apply_id FROM fw_requestion_note_temp
OPTION (MAXRECURSION 0);  -- 允許超過 100 層

執行邏輯圖解

1. 定點查詢產生第 0 層結果集（根節點集合）。
2. 遞歸查詢將第 0 層 old_apply_id 與 fw.apply_id 關聯，產生第 1 層 old_apply_id。
3. 重複步驟 2，直到無新行產生或達 MAXRECURSION 限制。
4. 最終 SELECT 輸出所有層級的 old_apply_id。

常見應用場景

效能與限制提醒

• 遞歸 CTE 逐層執行，大深度/大寬度樹狀結構可能產生大量中間結果，消耗 tempdb。
• 缺乏索引支援（apply_id、old_apply_id 應建索引）時效能急劇下降。
• SQL Server 2005 不支援 SEARCH DEPTH FIRST/BREADTH FIRST、CYCLE 子句（SQL 2008+ 新增）。
• 極大樹狀結構建議改用 HierarchyId (SQL 2008+) 或應用層快取/物質化路徑。

擴充：取得完整路徑字串

WITH cte AS (
    SELECT apply_id, old_apply_id, CAST(apply_id AS VARCHAR(MAX)) AS path, 1 AS lvl
    FROM fw_requestion_note WHERE old_apply_id = 'ROOT_GUID'
    UNION ALL
    SELECT fw.apply_id, fw.old_apply_id,
           cte.path + ' > ' + CAST(fw.apply_id AS VARCHAR(MAX)), lvl + 1
    FROM fw_requestion_note fw
    JOIN cte ON fw.apply_id = cte.old_apply_id
)
SELECT apply_id, old_apply_id, path, lvl FROM cte OPTION (MAXRECURSION 0);

輸出每節點從根到該節點的完整路徑，便於報表顯示或應用層快速判斷祖先關係。

總結

SQL Server 2005 引入的遞歸 CTE (WITH ... UNION ALL) 以宣告式 SQL 解決階層/圖狀資料查詢，取代舊版遊標/暫存表迴圈寫法，程式碼簡潔、可讀性高。關鍵在於正確設計定點與遞歸成員、建立關聯欄位索引、設定適當 MAXRECURSION 防無限迴圈。