MySql 从两个表中选择聚合

Question

在 previous question 中我询问如何根据三个表返回每个用户通过的最新测试的日期，用户、测试 和通过。

|--------|---------|---------|---------|---------|---------|
|User    |Test A   |Test B   |Test C   |Test D   |Test E   |
|--------|---------|---------|---------|---------|---------|
|James   |Null     |6/3/11   |Null     |Null     |4/3/11   |
|Mark    |Null     |1/4/11   |8/5/11   |23/5/10  |Null     |
|--------|---------|---------|---------|---------|---------|

建议以下查询:

select
  u.username,
  max(case when testid = 1 then datetaken else null end) as A,
  max(case when testid = 2 then datetaken else null end) as B,
  max(case when testid = 3 then datetaken else null end) as C,
  max(case when testid = 4 then datetaken else null end) as D,
  max(case when testid = 5 then datetaken else null end) as E
from users as u left join passes as p on u.userid = p.userid
group by u.userid

事实证明，在我的实际场景中，我有两个测试表(比如包含测试 A、B 和 C 的 TestsX 以及包含测试 D 和 E 的 TestsY)和两个相应的通过表(比如 PassesX 和 PassesY)。

这是必需的，因为表具有不同的结构。但是，为简单起见，我们假设它们是相同的。它们都链接到同一个用户表。请告诉我如何相应地调整上面的选择命令，以获得相同的输出(一张表中的所有内容)？

也许这可以通过子查询和联合来实现，但我不知道如何实现？

非常感谢(我希望这是有道理的)。

Answer 1

SELECT
  u.UserName,
  px.TestA,
  px.TestB,
  px.TestC,
  py.TestD,
  py.TestE
FROM Users u
  LEFT JOIN (
    SELECT
      UserId,
      MAX(CASE TestId WHEN 1 THEN DateTaken ELSE NULL END) AS TestA,
      MAX(CASE TestId WHEN 2 THEN DateTaken ELSE NULL END) AS TestB,
      MAX(CASE TestId WHEN 3 THEN DateTaken ELSE NULL END) AS TestC
    FROM PassesX
    GROUP BY UserId
  ) px ON u.UserId = px.UserId
  LEFT JOIN (
    SELECT
      UserId,
      MAX(CASE TestId WHEN 4 THEN DateTaken ELSE NULL END) AS TestD,
      MAX(CASE TestId WHEN 5 THEN DateTaken ELSE NULL END) AS TestE,
    FROM PassesY
    GROUP BY UserId
  ) py ON u.UserId = py.UserId