DB2 遞歸查詢（CTE）詳解：樹形結構遍歷實戰

什麼是遞歸查詢？

DB2 遞歸查詢（Recursive Query），又稱樹查詢，基於 ANSI SQL 標準的 Common Table Expression (CTE) 實現。它能以單條 SQL 完成層級數據的逐層遍歷，如組織架構、物料清單（BOM）、流程鏈路等樹形/圖形結構查詢。

數據模型示例

假設流程表 DOCM_PROCESS_TS 結構如下：

樣本數據：

PROCESS_ID    PAR_PROCESS_ID    FW_ID
742482939     414358129         471
414358129     1707251033        470
1707251033    NULL              468

遞歸 CTE 語法拆解

目標：從葉子節點 (FW_ID='471') 向上遞歸查找根節點 (PAR_PROCESS_ID IS NULL)。

WITH PAR (PROCESS_ID, PAR_PROCESS_ID) AS (
    -- 【1】種子查詢：僅執行一次，初始化結果集
    SELECT PROCESS_ID, PAR_PROCESS_ID
    FROM DOCM_PROCESS_TS
    WHERE FW_ID = '471'

    UNION ALL

    -- 【2】遞歸查詢：引用 CTE 自身 PAR，關聯父節點
    SELECT CHILD.PROCESS_ID, CHILD.PAR_PROCESS_ID
    FROM PAR
    JOIN DOCM_PROCESS_TS CHILD
      ON PAR.PAR_PROCESS_ID = CHILD.PROCESS_ID
)
-- 【3】最終查詢：從遞歸結果集中篩選目標行
SELECT PROCESS_ID, PAR_PROCESS_ID
FROM PAR
WHERE PAR_PROCESS_ID IS NULL;

三階段執行機制

1. 種子階段：執行初始查詢，結果存入虛擬表 PAR。
2. 遞歸階段：反覆執行 UNION ALL 後的查詢，每輪將上一輪結果作為輸入，關聯父表產生新行，直到無新行產生或達到遞歸深度限制（默認 254 層，可通過 MAXRECURSION 調整）。
3. 最終查詢：對累積的完整結果集進行過濾、投影、排序等。

關鍵注意事項

• 必須包含終止條件：遞歸部分的 JOIN 條件最終須導致結果集為空，否則陷入無限循環。
• UNION ALL vs UNION：必須用 UNION ALL，UNION 會去重導致性能下降且可能邏輯錯誤。
• 列名與類型一致：種子查詢與遞歸查詢的選擇列數量、順序、數據類型必須完全匹配。
• 性能優化：在關聯列（如 PROCESS_ID, PAR_PROCESS_ID）建立索引；必要時在最終查詢加 FETCH FIRST n ROWS ONLY 限制輸出。

擴展應用：獲取完整路徑

若需輸出從根到葉的完整路徑字符串，可在 CTE 中累加字段：

WITH RECURSIVE TREE (ID, PID, PATH, LVL) AS (
    SELECT PROCESS_ID, PAR_PROCESS_ID, CAST(PROCESS_ID AS VARCHAR(1000)), 1
    FROM DOCM_PROCESS_TS WHERE FW_ID = '471'
    UNION ALL
    SELECT C.PROCESS_ID, C.PAR_PROCESS_ID,
           T.PATH || '->' || CAST(C.PROCESS_ID AS VARCHAR(1000)), T.LVL + 1
    FROM TREE T
    JOIN DOCM_PROCESS_TS C ON T.PID = C.PROCESS_ID
    WHERE T.LVL < 100 -- 防無限遞歸保護
)
SELECT * FROM TREE ORDER BY LVL DESC;