DB2左连接取指定状态最新时间戳并保留无匹配记录方案咨询

最优实现方案 #

DB2原生支持窗口函数，使用ROW_NUMBER窗口函数筛选目标记录是性能最优、可读性最高的实现方式，代码如下：

SELECT 
    a.Product_id,
    a.Order_no,
    b.Update_ts,
    b.Order_stat
FROM Product a
LEFT JOIN (
    SELECT 
        Product_id,
        Order_no,
        Update_ts,
        Order_stat,
        ROW_NUMBER() OVER(
            PARTITION BY Product_id, Order_no 
            ORDER BY Update_ts DESC
        ) AS rn
    FROM Orderdetails
    WHERE Order_stat = 'P'
) b 
ON a.Product_id = b.Product_id 
AND a.Order_no = b.Order_no 
AND b.rn = 1

方案说明 #

1. 内层子查询提前过滤Orderdetails表中所有非P状态的记录，减少后续计算的数据量
2. 用PARTITION BY按联合主键Product_id、Order_no分组，每组内按更新时间戳倒序排序，取第一条即为该组P状态的最新记录
3. 左关联Product表，保留表A全量记录，无匹配P状态记录的条目对应表B字段自动返回NULL，完全符合需求要求

原报错原因补充 #

你之前的SQL触发SQLCODE=-338报错，是因为DB2不支持在LEFT JOIN的ON子句中使用关联右表的相关子查询作为匹配条件，属于语法限制。

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