查询contact_time前最新订单状态的SQL语句修复求助

修正后的SQL代码 #

SELECT 
    os.ORDER_ID, 
    os.ORDER_STATUS_ID,
    ms.STATUS_DESCRIPTION
FROM ORDER_STATUS os
INNER JOIN META_STATUS ms 
    ON os.ORDER_STATUS_ID = ms.META_STATUS_ID
INNER JOIN (
    SELECT 
        os_sub.ORDER_ID, 
        MAX(os_sub.TIMESTAMP) AS MAX_TIME 
    FROM ORDER_STATUS os_sub
    INNER JOIN CONTACT c
        ON os_sub.ORDER_ID = c.ORDER_ID
    WHERE c.CONTACT_TIME > os_sub.TIMESTAMP
    GROUP BY os_sub.ORDER_ID
) X
ON X.ORDER_ID = os.ORDER_ID 
AND os.TIMESTAMP = X.MAX_TIME

修正说明 #

• 补充了需求要求返回的STATUS_DESCRIPTION字段，该字段从关联的META_STATUS表获取
• 给所有表增加了别名，避免多表同名字段引发的歧义报错
• 补充了子查询中缺失的GROUP BY os_sub.ORDER_ID子句，原代码在多数SQL执行环境下会因为未分组聚合而报错
• 如果需要保留没有匹配到有效状态记录的订单，可以将两处INNER JOIN修改为LEFT JOIN，再按需处理空值即可

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