我应该用哪种连接来连接这些表？

Question

我有以下三个表，我试图连接在一起，并创建一个概览的所有桌面和谁有一个分配给他们的UserID，如果有的话。

dbo.Users
ID    Name     Lastname   JobTitle
118   Ryan     Doe        Field Engineer
119   Jessica  Braun      Technical Consultant
120   Daniel   Sous       Web developer
121   Amy      Amyson     Intern
.. etc

dbo.LightDesktops
ID    Model     MACAddress          UserID
1     HP1234    AA:AA:AA:AA:AA:AA   118
2     HP1234    BB:BB:BB:BB:BB:BB   121
3     HP1234    AA:BB:BB:AA:BB:AA   NULL
4     HP1234    BB:AA:BB:AA:AA:BB   124

dbo.MediumDesktops
ID   Model      MACAddress          UserID
1    HP12PRO    AA:AB:AA:BB:AA:BA   132
2    HP12PRO    BB:BA:AB:BA:BB:AA   119
3    HP12PRO    AA:BA:BA:AB:AA:BA   123
4    HP12PRO    BB:BB:BB:AB:BA:BB   241

我设法弄清楚了如何在每种类型的桌面上都这样做，例如LightDesktops：

SELECT * FROM LightDesktops LEFT OUTER JOIN Users ON LightDesktops.UserID = Users.UserID

这将向我展示一个很好的概述，轻台式机与他们的信息，以及谁有一个分配给如果任何。

如果我想了解一下没有使用的轻型台式机，那么我可以这样做。

SELECT * FROM LightDesktops LEFT OUTER JOIN Users ON LightDesktops.UserID = Users.UserID WHERE LightDesktops.UserID IS NULL

除了包含桌面信息的两个表之外，我如何实现相同的结果？我试图使用一个UNION，但是返回了很多重复的值。

Answer 1

使用UNION将轻型和中型桌面表合并为一个数据集不应该给您提供重复的数据集，除非表包含行中的重复值，可以在、SELECT子句中的所有列、和使用UNION ALL。如果您知道您的表具有唯一的值，请使用UNION ALL来提高性能。

我将将两个表组合在一个公共表表达式(cte)中，然后将结果表与一个LEFT OUTER JOIN连接到您的用户表中，该表还可以被过滤以查找没有匹配的WHERE [user].[UserID] IS NULL、NB的条目，这些条目将在您的桌面表中返回用户已被删除的孤立行；或者，删除左外部联接并使用WHERE [desktop].[UserID] IS NULL只返回没有辅助用户的dekstop。

您可以尝试以下代码；

WITH cte_Desktop AS
(
    SELECT
        [ID] as [DesktopID],
        'Light Desktop' as [DekstopType],
        [Model],
        [MACAddress],
        [UserID]
    FROM [dbo].[LightDesktops]

    UNION

    SELECT
        [ID],
        'Medium Desktop',
        [Model],
        [MACAddress],
        [UserID]
    FROM [dbo].[MediumDesktops]
)

SELECT
    [desktop].*

FROM cte_Desktop AS [desktop]
LEFT OUTER JOIN [dbo].[Users] AS [user]
    on [user].[UserID] = [desktop].[UserID]

WHERE [user].[UserID] IS NULL

Answer 2

试一试

WITH all as (
select model , userid from LightDesktops
union
select model , userid from MediumDesktops
)

select * FROM all where UserId IS NULL

Answer 3

使用Full join获取所有在场用户的报告。它会给你完整的报告。在此结果的顶部，对userid的查询为空。

  SELECT U.ID userid,
       U.NAME,
       LD.USERID LD_USERID,
       LD.MODEL LIGHT_MODEL,
       LD.MACADDRESS LIGHT_MAC,
       LM.USERID LM_USERID,
      LM.MODEL MEDIUM_MODEL,
      LM.MACADDRESS MEDIUM_MAC
FROM   #USERS U
      FULL OUTER JOIN #LIGHTD LD
         ON (U.ID = LD.USERID )
      FULL OUTER JOIN #LIGHTM LM
         ON (LM.USERID = U.ID)