为何COUNT()语法在SQLZoo JOIN挑战13中无法正常工作？

问题场景 #

我在完成SQLZoo JOIN章节的挑战13时，自己写了一段SQL语句来统计每场比赛两队的进球数，但结果出现了奇怪的问题：当两支球队都有进球时，统计出的数字是两队进球数的乘积，而不是各自的实际进球数；只有当其中一支球队没进球时，结果才是正确的。

我最初尝试的SQL语句如下：

SELECT ga.mdate, ga.team1, COUNT(go1.teamid), ga.team2, COUNT(go2.teamid) 
FROM game ga 
LEFT JOIN goal go1 ON id=go1.matchid AND go1.teamid=ga.team1 
LEFT JOIN goal go2 ON id=go2.matchid AND go2.teamid=ga.team2 
GROUP BY ga.mdate, ga.team1, ga.team1, ga.team2 
ORDER BY ga.mdate, go1.matchid, ga.team1, ga.team2

为什么会出错？ #

核心问题出在两次LEFT JOIN带来的笛卡尔积：

• 假设一场比赛中，team1进了3球，team2进了2球。第一次LEFT JOIN会把game表和team1的3条进球记录关联，得到3条记录；第二次LEFT JOIN再把这3条记录和team2的2条进球记录关联，最终会生成3×2=6条临时记录。
• 当你用COUNT(go1.teamid)时，这6条记录里的go1.teamid都是非空的，所以统计结果变成了6（而不是实际的3）；同理COUNT(go2.teamid)也会统计出6（而不是实际的2）。
• 只有当其中一支球队没有进球时，对应的LEFT JOIN后没有匹配到记录，临时记录数等于另一队的进球数，COUNT才能得到正确结果。

另外，你的GROUP BY子句里重复写了ga.team1，这属于冗余语法，但不是导致错误的直接原因。

正确的解决方法 #

这里提供两种可靠的方案，都能避免笛卡尔积的问题：

方案1：用子查询预统计每队每场的进球数 #

先在子查询里按比赛和球队分组统计进球数，再和game表关联：

SELECT
  ga.mdate,
  ga.team1,
  COALESCE(t1.goals, 0) AS team1_goals,
  ga.team2,
  COALESCE(t2.goals, 0) AS team2_goals
FROM game ga
LEFT JOIN (
  SELECT matchid, teamid, COUNT(*) AS goals
  FROM goal
  GROUP BY matchid, teamid
) t1 ON ga.id = t1.matchid AND ga.team1 = t1.teamid
LEFT JOIN (
  SELECT matchid, teamid, COUNT(*) AS goals
  FROM goal
  GROUP BY matchid, teamid
) t2 ON ga.id = t2.matchid AND ga.team2 = t2.teamid
ORDER BY ga.mdate, ga.id, ga.team1, ga.team2;

用COALESCE是为了把NULL（没进球的情况）转换成0，让结果更直观。

方案2：用条件聚合简化查询 #

只连接一次goal表，通过CASE语句区分两队的进球，再聚合统计：

SELECT
  ga.mdate,
  ga.team1,
  COUNT(CASE WHEN go.teamid = ga.team1 THEN 1 END) AS team1_goals,
  ga.team2,
  COUNT(CASE WHEN go.teamid = ga.team2 THEN 1 END) AS team2_goals
FROM game ga
LEFT JOIN goal go ON ga.id = go.matchid
GROUP BY ga.mdate, ga.id, ga.team1, ga.team2
ORDER BY ga.mdate, ga.id, ga.team1, ga.team2;

这种方式更简洁，因为COUNT会自动忽略CASE语句里的NULL值（当条件不满足时，CASE返回NULL，不会被计数）。

内容的提问来源于stack exchange，提问作者Luciano Soares