mysql SELECT在单个表中每个类别的最佳选择

Question

可以说我有一张叫Gamers的桌子。 玩家 包含列GamerID，GameID，Score。

我有兴趣选择每个游戏中得分最高的球员。

例如，

|Gamers
|-------------------------
|GamerID | GameID | Score
|1       | 1      | 10
|2       | 1      | 10
|3       | 1      | 10
|4       | 1      | 90
|5       | 2      | 40
|6       | 2      | 10
|7       | 3      | 10
|8       | 3      | 30

查询后，我希望获得GamerID 4、5和8的行。执行该操作的查询是什么？

Answer 1

试试这个：

SELECT gamers.*
FROM gamers
INNER JOIN 
 (SELECT 
   max(score) as maxscore, 
   gameid from gamers
   GROUP BY gameid) AS b
ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) ;
ORDER BY score DESC, gameid;

这将输出：

+---------+--------+-------+
| gamerid | gameid | score |
+---------+--------+-------+
|       4 |      1 |    90 |
|       5 |      2 |    40 |
|       8 |      3 |    30 |
+---------+--------+-------+
3 rows in set (0.00 sec)

您可以做的另一种选择是创建一个临时表或视图（如果您不喜欢子查询）。

create temporary table games_score (
 SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid
);

然后：

SELECT gamers.* 
FROM gamers 
INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) 
ORDER BY score DESC, gameid;

或视图：

create or replace view games_score AS 
SELECT max(score) as maxscore, gameid FROM gamers GROUP BY gameid;

然后：

SELECT gamers.* 
FROM gamers 
INNER JOIN games_score AS b ON (b.gameid = gamers.gameid AND b.maxscore=gamers.score) 
ORDER BY score DESC, gameid;