mysql - 外部联接-不起作用，不会显示空行

我有一个查询，可以做几件事。首先，应该获得所有dividid为1的球队。然后应该在结果栏中获取获胜次数。

它可以完成此任务，但是如果团队在结果表中没有注册数据，则不会显示TeamName 0（获胜）。有什么想法为什么会这样？

表1：活跃团队

 aid teamid divisionid
  1    1        1
  2    2        1
  3    3        1
  4    4        2

表2：团队

  teamid teamname
    1     Argos
    2     Leafs
    3     Blue Jays
    4     Ducks

表3：结果
  摆脱队友结果
   1 1'w'
   2 2'l'

最终结果目标

 divid teamname  wins
   1     Argos    1
   1     Leafs    0
   1     Bluejays 0

当前查询

SELECT activeteams.divisionid, teams.teamname, res.wins
FROM activeteams
JOIN teams ON activeteams.teamid = teams.teamid
  OUTER JOIN (
    SELECT
      COALESCE(SUM(CASE WHEN result =  'w' THEN 1 ELSE 0 END ), 0) AS wins,
      results.teamid AS teamid
  FROM results
  GROUP BY results.teamid
  )res ON teams.teamid = res.teamid
WHERE activeteams.divisionid = 1

最佳答案

你可以尝试一下：

SELECT activeteams.divisionid, teams.teamname, res.wins
FROM activeteams
INNER JOIN teams ON activeteams.teamid = teams.teamid
LEFT JOIN (
    SELECT
        COALESCE(SUM(CASE WHEN result =  'w' THEN 1 ELSE 0 END ), 0) AS wins,
        teamid
    FROM results
    GROUP BY teamid
  ) res ON teams.teamid = res.teamid
WHERE activeteams.divisionid = 1