订单状态历史表中指定状态最新更新时间的SQL查询问题排查

咱们先拆解下你现有SQL的问题，再一步步修复：

现有SQL的核心问题 #

你的查询逻辑存在两个关键漏洞：

1. 子查询直接取了shipped和delivered状态里的最大updated_at，完全忽略了需求里的规则优先级——没有区分订单是否存在shipped状态，也没考虑订单的最后状态是什么；
2. 后面的过滤条件仅确保订单最后状态是shipped或delivered，但没改变子查询取最大值的逻辑，导致像示例里的订单121（最后状态是delivered但有过shipped记录），错误返回了delivered的时间，而不是你期望的最后一次shipped的时间。

符合需求的正确查询方案 #

我们可以用CTE（公共表表达式）结合窗口函数，先把每个订单的关键信息整理清楚，再严格按规则输出：

WITH order_status_summary AS (
    SELECT 
        order_id,
        -- 获取该订单的最后状态
        FIRST_VALUE(order_status_id) OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS last_status,
        -- 获取该订单最后一次shipped的时间
        MAX(CASE WHEN order_status_id = 'shipped' THEN updated_at END) OVER (PARTITION BY order_id) AS last_shipped_time,
        -- 获取该订单最后一次delivered的时间
        MAX(CASE WHEN order_status_id = 'delivered' THEN updated_at END) OVER (PARTITION BY order_id) AS last_delivered_time
    FROM order_status_history
),
-- 去重，确保每个订单只保留一条汇总记录
unique_order_summary AS (
    SELECT DISTINCT order_id, last_status, last_shipped_time, last_delivered_time
    FROM order_status_summary
)
SELECT 
    o.id AS order_id,
    CASE
        -- 规则1：最后状态是shipped，取最后一次shipped的时间
        WHEN uos.last_status = 'shipped' THEN uos.last_shipped_time
        -- 规则2：从未有过shipped状态，取最后一次delivered的时间
        WHEN uos.last_shipped_time IS NULL THEN uos.last_delivered_time
        -- 补充场景：有过shipped但最后是delivered，按示例取最后一次shipped的时间
        ELSE uos.last_shipped_time
    END AS updatedAt
FROM orders o
JOIN unique_order_summary uos ON o.id = uos.order_id
-- 仅保留最后状态是shipped或delivered的订单（对应原SQL的过滤条件）
WHERE uos.last_status IN ('shipped', 'delivered');

方案说明 #

1. order_status_summary CTE：通过窗口函数一次性计算出每个订单的三个核心信息：最后状态、最后一次shipped的时间、最后一次delivered的时间；
2. unique_order_summary CTE：对汇总结果去重，避免同一订单出现多条重复记录；
3. 主查询：用CASE语句严格匹配你的需求规则，同时保留原SQL中对订单最后状态的过滤逻辑。

用你的示例数据测试这个查询，订单121会返回2021-12-30 10:03:00，完全符合你的期望。

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