SQL Server按状态优先级对transactions表去重的技术问询

首先，咱们的核心需求是：给每个name保留优先级更高的状态行——expired、cancelled、used这三类状态优先级高于available；如果某个用户同时有多个高优先级状态（比如harry的expired和cancelled），这些都要保留，只有当用户没有任何高优先级状态时，才保留他的available行。

方法一：窗口函数方案（适配PostgreSQL、MySQL 8+、SQL Server等） #

这种方法用窗口函数帮咱们快速统计每个用户的高优先级状态数量，再筛选符合要求的行：

WITH ranked_transactions AS (
    SELECT 
        name,
        status,
        -- 给状态标记优先级：高优先级状态设为1，available设为2
        CASE 
            WHEN status IN ('expired', 'cancelled', 'used') THEN 1
            ELSE 2
        END AS priority_rank,
        -- 统计每个用户下高优先级状态的总数量
        COUNT(CASE WHEN status IN ('expired', 'cancelled', 'used') THEN 1 END) OVER (PARTITION BY name) AS high_priority_count
    FROM transactions
)
SELECT name, status
FROM ranked_transactions
WHERE 
    priority_rank = 1 -- 保留所有高优先级状态行
    OR (priority_rank = 2 AND high_priority_count = 0) -- 仅当无高优先级时，保留available
ORDER BY name, status;

方法二：子查询方案（通用SQL，兼容更多老版本数据库） #

如果你的数据库不支持窗口函数，用子查询也能实现同样逻辑：

SELECT t.name, t.status
FROM transactions t
WHERE 
    -- 先保留所有高优先级状态行
    t.status IN ('expired', 'cancelled', 'used')
    -- 再判断：如果该用户没有任何高优先级状态，就保留他的available
    OR NOT EXISTS (
        SELECT 1 
        FROM transactions t2 
        WHERE t2.name = t.name 
          AND t2.status IN ('expired', 'cancelled', 'used')
    )
ORDER BY t.name, t.status;

预期结果验证 #

执行任意一种方案后，都会得到你想要的结果：

name status #

harry expired
harry cancelled
barry available
sally expired
jane used
nelly available

逻辑拆解 #

简单说就是两步：

1. 先把所有非available的行都留下来；
2. 对那些只有available状态的用户，单独保留他们的available行。

这样就完全贴合你给出的优先级去重规则啦！

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