SQL 帮助：使用嵌套 SELECT 计算单个查询中的行数

我正在寻找一种更好的方法来执行以下查询。我有一个看起来像这样的表：

game_id | home_team_id | away_team_id
1       | 100          | 200
2       | 200          | 300
3       | 200          | 400
4       | 300          | 100
5       | 100          | 400

我想编写一个查询来计算每个球队的主场比赛和客场比赛数量并输出以下内容：

team_id | home_games | away_games
100     | 2          | 1
200     | 2          | 1
300     | 1          | 1
400     | 0          | 2

现在，我编写了这个可以工作的怪物，但它很慢（我知道它从表中拉出整个 2,800 行两次）。

SELECT 
  home_team_id as team_id,
  (SELECT count(*) FROM `game` WHERE home_team_id = temp_game.home_team_id) as home_games,
  (SELECT count(*) FROM `game` WHERE home_team_id = temp_game.away_team_id) as away_games
  FROM (SELECT * FROM `game`) as temp_game
  GROUP BY home_team_id

SQL高手可以帮我找到更好的方法吗？我认为我的问题是我不明白如何获取团队 ID 的不同列表来进行计数查询。我敢打赌，有更好的方法，可以使用更好放置的嵌套 SELECT。提前致谢！

如果您有另一个带有 team_id 和 team_name 的表团队，那就更干净了。

SELECT team_id, team_name, 
     sum(team_id = home_team_id) as home_games, 
     sum(team_id = away_team_id) as away_games
 FROM game, team
 GROUP BY team_id
  

发生了什么事：没有 WHERE 子句会导致两个表之间出现笛卡尔积；我们按 team_id 分组以返回每个团队一行。现在每个 team_id 都有游戏表中的所有行，因此您需要对它们进行计数，但 SQL 计数函数不太正确（它会计算所有行或所有不同的行）。所以我们说 team_id = home_team_id 解析为 1 或 0，然后我们使用 sum 将 1 相加。

team_name 只是因为当我们应该说“Mud City Stranglers 有 20 个主场比赛”时，说“200 队有 20 个主场比赛”是很奇怪的。

附言。即使没有比赛（通常是 SQL 中的一个问题，有一支球队有 0 场比赛，并且该行不会显示，因为连接失败），这也会起作用。