Mysql LIMIT 在 GROUP BY 中的 ORDER

Question

我有这个游戏日志表:

+---------+------+-------+
| game_id | user | score |
+---------+------+-------+
| 1       | Nick | 10    |
| 1       | Bob  | 15    |
| 2       | Nick | 15    |
| 2       | Bob  | 10    |
| 3       | Nick | 20    |
+---------+------+-------+

在每场比赛中，获胜者只有一位得分最高的用户。我正在尝试选择每场比赛的获胜者，例如:

+---------+--------+
| game_id | winner |
+---------+--------+
| 1       | Bob    |
| 2       | Nick   |
| 3       | Nick   |
+---------+--------+

但是我所有的努力都没有结果。谁能帮帮我？

Answer 1

您可以使用以下查询:

SELECT g1.game_id, g1.user AS winner
FROM games AS g1
INNER JOIN (
   SELECT game_id, MAX(score) AS maxScore
   FROM games
   GROUP BY game_id 
) AS g2 ON g1.game_id = g2.game_id AND g1.score = g2.maxScore

Demo here