mysql - 我使用 CASE 语句的查询有什么问题

Question

我正在尝试解决 http://www.sqlzoo.net/wiki/The_JOIN_operation 上的 #13|

“如图所示，列出每场比赛以及每支球队的进球数。这将使用“CASE WHEN”，这在之前的任何练习中都没有解释过。”

这是我的查询:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game INNER JOIN goal ON matchid = id
GROUP BY game.id
ORDER BY mdate,matchid,team1,team2

我得到的结果是“行数太少”。我不明白我哪里错了。

Answer 1

INNER JOIN 只返回有目标的游戏，即 goal 和 game 表之间的匹配。

您需要的是 LEFT JOIN。您需要第一个表 game 中的所有行，但它们不需要匹配 goal 中的所有行，根据我对您的 0-0 评论问题:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game LEFT JOIN goal ON matchid = id
GROUP BY game.id,game.mdate, game.team1, game.team2 
ORDER BY mdate,matchid,team1,team2

这将返回 6 月 27 日葡萄牙和西类牙之间的 0-0 结果，您的初始答案遗漏了这一结果。