MySQL:根据连接表重复条目合并数据透视表中的条目

Question

我有 2 张 table ，其中一位参与者:

+----+------------+-----------+
| id | First Name | Last Name |
+----+------------+-----------+
|  0 | John       | Snow      |
|  1 | John       | Snow      |
|  2 | Michael    | Jackson   |
+----+------------+-----------+

还有一个将参与者与事件连接起来的数据透视表:

+----+----------------+----------+
| id | participant_id | event_id |
+----+----------------+----------+
|  0 |              0 |       12 |
|  1 |              1 |       35 |
|  2 |              2 |       35 |
+----+----------------+----------+

参与者表中错误地存在重复条目。

如何删除参与者表中的重复条目并相应更新数据透视表？所以预期的结果将是:

参加者:

+----+------------+-----------+
| id | First Name | Last Name |
+----+------------+-----------+
|  0 | John       | Snow      |
|    |            |           | //deleted
|  2 | Michael    | Jackson   |
+----+------------+-----------+

数据透视表:

+----+----------------+----------+
| id | participant_id | event_id |
+----+----------------+----------+
|  0 |              0 |       12 |
|  1 |              0 |       35 | //participant_id changed from 1 to 0
|  2 |              2 |       35 |
+----+----------------+----------+

Answer 1

这将是一个多步骤的过程:

• 第一步是更新映射表pivot。以下查询将为您提供所有重复的名称以及它们的第一个 id:

SELECT first_name, last_name, MIN(id) AS first_id 
FROM participants 
GROUP BY first_name, last_name 
HAVING COUNT(*) > 1 -- more than one rows means duplicates exist

您可以使用上述查询作为子查询，通过一系列联接来更新pivot表:

UPDATE pivot AS m 
JOIN participants AS p1 
  ON p1.id = m.participant_id 
JOIN (
       SELECT first_name, last_name, MIN(id) AS first_id 
       FROM participants 
       GROUP BY first_name, last_name 
       HAVING COUNT(*) > 1
     ) AS p2 ON p2.first_name = p1.first_name 
                AND p2.last_name = p1.last_name 
                AND p2.first_id <> p1.id  -- avoid the original row
SET m.participant_id = p2.first_id  -- update the duplicate row's id to first id

• 现在，您可以使用相同的子查询删除重复行(以查找重复项):

DELETE p1 FROM participants AS p1 
JOIN (
       SELECT first_name, last_name, MIN(id) AS first_id 
       FROM participants 
       GROUP BY first_name, last_name 
       HAVING COUNT(*) > 1
     ) AS p2 ON p2.first_name = p1.first_name 
                AND p2.last_name = p1.last_name 
                AND p2.first_id <> p1.id  -- avoid the original row

• 最后，通过在 (first_name, last_name) 上定义 UNIQUE 约束，在数据定义级别修复问题，以避免再次发生这种情况

ALTER TABLE participants ADD CONSTRAINT unq_idx_name UNIQUE(first_name, last_name);