如何编写SQL查询：重复邮箱时优先保留IsActive=1的行？

现有数据集包含以下字段：

• SubID：唯一标识
• EMail：邮箱地址
• IsActive：激活状态（1=激活，0=未激活）

需要编写SQL查询实现：

• 当同一邮箱对应多条记录时，仅返回IsActive=1的行
• 若该邮箱没有IsActive=1的记录，则返回任意一条IsActive=0的行

示例1 #

原始数据 #

预期结果 #

示例2 #

原始数据 #

预期结果 #

解决方案 #

通用写法（支持MySQL 8+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数，按邮箱分组后给激活状态行分配最高优先级，取每组第一行即可：

SELECT SubID, EMail, IsActive
FROM (
    SELECT 
        SubID, 
        EMail, 
        IsActive,
        -- 按邮箱分组，激活行排前面，同状态下按SubID升序取第一条
        ROW_NUMBER() OVER (
            PARTITION BY EMail 
            ORDER BY IsActive DESC, SubID ASC
        ) AS row_rank
    FROM 你的表名
) ranked_data
WHERE row_rank = 1;

说明

• PARTITION BY EMail：将数据按邮箱分组
• ORDER BY IsActive DESC：让IsActive=1的行排在每组最前面
• ORDER BY ... SubID ASC：当邮箱下全是未激活行时，按SubID升序取第一条，保证结果稳定（可替换为其他字段如创建时间）
• 外层筛选row_rank=1：获取每组的目标行

兼容MySQL 5.x的写法 #

如果数据库不支持窗口函数，可使用关联子查询实现：

SELECT t1.SubID, t1.EMail, t1.IsActive
FROM 你的表名 t1
LEFT JOIN 你的表名 t2 
    ON t1.EMail = t2.EMail 
    AND t2.IsActive = 1
WHERE 
    -- 邮箱存在激活行时，取激活行
    (t2.SubID IS NOT NULL AND t1.IsActive = 1)
    OR 
    -- 邮箱无激活行时，取SubID最小的未激活行
    (t2.SubID IS NULL AND t1.SubID = (
        SELECT MIN(SubID) FROM 你的表名 WHERE EMail = t1.EMail
    ));

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