DB2 SQL子查询取指定row_number值时父表字段作用域问题求解

错误原因 #

• 你当前写法采用了两层嵌套子查询，DB2中父查询的关联字段仅能向下穿透1层子查询，第二层子查询无法识别外层的TLORDER.DETAIL_LINE_ID字段，因此触发作用域报错
• 现有写法需要多次扫描ACHARGE_TLORDER表，执行效率极低

优化实现方案 #

改用CTE（公共表表达式）预计算所有需要的费用指标，再和主表关联即可，不仅解决作用域问题，性能也会大幅提升，参考代码如下：

WITH charge_precalc AS (
    SELECT 
        DETAIL_LINE_ID,
        SUM(CHARGE_AMOUNT) OVER (PARTITION BY DETAIL_LINE_ID) AS total_xtra,
        CHARGE_AMOUNT,
        ROW_NUMBER() OVER (PARTITION BY DETAIL_LINE_ID ORDER BY ACT_ID DESC) AS rn
    FROM TMWIN.ACHARGE_TLORDER
    WHERE ACODE_ID <> '' 
      AND ACODE_ID NOT LIKE 'FSC%'
),
charge_pivot AS (
    SELECT
        DETAIL_LINE_ID,
        MAX(total_xtra) AS "TOTAL XTRA CHARGES",
        MAX(CASE WHEN rn = 1 THEN CHARGE_AMOUNT END) AS "XTRA CHARGE 1",
        MAX(CASE WHEN rn = 2 THEN CHARGE_AMOUNT END) AS "XTRA CHARGE 2",
        MAX(CASE WHEN rn = 3 THEN CHARGE_AMOUNT END) AS "XTRA CHARGE 3"
    FROM charge_precalc
    GROUP BY DETAIL_LINE_ID
)
SELECT 
    t."DETAIL_LINE_ID",
    t."BILL_TO_NAME", 
    t."TOTAL_CHARGES",
    cp."TOTAL XTRA CHARGES",
    cp."XTRA CHARGE 1",
    cp."XTRA CHARGE 2",
    cp."XTRA CHARGE 3"
FROM "TMWIN"."TLORDER" t
LEFT JOIN charge_pivot cp ON t.DETAIL_LINE_ID = cp.DETAIL_LINE_ID
WHERE  t."BILL_TO_CODE"!='' 
  AND t."PICK_UP_BY_END">='2021-12-09'

逻辑说明 #

• 第一层CTEcharge_precalc批量计算所有符合条件的费用记录的总金额、排名，仅扫描一次ACHARGE_TLORDER表
• 第二层CTEcharge_pivot通过条件聚合将行转列，提取每个订单的前3项最新额外费用
• 最后通过左关联匹配订单主表数据，无对应额外费用的订单会返回NULL值，符合业务逻辑
• 日期格式改为标准的YYYY-MM-DD格式，可避免不同环境的日期解析报错

内容的提问来源于stack exchange，提问作者sirocode