mysql - 查找重复项，然后使用主表中的 id 更新表，然后删除表中的记录

Question

我的问题如下我有两个表，personaldata 表包含员工的记录，whoisonboard 表包含员工入职时的记录。我们在个人数据表中获得了重复项，并且当人们 checkin 时，这些不同的 ID 也存储在 whoisonboard 表中。找到重复项没有问题。

删除个人表中whoisonboard表中不存在的所有数据) 从个人数据中删除 id 不在的位置(从 whoisonboard 中选择 personid)

这将删除任何没有上过任何船只的人，因为 whoisonboard 表中不会有记录。

我们会删除 whoisonboard 中在个人数据中没有相应记录的所有记录 - 这是为了确保不存在孤立的 whoisonboard 记录

  DELETE FROM whoisonboard WHERE personid NOT IN (SELECT id FROM personaldata)

我们可以在个人数据表中找到所有重复项，并给出 whoisonboard，为了识别重复项，查询会查找字段名称、date_of_birth 和国籍是否相同。

 select a.id as personid, b.id as whoisid, b.personid whoispersonid, a.names, a.date_of_birth, a.nationality 
 from personaldata a
 join whoisonboard b on a.id = b.personid 
   where  (a.names, a.date_of_birth, a.nationality) in (
     select a.names, a.date_of_birth, a.nationality
      from personaldata a
      group  by a.names, a.date_of_birth, a.nationality
      having count(distinct a.id) > 1
    )
  order by date_of_birth desc

然后我们可以发出此 SQL 语句来更新记录，然后删除重复项的孤立记录，如果我们有很多重复项，则执行此操作可能会很耗时。

UPDATE whoisonboard SET personid = '74777a8e-343c-11e9-a2bb-000c2912dae9' 
WHERE `id` LIKE '5bd2c268-ec4d-11e8-ab89-000c29045ceb'

最后，我会用

删除孤儿记录

从 id 不在的个人数据中删除(从 whoisonboard 中选择 personid)

我一直在尝试构建一条可以一次性完成更新的 SQL 语句，但失败了

 update whoisonboard set personid = final_id 
 from whoisonboard 
 join personaldata on personaldata.id = whoisonboard.personid 
 join ( select names, date_of_birth, nationality, min(id) as final_id from 
 personaldata group by names, date_of_birth, nationality ) min_ids on 
 min_ids.names = personaldata.names

我在执行时遇到错误，我想知道我尝试做的事情是否可以在一个sql语句中实现，问题是，当我们试图避免重复时，它们确实会发生，最好有一种简单的刷新方法数据库。

Answer 1

我这样做只是为了纠正我的数据仓库中的类似问题。

我包含了很多伪代码，因为这很长，而且我不想为您的情况测试它。另外，我的代码是针对 SQL Server 的，因此该代码可能不适合您。所以这是这个概念......

创建一个临时表来存储所有自然键代码组合和 id(每个自然键有许多 id)。

create table #p (id [auto_increment], personkey, personid)
insert #p select lastname + ',' + firstname, personid 
from personaldata 
order by 1

创建一个临时表来存储每个自然键值的最小 id(每个自然键一个 id)。

create table #pmin (id [auto_increment], personkey, personid)
insert #pmin
select personkey, min(personid) as personid
from #p
group by personkey
order by 1

循环#pmin的记录，更新whoisonboard，整理persondata。

declare variables
initialize variables

loop through #pmin from id = 1 to [max]
begin loop
    increment counter
    store the values of personkey and personid for this iteration
        select @thisVal = personkey, @idMin = personid from #pmin where id = @i
    store all values of personid for this personkey from #p (I used a table variable @a)
        insert @a select personid from #p where personkey = @thisVal
    update whoisonboard set personid = min personid for all values of personid
        update whoisonboard set personid = @idMin where personid in (select personid from @a)
    delete all but the first persondata record for this iteration
        delete persondata where personid in (select personid from @a where personid <> @idMin)
end loop

我的代码还包括我需要为我的案例执行的一些其他步骤，以及大量测试/数据比较代码，以验证我在每个步骤中都做了正确的事情。

• 报告每个人之前和之后的日期、工资或其他信息。它们应该完全匹配。
• 验证您是否获得了第一条和最后一条记录。
• 您认为合适的其他检查

总共，我的代码大约有 600 行。 (这就是为什么我不想在这里讨论到这个程度。)但是我在这里提供的内容应该是完成您的任务的足够大纲。