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

链接为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。我的代码无法显示这些游戏，也找不到其他可用的解决方案。真的希望有人可以帮助我解决这个问题。

最佳答案

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。

关于mysql - SQLZoo中SQL Join的＃13的解决方案，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25837329/