SQL查询如何返回全部active记录及仅IDENTIFIER唯一的inactive记录

核心过滤逻辑 #

符合以下任意条件的记录会被保留：

• 记录本身状态为active（同一IDENTIFIER下多条active记录全部保留）
• 记录状态为inactive，同时满足：
  • 对应IDENTIFIER分组下无任何active状态记录
  • 对应IDENTIFIER分组下仅有1条记录（即IDENTIFIER全局唯一）

实现代码 #

WITH group_stats AS (
    SELECT
        *,
        -- 统计同IDENTIFIER下active记录总数
        COUNT(CASE WHEN status = 'active' THEN 1 END) OVER(PARTITION BY IDENTIFIER) AS group_active_count,
        -- 统计同IDENTIFIER下总记录数
        COUNT(*) OVER(PARTITION BY IDENTIFIER) AS group_total_count
    FROM 你的业务表名
)
SELECT * FROM group_stats
WHERE
    status = 'active'
    OR (
        status = 'inactive'
        AND group_active_count = 0
        AND group_total_count = 1
    )

场景验证 #

以下是对应4种业务场景的返回结果：

1. 单IDENTIFIER下存在1条active记录：正常返回该记录
2. 单IDENTIFIER下存在2条active+1条inactive：返回2条active记录，inactive记录被过滤
3. 单IDENTIFIER下无active记录：仅当该分组下只有1条inactive记录时返回，多条inactive全部过滤
4. 单IDENTIFIER下仅存在2条active记录：两条全部返回

参考样例 #

原始数据样例 #

预期返回结果 #

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