如何在MySQL中按条件删除重复行（保留Active=1的记录）

你的原查询逻辑存在问题：SELECT Id FROM tbl_name GROUP BY email会返回每个email分组中任意一条记录的Id（数据库对非聚合列的返回行为未定义，不同环境结果不同），因此NOT IN筛选后只会排除这个随机Id对应的记录，完全没考虑Active的优先级，这就是你得到错误结果的原因。

需求拆解 #

对于同一email：

1. 若同时存在Active=1和Active=0，保留Active=1的记录（可选保留其中一条，比如最小/最大Id），删除Active=0的记录
2. 若仅存在一种Active值，保留该值的记录（可选保留一条）

以下是两种可行的实现方案：

方案1：适用于所有SQL数据库（无窗口函数支持） #

1. 查询需要保留的记录 #

先获取每个email的最高优先级Active值（1优先级高于0），再筛选出对应记录，最后按email分组保留一条（以最小Id为例）：

SELECT t.Id, t.email, t.Active
FROM tbl_name t
JOIN (
    -- 每个email的目标Active值（优先1）
    SELECT email, MAX(Active) AS target_active
    FROM tbl_name
    GROUP BY email
) t2 ON t.email = t2.email AND t.Active = t2.target_active
GROUP BY t.email, t.Active
HAVING t.Id = MIN(t.Id); -- 保留每组最小Id的记录

2. 删除不需要的记录 #

DELETE FROM tbl_name
WHERE Id NOT IN (
    SELECT MIN(t.Id)
    FROM tbl_name t
    JOIN (
        SELECT email, MAX(Active) AS target_active
        FROM tbl_name
        GROUP BY email
    ) t2 ON t.email = t2.email AND t.Active = t2.target_active
    GROUP BY t.email, t.Active
);

方案2：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

窗口函数更简洁直观，按email分组后，先按Active降序排序（1在前），再按Id升序排序，取每组第一条即为需要保留的记录：

1. 查询需要保留的记录 #

WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY email 
               ORDER BY Active DESC, Id ASC -- 优先Active=1，再选最小Id
           ) AS row_rank
    FROM tbl_name
)
SELECT Id, email, Active
FROM ranked_records
WHERE row_rank = 1;

2. 删除不需要的记录 #

WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY email 
               ORDER BY Active DESC, Id ASC
           ) AS row_rank
    FROM tbl_name
)
DELETE FROM tbl_name
WHERE Id IN (SELECT Id FROM ranked_records WHERE row_rank > 1);

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