SQL联合查询但过滤部分重复记录

Question

以下是我所理解的内容：

我有两个数据库表：People和Contacts。 People是原始表，而Contacts是新表，用于覆盖People表中的数据（在实际情况下，这些是不同的数据库）。

在最初的加载时，所有人员记录都将被复制到联系人表中，但在初始加载之后，可以向People或Contacts表中添加新人员，也可以将联系人设置为已删除。

我希望返回所有人员，但希望Contacts表优先，例如：

示例：

People表

     PersonGuid | PersonId | FirstName   | LastName
     -----------|----------|-------------|-----
1      xxxx1    | 1        | John        | Smith
2      xxxx2    | 2        | Jane        | Doe
3      xxxx3    | 3        | John        | Doe
4      xxxx4    | 4        | Alice       | James
5      xxxx5    | 5        | Claire      | Wheeler

联系人表 （Jane Doe 被设置为已删除，John Doe 由于在初始导入后添加，所以不存在，Emma Green 已被添加。对于未被操作过的条目，已删除为NULL）

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx3    | NULL     | Emma        | Green    |  0
4      xxxx4    | 4        | Alice       | James    |  0
5      xxxx5    | 5        | Claire      | Wheeler  |  NULL

我想要返回的是：（所有记录，但将Jane Doe设置为已删除，因为她在联系人表中）

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx3    | 3        | John        | Doe      |  0
4      xxxx4    | NULL     | Emma        | Green    |  0
5      xxxx5    | 4        | Alice       | James    |  0
6      xxxx6    | 5        | Claire      | Wheeler  |  NULL

但我所得到的是： （简·多两次）

     ID         | LinkedId | FirstName   | LastName |  Deleted
     -----------|----------|-------------|----------|----------
1      xxxx1    | 1        | John        | Smith    |  NULL
2      xxxx2    | 2        | Jane        | Doe      |  1
3      xxxx2    | 2        | Jane        | Doe      |  0
4      xxxx3    | 3        | John        | Doe      |  0
5      xxxx4    | NULL     | Emma        | Green    |  0
6      xxxx5    | 4        | Alice       | James    |  0
7      xxxx6    | 5        | Claire      | Wheeler  |  NULL

我想合并这两个表格，但是不知道如何过滤筛选，使联系人数据覆盖人员数据，同时还要获取两个表格的所有数据？

Select PersonGuid as Id,
Personid,
Firstname,
LastName,
cast(0 as bit) as deleted
From DB1.People

Union

Select
Id,
LinkedId as PersonId,
FirstName,
LastName,
Deleted 
from DB2.Contacts

我尝试过搜索，但我不确定该用什么关键词，所以我的术语可能有误。

编辑-实际查询远非我的示例简单，并且每个查询的来源都是约6个表的连接。 我试图制作一个简单的示例以使其清晰，但不确定这是否使解决方案不同？

- Bex

---

简·多和约翰·多是应该有相同的ID还是这是一个打字错误？艾玛·格林和约翰·多也是同样的问题吗？ - Chris Pickford

刚刚注意到这个问题。它们不应该有相同的ID。我现在会进行编辑。 - Bex

Answer 1

请尝试使用以下查询。

Select PersonGuid as Id,
Personid,
Firstname,
LastName,
cast(0 as bit) as deleted
From DB1.People
where not exists (select 1 from Contacts WHERE personID=LinkedId and deleted=1)

Union

Select
Id,
LinkedId as PersonId,
FirstName,
LastName,
Deleted 
from DB2.Contacts

- Unnikrishnan R

---

在“真实”的查询中，From 实际上连接了约 6 个表，因此是否有其他方法可以做到这一点，因为我认为在一个如此大的查询中添加 not exists 不太可能。 - Bex

Answer 2

看起来你不想要一个联合（Union） - 你更想要一个连接（Join）。FULL OUTER JOIN 返回两个表的所有行，并允许你通过在ID和LinkedID上连接来识别同一人的行。

然后，你可以使用 ISNULL 函数从联系人（Contacts）中选择存在的行，或者从人员（Person）中选择不存在的行。

Select ISNULL(C.ID,P.PersonGuid) as Id,
       ISNULL(C.LinkedID,Personid) as PersonId,
       ISNULL(C.Firstname,P.FirstName) as FirstName,
       ISNULL(C.Lastname,P.Lastname) as LastName,
       ISNULL(C.Deleted,cast(0 as bit)) as deleted
From DB1.People P
FULL OUTER JOIN DB2.Contacts C on P.PersonID = C.LinkedId

- BeanFrog

---

我可能把我的例子简化得太多了。实际查询的来源是在大约6个表上进行的联接。您认为是否应该将我的每个查询插入到临时表中并将它们连接起来？ - Bex

要么将您的连接替换为 FROM (这里是人员的大连接) P FULL OUTER JOIN (这里是联系人的大连接) C ON .....，要么构建一个连接表的视图，并引用它。 - BeanFrog

Answer 3

我认为这可能做到你想要的：

select pc.*
from (select pc.*,
             row_number() over (partition by firstname, lastname order by priority) as seqnum
from ((select ID, LinkedId, FirstName, LastName, Deleted, 1 as priority
             from contacts
            ) union all
            (select PersonGuid, PersonId, FirstName, LastName, 0 as Deleted, 2
             from persons
            )
           ) pc
     ) pc
where seqnum = 1;

我不确定如何在表格之间识别同一个人。这里使用名称，因为这是你用语言描述问题的方式。

思路如下：

• 使用UNION ALL（而不是UNION）将所有表格的行合并。
• 添加优先级列。
• 使用ROW_NUMBER()根据优先级选择第一个值（如果使用例如id来匹配表格，则PARTITION BY会发生变化）。
• 最后，选择行号为1的行。

- Gordon Linoff

Answer 4

请问您能否尝试一下这个：

SELECT p.PersonGuid, 
    p.PersonId, 
    p.FirstName, 
    p.LastName, 
    CASE WHEN c.LinkedId IS NULL THEN 0 ELSE c.DELETED END deleted 
FROM people p
LEFT JOIN contact c ON c.LinkedId = p.PersonId
UNION
SELECT * FROM #contact a WHERE a.LinkedId IS NULL

- Shushil Bohara

Answer 5

UNION会返回没有重复记录的结果集，所以要用联系人数据覆盖个人数据，需要改变查询的顺序：

Select Id, LinkedId as PersonId, FirstName, LastName, Deleted 
from DB2.Contacts

UNION

Select PersonGuid as Id,Personid,Firstname,LastName,cast(0 as bit) as deleted 
From DB1.People

要获取所有记录，请使用UNION ALL而不是UNION。这将返回重复的记录。

- owczarek