MySQL数据库中重复值的查找与更新方案问询

需求概述 #

需要完成三个递进操作：

1. 筛选出Email字段重复的所有记录；
2. 从第一步结果中，进一步筛选出FirstName、LastName、Address三个字段同时重复的记录；
3. 从第二步结果里，将每组（按FirstName、LastName、Address分组）中created_date最大的记录的isParent字段更新为1。

现有SQL问题 #

你提供的SQL存在两处问题：

• 语法错误：字段列表中Address与SF_dup_leads.Email之间缺少逗号，且语句末尾多了逗号，执行会直接报错；
• 逻辑倒置：子查询先按姓名地址分组统计Email数量，不符合先筛选重复Email、再筛选重复姓名地址的需求逻辑，无法得到正确结果。

分步正确实现 #

第一步：筛选Email重复的记录 #

先找出所有出现过多次的Email，再关联原表获取这些Email的全部记录：

-- 第一步：获取Email重复的所有记录
SELECT *
FROM SF_dup_leads
WHERE Email IN (
    SELECT Email
    FROM SF_dup_leads
    GROUP BY Email
    HAVING COUNT(*) > 1
);

执行后会得到示例中abc@123.com和xyz@123.com的所有记录，符合需求说明。

第二步：筛选Email重复且姓名地址也重复的记录 #

基于第一步的结果，进一步找出FirstName、LastName、Address同时重复的记录，MySQL 8.0+用CTE实现更清晰：

-- 第二步：获取Email重复且FirstName/LastName/Address也重复的记录
WITH email_dup_records AS (
    -- 复用第一步的结果：Email重复的记录
    SELECT *
    FROM SF_dup_leads
    WHERE Email IN (
        SELECT Email
        FROM SF_dup_leads
        GROUP BY Email
        HAVING COUNT(*) > 1
    )
)
SELECT *
FROM email_dup_records
WHERE (FirstName, LastName, Address) IN (
    SELECT FirstName, LastName, Address
    FROM email_dup_records
    GROUP BY FirstName, LastName, Address
    HAVING COUNT(*) > 1
);

如果你的MySQL版本低于8.0，改用子查询嵌套：

SELECT *
FROM SF_dup_leads
WHERE Email IN (
    SELECT Email
    FROM SF_dup_leads
    GROUP BY Email
    HAVING COUNT(*) > 1
)
AND (FirstName, LastName, Address) IN (
    SELECT FirstName, LastName, Address
    FROM SF_dup_leads
    WHERE Email IN (
        SELECT Email
        FROM SF_dup_leads
        GROUP BY Email
        HAVING COUNT(*) > 1
    )
    GROUP BY FirstName, LastName, Address
    HAVING COUNT(*) > 1
);

执行后会得到示例中重复的组，比如John Abraham India两条、Shahrukh Khan India两条等。

第三步：更新每组最新记录的isParent为1 #

需要先定位到每组（按FirstName/LastName/Address分组）中created_date最大的记录，再执行更新。注意示例中created_date是dd/mm/yy格式的字符串，需要转成日期类型比较：

-- 第三步：更新目标记录的isParent为1（MySQL 8.0+支持）
WITH email_dup_records AS (
    SELECT *
    FROM SF_dup_leads
    WHERE Email IN (
        SELECT Email
        FROM SF_dup_leads
        GROUP BY Email
        HAVING COUNT(*) > 1
    )
), name_addr_dup_records AS (
    SELECT *
    FROM email_dup_records
    WHERE (FirstName, LastName, Address) IN (
        SELECT FirstName, LastName, Address
        FROM email_dup_records
        GROUP BY FirstName, LastName, Address
        HAVING COUNT(*) > 1
    )
), max_date_groups AS (
    SELECT *,
           MAX(STR_TO_DATE(created_date, '%d/%m/%y')) OVER (PARTITION BY FirstName, LastName, Address) AS latest_date
    FROM name_addr_dup_records
)
UPDATE SF_dup_leads t1
JOIN max_date_groups t2
  ON t1.Email = t2.Email
  AND t1.FirstName = t2.FirstName
  AND t1.LastName = t2.LastName
  AND t1.Address = t2.Address
  AND STR_TO_DATE(t1.created_date, '%d/%m/%y') = t2.latest_date
SET t1.is_Parent = 1;

如果是MySQL 5.7及以下版本，改用子查询实现：

UPDATE SF_dup_leads t1
JOIN (
    SELECT t.*,
           MAX(STR_TO_DATE(t.created_date, '%d/%m/%y')) AS latest_date
    FROM (
        SELECT *
        FROM SF_dup_leads
        WHERE Email IN (
            SELECT Email
            FROM SF_dup_leads
            GROUP BY Email
            HAVING COUNT(*) > 1
        )
        AND (FirstName, LastName, Address) IN (
            SELECT FirstName, LastName, Address
            FROM SF_dup_leads
            WHERE Email IN (
                SELECT Email
                FROM SF_dup_leads
                GROUP BY Email
                HAVING COUNT(*) > 1
            )
            GROUP BY FirstName, LastName, Address
            HAVING COUNT(*) > 1
        )
    ) t
    GROUP BY t.FirstName, t.LastName, t.Address
) t2
  ON t1.FirstName = t2.FirstName
  AND t1.LastName = t2.LastName
  AND t1.Address = t2.Address
  AND STR_TO_DATE(t1.created_date, '%d/%m/%y') = t2.latest_date
SET t1.is_Parent = 1;

执行效果 #

更新后，示例数据中每组的最新记录isParent会被设为1，比如：

• abc@123.com John Abraham India 12/12/22
• abc@123.com Shahrukh Khan India 01/03/12
• xyz@123.com Shahrukh Khan Pakistan 11/02/21
• xyz@123.com Salman Khan Uganda 11/12/22
• xyz@123.com Salman Khan Sudan 04/03/21

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