DB2 公共表表達式 (CTE) 完全指南：語法、遞迴與實戰範例

什麼是公共表表達式 (CTE)？

CTE (Common Table Expression) 又稱 WITH 子句，是一個語句級暫存結果集，僅存在於定義它的單一 SQL 陳述式（SELECT、INSERT、UPDATE、DELETE、MERGE）執行期間。它像一個「內嵌 View」，可被主查詢多次引用，且每次引用結果一致。

基本語法結構

WITH cte_name [(col1, col2, ...)] AS (
    SELECT ...  -- 定義查詢
)
[, cte_name2 AS (...)]  -- 可定義多個 CTE，後者可引用前者
SELECT ... FROM cte_name ...;  -- 主查詢引用 CTE

核心特性

• 可重複引用：主查詢中可多次引用同一 CTE，優化器通常會將其物化（Materialize）一次。
• 鏈式依賴：後定義的 CTE 可引用前面定義的 CTE（如範例中 PAYBYED 引用 PAYLEVEL）。
• 遞迴能力：支援 WITH RECURSIVE 處理階層/圖結構（組織架構、BOM 展開）。
• 可更新：若 CTE 符合可更新 View 條件，可作為 INSERT/UPDATE/DELETE 目標。

實戰範例一：銷售彙總與總計聯合展示

需求：依產品彙總銷量，並附加全產品總計行。

WITH 
PROD_QTY AS (
    SELECT PRODUCT_ID, SUM(QUANTITY) AS QTY
    FROM CUSTOMER_ORDER_ITEM
    GROUP BY PRODUCT_ID
),
TOTALS AS (
    SELECT -1 AS PRODUCT_ID, SUM(QTY) AS TOTAL_QTY
    FROM PROD_QTY
)
SELECT PRODUCT_ID, QTY AS QUANTITY
FROM PROD_QTY
UNION ALL
SELECT PRODUCT_ID, TOTAL_QTY
FROM TOTALS
ORDER BY 1 DESC;

解析：PROD_QTY 計算分群彙總，TOTALS 基於其結果算總計，主查詢 UNION ALL 合併。若無 CTE，需重複彙總邏輯或使用衍生表/暫存表。

實戰範例二：多層聚合與自我關聯比較

需求：找出薪資低於「同學歷、同入職年份群體平均薪資」的員工。

WITH 
PAY_LEVEL AS (
    SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIRE_YEAR,
           SALARY + BONUS + COMM AS TOTAL_PAY
    FROM EMPLOYEE
    WHERE EDLEVEL > 16
),
AVG_PAY_BY_ED_HIRE AS (
    SELECT EDLEVEL, HIRE_YEAR, AVG(TOTAL_PAY) AS AVG_PAY
    FROM PAY_LEVEL
    GROUP BY EDLEVEL, HIRE_YEAR
)
SELECT 
    P.EMPNO, P.EDLEVEL, P.HIRE_YEAR, P.TOTAL_PAY,
    DECIMAL(A.AVG_PAY, 7, 2) AS GROUP_AVG_PAY
FROM PAY_LEVEL P
JOIN AVG_PAY_BY_ED_HIRE A
  ON P.EDLEVEL = A.EDLEVEL
 AND P.HIRE_YEAR = A.HIRE_YEAR
WHERE P.TOTAL_PAY < A.AVG_PAY;

亮點：PAY_LEVEL 只掃描一次 EMPLOYEE 表，AVG_PAY_BY_ED_HIRE 再聚合，主查詢關聯比較。邏輯清晰、效能優於多層巢狀子查詢。

遞迴 CTE 範例：組織架構層級顯示

WITH RECURSIVE ORG_TREE (EMP_ID, MGR_ID, LEVEL, PATH) AS (
    -- Anchor: 根節點 (CEO)
    SELECT EMPNO, MGRNO, 1, CAST(EMPNO AS VARCHAR(1000))
    FROM EMPLOYEE
    WHERE MGRNO IS NULL
    
    UNION ALL
    
    -- Recursive: 向下遞迴
    SELECT E.EMPNO, E.MGRNO, OT.LEVEL + 1, 
           OT.PATH || '->' || CAST(E.EMPNO AS VARCHAR(100))
    FROM EMPLOYEE E
    JOIN ORG_TREE OT ON E.MGRNO = OT.EMP_ID
    WHERE OT.LEVEL < 20  -- 防無限遞迴保護
)
SELECT * FROM ORG_TREE ORDER BY PATH;

效能與最佳實務

• 物化機制：DB2 通常將 CTE 物化為暫存表，避免重複計算。但極大結果集可能溢出暫存空間（TEMPSPACE），需監控。
• 替代方案：極簡單、單次引用的子查詢，直接寫巢狀 SELECT 可能更輕量。
• 遞迴限制：務必加 WHERE LEVEL < max_depth 防止無限循環；最大遞迴深度受 MAX_RECURSION_DEPTH 參數控制（預設 256/32767 視版本）。
• 命名規範：CTE 名稱建議加前綴（如 CTE_）區別實體表。