如何获取订单Received状态后属于指定列表的首个后续状态

以下方案兼容MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的主流数据库：

实现逻辑 #

1. 先对每个订单的所有状态记录按时间排序，生成唯一序号，解决同一天多状态的排序问题
2. 提取所有Received状态的记录位置
3. 匹配每个Received之后的第一条符合有效范围的状态记录，跳过中间无效状态

完整SQL语句 #

WITH ordered_orders AS (
    -- 给每个订单的状态按时间排序，生成序号
    SELECT 
        `Order ID`,
        `Order Status`,
        `Date`,
        ROW_NUMBER() OVER (
            PARTITION BY `Order ID` 
            ORDER BY STR_TO_DATE(`Date`, '%m/%d/%Y') ASC, 
                     CASE WHEN `Order Status` = 'Received' THEN 0 ELSE 1 END ASC
        ) AS rn
    FROM orders
),
received_records AS (
    -- 提取所有Received状态的位置
    SELECT 
        `Order ID`,
        rn AS received_rn
    FROM ordered_orders
    WHERE `Order Status` = 'Received'
)
-- 匹配每个Received之后第一个有效状态
SELECT 
    o.`Order ID`,
    o.`Order Status`,
    o.`Date`
FROM ordered_orders o
INNER JOIN received_records r 
    ON o.`Order ID` = r.`Order ID`
    AND o.rn > r.received_rn
WHERE o.`Order Status` IN ('Shipped', 'Partially Shipped', 'Cancelled')
-- 排除非首个符合要求的记录
AND NOT EXISTS (
    SELECT 1 FROM ordered_orders o2
    WHERE o2.`Order ID` = o.`Order ID`
    AND o2.rn > r.received_rn
    AND o2.rn < o.rn
    AND o2.`Order Status` IN ('Shipped', 'Partially Shipped', 'Cancelled')
)
ORDER BY o.`Order ID`, o.rn;

注：如果你的数据库日期字段本身是日期类型而非字符串，可以去掉STR_TO_DATE函数，直接按Date字段排序即可。

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