T-SQL选择连接三个表

Question

我正在使用 SQL Server 2012 上的 T-SQL 进行查询。这是一个复杂的查询，我想从 3 张表中查询一个列表。结果应该像这样： 期望输出：

ProjectId |    Title    | Manager   | Contact   | StatusId 
----------+-------------+-----------+-----------+-----------
1         |   projectX  |   1123    |  4453     |  1 
2         |   projectY  |   2245    |  5567     |  1
3         |   projectZ  |   3335    |  8899     |  1

我的3个表：

1) 项目： 项目ID、项目数据ID、成员版本ID
2) 项目数据： 项目数据ID、标题、状态ID
3) 成员： 成员ID、成员版本ID、成员类型ID、员工ID

棘手的部分是实现版本控制。因此，随着时间的推移，项目成员可能会发生变化，而且应该始终能够返回到先前的版本，这就是为什么我在Project和Members之间使用了MemberVersionId作为外键的原因。表格Project和ProjectData通过ProjectDataId相互关联。

因此，1个项目有1个OfferData和N个成员。

一些示例数据：
项目

ProjectId | ProjectDataId | MemberVersionId | 
----------+---------------+-----------------+
1         |   2           |   1             | 
2         |   3           |   1             |
3         |   4           |   1             |

项目数据

ProjectDataId |    Title    | StatusId 
--------------+-------------+-----------
2             |   projectX  |  1 
3             |   projectY  |  1
4             |   projectZ  |  1

成员：MemberTypeId 1 = 经理，MemberTypeId 2 = 联系人，3 = 其他。

MemberId | MemberVersionId | MemberTypeId | EmployeeId | 
---------+-----------------+--------------+------------+
1        |   1             |   1          |  1123      | 
2        |   1             |   2          |  4453      |
3        |   1             |   3          |  9999      |
4        |   2             |   1          |  2245      | 
5        |   2             |   2          |  5567      | 
6        |   2             |   3          |  9999      | 
7        |   3             |   1          |  3335      | 
8        |   3             |   2          |  8899      | 
9        |   3             |   3          |  9999      | 

我的当前查询看起来像这样：

SELECT ProjectId, Title, EmployeeId AS Manager, EmployeeId AS Contact, StatusId
FROM [MySchema].[Project] a,
     [MySchema].[ProjectData] b,
     [MySchema].[Members] c
WHERE a.ProjectDataId = b.ProjectDataId
  AND a.MemberVersionId = c.MemberVersionId

不幸的是，这还没有起作用。您知道如何解决这个问题吗？

谢谢。

- TimHorton

---

请提供所需的输出。 - user7715598

今日提示：转换为现代、明确的“JOIN”语法！更容易编写（无错误）、更易于阅读和维护，并且如果需要转换为外连接，也更容易实现！ - jarlh

Answer 1

像这样的内容吗？

SELECT 
    p.ProjectId, 
    pd.Title, 
    mm.EmployeeId AS Manager, 
    mc.EmployeeId AS Contact, 
    pd.StatusId
FROM 
    [MySchema].[Project] p
    INNER JOIN [MySchema].[ProjectData] pd ON pd.ProjectDataId = p.ProjectDataId
    INNER JOIN [MySchema].[Members] mm ON mm.MemberVersionId = p.MemberVersionId AND mm.MemberTypeId = 1
    INNER JOIN [MySchema].[Members] mc ON mc.MemberVersionId = p.MemberVersionId AND mc.MemberTypeId = 2;

- Richard Hansell

---

最佳答案。易于阅读，适当的连接和适当的表别名。 - Thorsten Kettner

完美运行！感谢您的帮助！ - TimHorton

Answer 2

你可以尝试这个方法：

    SELECT ProjectId, Title, C.EmployeeId AS Manager, d.EmployeeId AS Contact, StatusId
FROM [MySchema].[Project] a
INNER JOIN    [MySchema].[ProjectData] b ON A.ProjectDataId=B.ProjectDataId
LEFT JOIN (SELECT * FROM [MySchema].[Members] WHERE MemberTypeID=1) c ON a.MemberVersionId=c.MemberVersionId  
LEFT JOIN  (SELECT * FROM [MySchema].[Members] WHERE MemberTypeID=2)  d ON a.MemberVersionId=d.MemberVersionId 

- etsa

Answer 3

你必须选择两次成员，一次是为了经理，另一次是为了联系人：

SELECT ProjectId, Title, m.EmployeeId AS Manager, c.EmployeeId AS 
    Contact, StatusId
FROM [MySchema].[Project] a,
  [MySchema].[ProjectData] b,
  [MySchema].[Members] m
  [MySchema].[Members] c
WHERE a.ProjectDataId = b.ProjectDataId
  AND a.MemberVersionId = m.MemberVersionId and m.MemberTypeId = 1
  AND a.MemberVersionId = c.MemberVersionId and c.MemberTypeId = 2

- Fabio Campinho

Answer 4

请尝试这个：

 SELECT ProjectId, Title, cmanager.EmployeeId AS Manager, ccon.EmployeeId AS 
 Contact, StatusId
 from  [MySchema].[ProjectData] b
 inner join [MySchema].[Project] a on b.ProjectDataId=a.ProjectDataId
 left join [MySchema].[Members] cmanager on cmanager.MemberVersionId = 
  a.MemberVersionId and cmanager.MemberTypeId=1
 left join [MySchema].[Members] ccon on ccon.MemberVersionId = 
 a.MemberVersionId and ccon.MemberTypeId=2  

- Wintergreen

Answer 5

您的问题最简单的解决方案是在Project表中引入一个附加字段。您可以将其命名为LatestMemberVersion(int类型，保存当前最高的MemberVersionId)，这将成为关系的最新版本；或者您甚至可以添加更简单的IsLatestMemberVersion (bit类型，如果记录是最新/活动状态，则为1)。您可以使用ROW_NUMBER() OVER语句计算它们。

然后，查询将变为:

SELECT ProjectId, Title, EmployeeId AS Manager, EmployeeId AS Contact, StatusId
FROM [MySchema].[Project] a,
     [MySchema].[ProjectData] b ON a.ProjectDataId = b.ProjectDataId
     [MySchema].[Members] c ON a.MemberVersionId = c.MemberVersionId
WHERE 
a.[IsLatestMemberVersion] = 1 -- alternative is a.[LatestMemberVersion] = a.[MemberVersionId]

此外，您还可以尝试以下两种方法：

1. 您可能希望借鉴数据仓库的思想，即您需要 Slowly Changing Dimension Type 1 和 2 的组合。

2. 您可以尝试使用 SQL Server 功能，例如 Change Data Tracking。但我没有经验，这可能会导致无法解决的问题。

最后一个建议是，如果可能的话，请永远不要将连接条件写入 WHERE 子句中。这不易读，当您突然将 JOIN 更改为 LEFT JOIN 时可能会出现问题。Microsoft 自己推荐在适用时使用 ON 而不是 WHERE。

- netchkin