连接多个表以仅返回主表中每个记录的一个结果

2024-05-19 • 问答

目前我要加入三个表。我有从一个系统（旧）迁移到另一个系统（新）的数据。我需要比较此数据，以确保匹配但不匹配。我有三张桌子。一个有要移动的帐户列表。这两个系统具有不同的ID类型，因此该第一个表是两个表的所有ID的列表，以及每个移动的帐户。这就是我的基本人口。

ID1 ID2
ABC 123
ABC 123
ABC 123
DEF 456
DEF 456
DEF 456

然后我有了表2，它是旧系统中的所有数据。

ID  Fname   Lname
ABC John    Smith
ABC Tom Smith
ABC Kate    Smith
DEF Jason   Thomas
DEF Ruby    Thomas
DEF Alex    Johnson

然后表3是在新系统中找到的所有数据。

ID  Fname   Lname
123 John    Smith
123 Tom Smith
123 Kate    Smith
456 Jason   Thomas
456 Ruby    Thomas

现在，当我在ID上连接这些表时，得到的行比我需要的多得多。

我加入时会收到以下消息：

ID  Fname_old   Lname_old   ID2 Fname_new   Lname_new
ABC John    Smith   123 John    Smith
ABC John    Smith   123 Tom Smith
ABC John    Smith   123 Kate    Smith

我正在尝试将它们仅在返回匹配行的地方加入它们，如果找不到匹配项，我仍然应该从ID文件中获取ID，并从表2（旧数据）中获取数据，因为这是发送到新系统的数据。

ID1 ID2 Fname_old   Lname_old   Fname_new   Lname_new
ABC 123 John    Smith   John    Smith
ABC 123 Tom Smith   Tom Smith
ABC 123 Kate    Smith   Kate    Smith
DEF 456 Jason   Thomas  Jason   Thomas
DEF 456 Ruby    Thomas  Ruby    Thomas
DEF 456 Alex    Johnson

我使用的代码是：

Select a.ID1,a.ID2,b.fname as fname_old,b.lnam as lname_old,c.fname as fname_new,c.lname as lname_new
from table1 a
left join table2 b
on a.ID1 = b.ID
left join table3 c
on a.ID2 = c.ID

如果它只是第一个表中的重复行，则可以尝试在派生表中区分它们，如下所示：

Select a.ID1,a.ID2,b.fname as fname_old,b.lnam as lname_old,c.fname as fname_new,c.lname as lname_new
from (SELECT DISTINCT ID1,ID2 FROM table1) a
left join table2 b
on a.ID1 = b.ID
left join table3 c
on a.ID2 = c.ID

您正在ID列上加入他们。

当您有多个相同的ID并在这些ID上指定联接时，ID列通常是唯一的。

由于您需要比较数据，因此建议您查找MATCH及其工作方式，因为这似乎与您在此处查找的内容更接近。

您可以使用row_number()获得 a 匹配项：

Select a.ID1,c.lname as lname_new
from (select a.*,row_number() over (partition by id order by id) as seqnum
      from table1 a
     ) a left join
     (select b.*,row_number() over (partition by id order by id) as seqnum
      from table2 b
     ) b
     on a.ID1 = b.ID and a.seqnum = b.seqnum
     (select c.*,row_number() over (partition by id order by id) as seqnum
      from table3 c
     ) c
     on a.ID2 = c.ID and a.seqnum = c.seqnum;

注意：这不会保留原始值的“顺序”，因此任何行都可以与任何其他行匹配。为什么？ SQL表代表无序集。

如果表中有顺序，则可以在order by子句中使用该顺序以获得与该顺序一致的匹配项。

如果您有比较名字和姓氏的机会，此代码将起作用。

select DISTINCT a.ID1,b.lname as lname_old,c.fname as 
fname_new,c.lname as lname_new from table2 b
left join table1 a on a.ID1=b.ID 
left join table3 c on a.ID2=c.ID  and b.Fname=c.Fname and b.Lname=c.Lname

我的结果：

ID1 ID2 fname_old   lname_old   fname_new   lname_new
ABC 123 John    Smith   John    Smith
ABC 123 Kate    Smith   Kate    Smith
ABC 123 Tom     Smith   Tom     Smith
DEF 456 Alex    Johnson NULL    NULL
DEF 456 Jason   Thomas  Jason   Thomas
DEF 456 Ruby    Thomas  Ruby    Thomas

您说这是传输到两个系统的数据。因此，您希望所有数据都能匹配。因此，您可以减少查询以仅查找不匹配的数据（如果有）。

这是符合SQL标准的查询。您用蜂巢标记了您的请求。我对蜂巢一无所知，因此您可能需要调整查询。

select 
  t2.id as id1,t3.id as id2,t2.fname as fname_old,t2.lname as lname_old,t3.fname as fname_new,t3.lname as lname_new
from table2 t2
full outer join t3
   on t3.fname = t2.fname
  and t3.lname = t2.lname
  and exists (select null from table1 t1 where t1.id1 = t2.id and t1.id2 = t3.id)
where t2.id is null or t3.id is null;

这是完全反连接。它返回另一个表中不完全匹配的所有行。但是，并不能确定哪些偏离的行可能是成对的。您将得到如下结果：

ID1 | ID2 | Fname_old | Lname_old | Fname_new | Lname_new
----+-----+-----------+-----------+-----------+----------
DEF |     | Alex      | Johnson   |           |
GHI |     | Jone      | Miller    |           |
GHI |     | Maxx      | Miller    |           |
GHI |     | Fritz     | Miller    |           |
    | 789 |           |           | Joan      | Miller
    | 789 |           |           | Max       | Miller
    | 799 |           |           | Fritz     | Miller

如您所见，您将不得不手动检查此结果。但是理想情况下，查询根本不应该返回任何行，这只会证明一切都按预期进行，并且没有人（系统或人）弄乱数据：-）

连接多个表以仅返回主表中每个记录的一个结果

hesong0516 回答：连接多个表以仅返回主表中每个记录的一个结果

大家都在问