如何按条件删除contacts表重复记录并保留指定优先记录

原SQL语句仅简单保留每个name分组中id最小的记录，没有考虑is_contacted=1的优先保留规则，无法满足需求。以下是符合要求的修正方案：

方案1：使用子查询筛选需保留的记录ID #

DELETE FROM contacts
WHERE id NOT IN (
    SELECT target_id
    FROM (
        SELECT 
            name,
            COALESCE(
                MIN(CASE WHEN is_contacted = 1 THEN id END),
                MIN(id)
            ) AS target_id
        FROM contacts
        GROUP BY name
    ) AS keep_ids
);

方案2：使用LEFT JOIN方式删除冗余记录（适配更多数据库） #

DELETE c1
FROM contacts c1
LEFT JOIN (
    SELECT 
        name,
        COALESCE(
            MIN(CASE WHEN is_contacted = 1 THEN id END),
            MIN(id)
        ) AS keep_id
    FROM contacts
    GROUP BY name
) AS keep ON c1.id = keep.keep_id
WHERE keep.keep_id IS NULL;

逻辑说明 #

• 内层分组查询按name聚合：
  1. 先通过MIN(CASE WHEN is_contacted = 1 THEN id END)获取该分组中第一条（id最小）is_contacted=1的记录ID；
  2. 如果分组内没有is_contacted=1的记录，上述值为NULL，此时用COALESCE取分组内最小的id作为需保留的记录ID；
• 外层语句删除所有不在需保留ID集合内的冗余记录。

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