如何用SQL标准实现分组条件下的特定记录筛选？

当然可以用标准SQL实现这个需求！咱们先把需求再明确下：

• 按(ID, Start, End)分组
• 如果分组里同时存在Code='A'和Code='B'的记录，只留下Code='B'的那条
• 其他情况（分组里只有A或者只有B），保留分组内所有记录

样本输入数据 #

期望输出 #

实现方案：使用窗口函数（标准SQL支持） #

最简洁高效的方式是用窗口函数来标记每个分组的类型，再根据类型筛选记录。代码如下：

WITH group_metadata AS (
    SELECT
        *,
        -- 判断当前分组是否同时包含A和B
        CASE
            WHEN SUM(CASE WHEN Code = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Start, End) > 0
             AND SUM(CASE WHEN Code = 'B' THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Start, End) > 0
            THEN 'has_both'
            ELSE 'has_single'
        END AS group_code_type
    FROM your_table_name -- 替换成你的实际表名
)
SELECT ID, Start, End, Name, Code
FROM group_metadata
WHERE
    -- 分组同时有A和B时，只留Code=B的记录
    (group_code_type = 'has_both' AND Code = 'B')
    -- 其他情况保留所有记录
    OR group_code_type = 'has_single';

逻辑解释 #

1. CTE部分：通过窗口函数SUM(...) OVER (PARTITION BY ID, Start, End)，给每条记录标记它所在分组的Code类型：
   • 如果分组里既有A又有B，标记为has_both
   • 否则标记为has_single
2. 筛选部分：根据标记的类型过滤记录：
   • 对于has_both的分组，只保留Code='B'的记录
   • 对于has_single的分组，保留所有记录

这个写法完全符合SQL标准，在MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库都能正常运行。

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