如何编写SQL查询按状态优先级为每个客户筛选单条记录？

原始数据 #

需求说明 #

编写SQL查询为每个客户筛选一条记录，遵循以下规则：

• 若客户有状态为In Progress的记录，仅返回该记录；
• 若没有In Progress但存在Shipped记录，则返回Shipped记录。

实现查询 #

可以利用窗口函数ROW_NUMBER()为每个客户的记录按状态优先级排序，再筛选出优先级最高的那条：

SELECT id, customer_id, status
FROM (
    SELECT 
        id, 
        customer_id, 
        status,
        -- 按状态优先级排序：In Progress > Shipped > 其他
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY 
                CASE status 
                    WHEN 'In Progress' THEN 1 
                    WHEN 'Shipped' THEN 2 
                    ELSE 3 
                END
        ) AS rn
    FROM your_table_name
    -- 只保留需要考虑的两种状态，排除Cancelled这类无关状态
    WHERE status IN ('In Progress', 'Shipped')
) t
-- 取每个客户优先级最高的第一条记录
WHERE rn = 1;

预期结果 #

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