如何使用多个标准来识别 MySQL 表中的唯一人员

Question

我有大约 20 万条记录表dat，其中包含人员的识别数据以及测试日期和结果，这是一个虚拟版本：

+----+---------+----------+------------+----------+------------+--------+
| id | surname | forename | dob        | SchoolID | testDate   | result |
+----+---------+----------+------------+----------+------------+--------+
|  1 | Smith   | Mary     | 1980-04-11 | NULL     | 2005-10-12 |  14.32 |
|  2 | Smith   | Mary     | 1980-04-11 | 1234     | 2007-03-02 |   18.1 |
|  3 | Jones   | Kim      | 1978-10-24 | 4657     | 2002-04-14 |  24.31 |
|  4 | Jones   | Kim      | NULL       | 4567     | 2002-10-08 |  19.02 |
|  5 | Roberts | Kim      | 1978-10-24 | 4567     | 2003-12-18 |  14.19 |
|  6 | Roberts | Kim      | 1978-10-24 | 4567     | 2005-02-11 |  18.26 |
+----+---------+----------+------------+----------+------------+--------+

我想识别进行过多次测试的人员，并且创建一个新列uniqueID，为独特的人提供新的 ID。遗憾的是，我的数据库不是很整洁，所以我需要使用几个标准将这些记录分成唯一的个体：

• 姓氏、名字和出生日期是相同的（因为 SchoolID 经常丢失或错误）
• 姓氏、名字和 SchoolID 是相同的（因为有时未输入出生日期，或者 输入错误）
• 名字、出生日期和 SchoolID 相同（人们有时会结婚）
• 等。

在上面的示例中，表中只有两个唯一的人 Mary Smith 和 Kim Roberts nee Jones，所以这个 uniqueID 专栏最终应该是：

+----+----------+
| id | uniqueID |
+----+----------+
|  1 |        1 |
|  2 |        1 |
|  3 |        2 |
|  4 |        2 |
|  5 |        2 |
|  6 |        2 |
+----+----------+

虽然我已经涉足 MySQL 几年了，但我仍然是一个初学者。我已经搜索和尝试了几天，到目前为止我已经做到了这一点：

SELECT surname, forename, SchoolID 
   FROM dat 
   GROUP BY CONCAT(surname, forename, SchoolID);

例如，这是第二个标准（我计划在新表中提供自动递增键，然后加入回来）作为唯一 ID），但我对这些多重标准不太了解，所以非常感谢任何帮助！

谢谢 缺口

Answer 1

您可以通过自连接来做到这一点。不过，我会分多个步骤进行——从最好到最弱的匹配。

因此，假设 schoolID 在存在时是可靠的，这就是查找重复项的方式：（

select *
from   dat parent, 
       dat child
where  parent.id      <> child.id
and    parent.schoolid =  child.schoolid

对古老的连接语法表示歉意）。

运行一下，看看是否有任何奇怪的现象；如果你喜欢它，请将其变成更新。在 MySQL 中使用自连接进行更新有点痛苦，最简单的方法是创建一个视图来连接。

create view dupes as

select   min(parent.id) as uniqueid, child.id
from     dat parent, 
         dat child
where    parent.schoolid =  child.schoolid
group by child.id

update  ignore dat, dupes
set     dat.uniqueid =   dupes.uniqueid
where    dat.id      = dupes.id

现在，您转到第二个最强的链接，例如：

    select *
    from   dat parent, 
           dat child
    where  parent.id      <> child.id
    and    parent.uniqueID is  null
    and    child.uniqueID  is  null
    and    parent.forename = child.forename
    and    parent.surname  = child.surname
    and    parent.dob      = child.dob

再次运行它，检查数据是否有异常，然后转换为更新语句。

接下来，放宽限制 - 如果我们之前已经将父记录与另一条记录进行了匹配，那么我们可以删除对 null 的检查：

select *
from   dat parent, 
       dat child
where  parent.id      <> child.id
and    child.uniqueID  is  null
and    parent.forename = child.forename
and    parent.surname  = child.surname
and    parent.dob      = child.dob

等等。
通过从最大可能性向下工作，您可以降低相当脆弱的相似性覆盖明确链接的风险（例如，两个名叫“Kim”的人碰巧出生于 1978 年 10 月 24 日，

通过首先运行选择，然后将它们转换为更新其次，避免人为错误。

Answer 2

您尝试过“选择不同的”吗？