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的结果，而您最初的回答遗漏了这一结果。

由于两个表中都有同名的列，因此无需指定当前列的来源。

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 matchid = id

GROUP BY mdate, team1, team2

ORDER BY mdate,matchid,team1,team2

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

我使用子查询做到了这一点。
这里唯一的技巧是理解我们需要使用LEFT JOIN而不是INTER JOIN，因为如果我们使用INTER JOIN(0-0的情况)，那么有0个进球的比赛不会在我们的查询中捕捉到
希望下面的查询能有所帮助！

select t1.mdate,t1.team1,sum(t1.score1),t1.team2,sum(t1.score2) from(
SELECT mdate,
  team1,
  CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1,
  team2,
  CASE WHEN teamid=team2 THEN 1 ELSE 0 END score2
  FROM game JOIN goal ON matchid = id)t1
group by t1.mdate,t1.team1,t1.team2
order by mdate,team1,team2

对于MySQL，@mjsquu的答案有效，但for SQL Server和其他一些方言需要在GROUP BY中使用specify all the columns。因此，如果您熟悉MySQL，请记住，在某些方言中，您必须更具体地使用GROUP BY(遗憾的是...)。在适用于SQL Server的脚本下面：

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

`

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

`