mysql SELECT在单个表中每个类别的最佳选择 [英] mysql SELECT best of each category in a single table
本文介绍了mysql SELECT在单个表中每个类别的最佳选择的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说我有一个叫Gamers的桌子.
游戏玩家包含列GamerID
,GameID
,Score
.
Lets say I have a table called Gamers.
Gamers contains the columns GamerID
, GameID
, Score
.
我有兴趣选择每场得分最高的球员.
I am interested in selecting the highest scoring player of each game.
例如,
|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的行.执行该操作的查询是什么?
After the query, I hope to get the rows for GamerID 4, 5 and 8. What is the query that would do this?
推荐答案
尝试一下:
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)
您可以执行的另一种选择是创建临时表或视图(如果您不喜欢子查询).
The other option you can do is to create a temporary table or a view (if you don't like sub-query).
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;
这篇关于mysql SELECT在单个表中每个类别的最佳选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文