mysql - SQLZoo 中 SQL Join #13 的解决方案

Question

链接是http://sqlzoo.net/wiki/The_JOIN_operation .第 13 号问题。现在我可以列出所有比赛，只要至少有一个目标代码:

SELECT mdate, 
       team1, 
       SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1, 
       team2, 
       SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 FROM
    game JOIN goal ON (id = matchid)
    GROUP BY id
    ORDER BY mdate, matchid, team1, team2

但也有一些比赛的比分是0:0。我的代码无法显示这些游戏，也找不到其他可用的解决方案。真的希望有人能帮我解决这个问题。

Answer 1

SELECT mdate, 
       team1, 
       SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1, 
       team2, 
       SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 FROM
    game LEFT JOIN goal ON (id = matchid)
    GROUP BY mdate,team1,team2
    ORDER BY mdate, matchid, team1, team2

您想在 SELECT 中对没有聚合函数的列进行 GROUP BY，并使用 LEFT JOIN。