复杂的 SQL 连接帮助！

Question

（为了快速回顾，请参阅标题为“数据”的部分。只能在图 A 和图 B 中获得结果，我想要的是期望的结果，尝试了答案部分中发布的所有内容。）

所有，我一直在努力聪明地开始在我的应用程序中使用联接。天哪，我花了一段时间，但我想我已经掌握了窍门，但我无法让这个查询工作。

请帮我！这是连接：

SELECT     TOP (100) PERCENT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title, a.Description, a.ApplicableDate, 
                  a.LocalId, at.Name AS AccomplishmentTypeName, a.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, 
                  ugo.Name AS OtherUserGroupName
FROM         dbo.Accomplishment AS a INNER JOIN
                  dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId INNER JOIN
                  dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id INNER JOIN
                  dbo.Area AS al ON al.Id = aal.AreaId INNER JOIN
                  dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id INNER JOIN
                  dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN
                  dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN
                  dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId INNER JOIN
                  dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN
                  dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN
                  dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId LEFT OUTER JOIN
                  dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId
WHERE     (ug.LocalId = 2) AND (ugo.LocalId <> 2) AND (ugto.LocalId = 4)
ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

现在这有点复杂，但我正在做的是从所有这些相关表中选择信息，其中有一个成就，该成就有一个与之关联的用户，其中该用户有一个用户组 localId = 2，但如果他们确实有的话，我还想返回该用户所在的所有用户组，其中 usertype.localid = 4。

让我给出一些示例数据，希望能够清楚地说明这一点，现在我在数据库中有 3 项成就，看起来像这样：

--------(DATA)

Accomplishment 1:
   User: John
      Usergroups: Group A (Of UserGroupType 2), Group B (Of UserGroupType 3), 
                  Group C (Of UserGroupType 4), Group D (Of UsergroupType 4)
Accomplishment 2:
   User: John
      Usergroups: Group A (Of UserGroupType 2), Group B (Of UserGroupType 3), 
                  Group C (Of UserGroupType 4), Group D (Of UsergroupType 4)
Accomplishment 3:
   User: Sue
      Usergroups: Group A (Of UserGroupType 2)

现在我上面的连接结果有 4 行：

--------(图 A)：

 Accomplishment 1, John, Group A, Group C
 Accomplishment 1, John, Group A, Group D
 Accomplishment 2, John, Group A, Group C
 Accomplishment 2, John, Group A, Group D

现在，这是正确的，因为用户拥有的每个 userGroup 都有一行，其 usergrouptype.localid 为 4，但是，我怎样才能使其也显示 Accomplishment 3??我首先将所有联接设为内部联接，然后认为制作最后几个左联接将处理它以返回用户，即使它没有 usergrouptype localid 4 的用户组，但事实并非如此。基本上，我希望它返回 A 组中的用户的任何成就，并且如果他们有任何用户组类型为 4 的用户组，也返回所有这些用户组。

有什么想法吗？如果我不清楚，请告诉我，这非常复杂，我可能解释得不够。

编辑：

（要快速回顾，请参阅标题为“数据”的部分。只能在图 A 和图 B 中获得结果，我想要的是期望的结果，尝试了答案部分中发布的所有内容。）

HLGEM 和 Tom Hs 的结果是相同的，并且更接近我需要的结果，但仍然偏离。现在，我在图 B 中得到了 9 个结果。

------（图 B）：

Accomplishment 1, John, Group A, Group C
Accomplishment 1, John, Group A, Group D
Accomplishment 1, John, Group A, Null
Accomplishment 1, John, Group A, Group B (Group B is UsergroupType 3)
Accomplishment 3, Sue, Group A, Null
Accomplishment 2, John, Group A, Group C
Accomplishment 2, John, Group A, Group D
Accomplishment 2, John, Group A, Null
Accomplishment 2, John, Group A, Group B (Group B is UsergroupType 3)

因此，出于某种原因，它包含 John 的空行，即使他有用户组类型为 4 的用户组，并且其中包含具有用户组类型 3 的组 B 的行，不应显示。

再次编辑：

（要快速回顾，请参阅标题为“数据”的部分。只能在图 A 和图 B 中获得结果，我想要的是所需的结果，尝试了答案部分中发布的所有内容。）

是的，我真的被这个难住了，我已经尝试了几乎所有在 where 子句中放入一些内容的组合，它要么是 4 行，没有成就 3，要么是 9 行，其中用户 John 每个成就获得额外的两行，或者是 6 行，没有成就完成 3 以及 usergrouptype 3 用户组的额外行。更令人困惑的是，在最后一行添加 ugto.localid = 4 似乎对结果没有任何影响。它显示了我的用户组，其用户组类型为 3，但我在查询中没有看到任何可以这样做的地方。

编辑 5/02/10

（要快速回顾，请参阅标题为“数据”的部分。只能在图 A 和图 B 中获得结果，我想要的是期望的结果，尝试了答案中发布的所有内容部分。）

我认为我想要的东西是不可能的？无论如何，我想要的结果是这 5 行：

--------(期望的结果):

Accomplishment 1, John, Group A, Group C
Accomplishment 1, John, Group A, Group D
Accomplishment 2, John, Group A, Group C
Accomplishment 2, John, Group A, Group D
Accomplishment 3, Sue, Group A, Null

这是我尝试过的所有内容及其给出的结果的列表：（请原谅蹩脚的 SSMS 格式）

下面的所有内容都基于此基本查询：

开始查询/尝试 1：

SELECT     TOP (100) PERCENT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title, a.Description, a.ApplicableDate, 
                  a.LocalId, at.Name AS AccomplishmentTypeName, al.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, 
                  ugo.Name AS OtherUsergroupName
  FROM         dbo.Accomplishment AS a INNER JOIN
                  dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId INNER    JOIN
                  dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id INNER JOIN
                  dbo.Area AS al ON al.Id = aal.AreaId INNER JOIN
                  dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id INNER JOIN
                  dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN
                  dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN
                  dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId INNER JOIN
                  dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN
                  dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN
                  dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId AND ugo.LocalId <> 2 LEFT OUTER JOIN
                  dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId AND ugto.LocalId = 4
 WHERE     (ug.LocalId = 2)
 ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

结果： 图 B (参见上面的方法） 为什么不好： John 显示为 Null，并且显示类型 3 的用户组。

尝试 2：

尝试 1，减去所有内联连接过滤（仍保留最后两个的左连接）和以下 where 语句：

WHERE (ug.LocalId = 2)
  AND (ugo.LocalId <> 2 OR ugo.LocalId is null)
  AND (ugto.LocalId = 4 OR ugto.LocalId is null) 

结果： 图A（参见上面的方法） 为什么不好： 不包括成就 3

尝试 3：

与尝试 1 相同，但从该行下面开始进行更改：

 dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN

该行下面的更改：（

                  dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN
                  dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId AND ug.LocalId = 2 INNER JOIN
                  dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN
                  dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN
                  dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId AND ugo.LocalId <> 2 LEFT OUTER JOIN
                  dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId
WHERE     (ugto.LocalId = 4)
ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

要快速回顾，请参阅标题为“数据”的部分只能得到图 A 和图 B 中的结果，我想要的是所需的结果，尝试了答案部分中发布的所有内容。）

Answer 1

@ChaosPandion 关于重新格式化的评论是个好主意，但也许您需要一些帮助来理解什么是好的格式化。好吧，每个人的情况可能都不同:-)，但如果我编写您的查询，我会将其格式化如下：

SELECT  TOP (100) PERCENT
          a.Id,
          a.DateCreated,
          a.DateModified,
          a.LastUpdatedBy,
          a.AccomplishmentTypeId,
          a.Title,
          a.Description,
          a.ApplicableDate,  
          a.LocalId,
          at.Name AS AccomplishmentTypeName,
          a.Name AS AreaName,
          u.FirstName,
          u.LastName,
          ug.Name AS UserGroupName,  
          ugo.Name AS OtherUserGroupName 
FROM dbo.Accomplishment AS a
INNER JOIN dbo.AccomplishmentType AS at
  ON at.Id = a.AccomplishmentTypeId
INNER JOIN dbo.AccomplishmentArea AS aal
  ON aal.AccomplishmentId = a.Id
INNER JOIN dbo.Area AS al
  ON al.Id = aal.AreaId
INNER JOIN dbo.UserAccomplishment AS ua
  ON ua.AccomplishmentId = a.Id
INNER JOIN dbo.[User] AS u
  ON u.Id = ua.UserId
INNER JOIN dbo.UserUserGroup AS uug
  ON uug.UserId = u.Id
INNER JOIN dbo.UserGroup AS ug
  ON ug.Id = uug.UserGroupId
INNER JOIN dbo.UserGroupType AS ugt
  ON ugt.Id = ug.UserGroupTypeId
INNER JOIN dbo.UserUserGroup AS uugo
  ON uugo.UserId = u.Id
LEFT OUTER JOIN dbo.UserGroup AS ugo
  ON ugo.Id = uugo.UserGroupId
LEFT OUTER JOIN dbo.UserGroupType AS ugto
  ON ugto.Id = ugo.UserGroupTypeId 
WHERE ug.LocalId = 2 AND
      ugo.LocalId <> 2 AND
      ugto.LocalId = 4
ORDER BY a.DateCreated DESC,
         u.LastName,
         u.FirstName,
         u.Id,
         UserGroupName

我认为这种格式更易于阅读。

分享并享受。

Answer 2

在学习左连接时，您犯了一个典型的错误，您在 where 子句中添加了条件，将它们变成了内连接。

尝试一下，

SELECT     a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title,
a.Description, a.ApplicableDate,  a.LocalId, at.Name AS AccomplishmentTypeName, 
a.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUserGroupName 
FROM        dbo.Accomplishment AS a 
INNER JOIN  dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId 
INNER JOIN  dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id 
INNER JOIN  dbo.Area AS al ON al.Id = aal.AreaId 
INNER JOIN  dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id 
INNER JOIN  dbo.[User] AS u ON u.Id = ua.UserId 
INNER JOIN  dbo.UserUserGroup AS uug ON uug.UserId = u.Id 
INNER JOIN  dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId 
INNER JOIN  dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId 
INNER JOIN  dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id 
LEFT OUTER JOIN  dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId  AND ugo.LocalId <> 2
LEFT OUTER JOIN  dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId  AND ugto.LocalId = 4
WHERE     ug.LocalId = 2 
ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName 

请参阅此链接以获取对此的解释：
http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Answer 3

我不确定我是否完全理解您想要做什么，但我认为您需要将您的标准转移到 LEFT OUTER JOIN 中。如果将其放入 WHERE 子句中，那么如果 LEFT OUTER JOIN 未找到匹配项，这些列将显示为 NULL，因此它们将无法通过 WHERE 子句检查。您实际上是将 LEFT OUTER JOIN 转变为 INNER JOIN。

FROM
    dbo.Accomplishment AS a
INNER JOIN dbo.AccomplishmentType AS at ON
    at.Id = a.AccomplishmentTypeId
INNER JOIN dbo.AccomplishmentArea AS aal ON
    aal.AccomplishmentId = a.Id
INNER JOIN dbo.Area AS al ON
    al.Id = aal.AreaId
INNER JOIN dbo.UserAccomplishment AS ua ON
    ua.AccomplishmentId = a.Id
INNER JOIN dbo.[User] AS u ON
    u.Id = ua.UserId
INNER JOIN dbo.UserUserGroup AS uug ON
    uug.UserId = u.Id
INNER JOIN dbo.UserGroup AS ug ON
    ug.Id = uug.UserGroupId AND
    ug.localid = 2
INNER JOIN dbo.UserGroupType AS ugt ON
    ugt.Id = ug.UserGroupTypeId
INNER JOIN dbo.UserUserGroup AS uugo ON
    uugo.UserId = u.Id
LEFT OUTER JOIN dbo.UserGroup AS ugo ON
    ugo.Id = uugo.UserGroupId AND
    ugo.localid <> 2
LEFT OUTER JOIN dbo.UserGroupType AS ugto ON
    ugto.Id = ugo.UserGroupTypeId
WHERE
    ugto.localid = 4
ORDER BY
    a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

Answer 4

这是我到目前为止重写的内容：

SELECT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, 
       a.Title, a.Description, a.ApplicableDate, a.LocalId, 
       at.Name AS AccomplishmentTypeName, a.Name AS AreaName, u.FirstName, 
       u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUserGroupName
  FROM dbo.Accomplishment a 
  JOIN dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId 
  JOIN dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id 
  JOIN dbo.Area AS al ON al.Id = aal.AreaId 
  JOIN dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id 
  JOIN dbo.[User] AS u ON u.Id = ua.UserId 
  JOIN dbo.UserUserGroup AS uug ON uug.UserId = u.Id 
  JOIN dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId 
  JOIN dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId 
  JOIN dbo.UserGroupType AS ugto ON ugto.Id = ug.UserGroupTypeId
 ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName

您对 UserUserGroup 有一个冗余联接，并且您对 UserGroup 的联接可以合并，因为 LEFT JOIN 标准会取消在之前连接到同一个表时完成。

Answer 5

你加入一切都很好。这就是过滤！

如果您编写的过滤器不允许左连接表中存在空值，那么您的过滤器将删除左连接允许的其他记录。

ugo 和 ugto 都是通过 LEFT JOIN

WHERE (ug.LocalId = 2)
  AND (ugo.LocalId <> 2 OR ugo.LocalId is null)
  AND (ugto.LocalId = 4 OR ugto.LocalId is null) 

PS 达到的 - 混合 JOIN 和 FILTERING 标准（都在 ON 中或都在 WHERE 中）会导致疯狂。保持理智！

---

编辑：在连接中发现错误：

INNER JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id

应该是

LEFT JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id