mysql - 写一个查询来找出哪支球队赢得了最多的比赛

Question

在命令行上的 MySQL 中，我试图找出哪支球队赢得了最多的比赛。如果多支球队的获胜次数相同，那么我需要将它们与获胜次数一起显示。

我有两个关系：

Team
+---------+-----------+------+----+
| name    | shortName | abbr | id |
+---------+-----------+------+----+

Game
+---------+--------------+--------------+------------+------------+
| game_id | home_team_id | away_team_id | score_home | score_away |
+---------+--------------+--------------+------------+------------+

（home_team_id和away_team_id都是 Team.id 的外键）

老实说，我对这个很迷茫。我相信我需要：

• 比较 score_home 和 score_away 以获得胜利
• 总和每支球队的胜利次数
• 找到与最大获胜次数相关的 team_id
• 找到与该 team_id 对应的团队名称

而且我认为这些步骤中的每一个都将是它自己的子查询，所以这是我最好的尝试（非常错误）：

> SELECT t.name
  FROM (SELECT team_id
        FROM (SELECT team_id, SUM(games_won) as wins
              FROM (SELECT home_team_id as team_id, COUNT(*) as games_won                        
                    FROM Game
                    WHERE score_home > score_away

                    UNION

                    SELECT away_team_id as team_id, COUNT(*) as games_won                        
                    FROM Game
                    WHERE score_away > score_home) game
                  )
            )
      )
  INNER JOIN Team t
       ON t.id = game.team_id;

请帮忙。任何解释将不胜感激

标签： mysql

Answer 1

尝试这个

SELECT 
  t1.id                                        AS id,
  IFNULL(home_games.cnt + away_games.cnt, 0)   AS games_won
FROM team t1
LEFT JOIN(
  SELECT home_team_id, COUNT(*) AS cnt
  FROM game
  WHERE score_home > score_away
  GROUP BY home_team_id
) home_games ON home_games.home_team_id = t1.id
LEFT JOIN(
  SELECT away_team_id, COUNT(*) AS cnt
  FROM game
  WHERE score_away > score_home
  GROUP BY away_team_id
) away_games ON away_games.away_team_id = t1.id
ORDER BY games_won DESC