mysql在单个表格中选择每个类别的最佳
假设我有一个名为Gamers的表格。 游戏玩家包含列GamerID
,GameID
,Score
。mysql在单个表格中选择每个类别的最佳
我有兴趣选择每场比赛得分最高的球员。
例如,
|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是什么,会做这样的查询后?
试试这个:
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;
你可以这样使用一个子选择,像下面完成:
SELECT GameID, GamerID, Score
FROM Gamers
WHERE Score = (
SELECT
MAX(scores.Score)
FROM Gamers AS scores
WHERE scores.GameID = Gamers.GameID
)
GROUP BY GameID
测试上:
CREATE TABLE Gamers (
GamerID int(11) NOT NULL,
GameID int(11) NOT NULL,
Score int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO Gamers (GamerID, GameID, Score) VALUES
(1, 1, 10),
(2, 1, 10),
(3, 1, 10),
(4, 1, 90),
(5, 2, 40),
(6, 2, 10),
(7, 3, 10),
(8, 3, 10);
当然这不会是在两个玩家达到特定的游戏分数相同的情况下的理想选择。
虽然会返回所有行。 – CincauHangus 2012-02-17 02:54:15
@CincauHangus你是对的 - 我删除了我的答案,然后在经过更多考虑后修改了它。 – 2012-02-17 03:10:24
谢谢,这工作。不过,我的〜1300排桌上花了18.3秒。 (该表还有一堆额外的东西) – Morpork 2012-02-17 03:41:37
试试这个:
SELECT a.GameID, a.GamerID, a.Score
FROM Gamers a INNER JOIN
(
SELECT s.GameID, MAX(s.Score) AS Maxx
FROM Gamers s
GROUP BY s.GameID
) b ON (a.GameID = b.GameID) AND (a.Score = b.Maxx)
ORDER BY GameID;
谢谢,这也适用。 – Morpork 2012-02-17 03:38:14
试试这个:
select g1.* from gamers g1
left join gamers g2
on g1.gameId = g2.gameId and g1.score < g2.score
where g2.score is null
结果给提供的数据显示:
+---------+--------+-------+
| GAMERID | GAMEID | SCORE |
+---------+--------+-------+
| 4 | 1 | 90 |
| 5 | 2 | 40 |
| 8 | 3 | 30 |
+---------+--------+-------+
但是如果你有类似'(gamerid,gameid,score)=(9,3,30)'这样的配合会发生什么?这种情况的政策当然没有说明,但它仍然可以发生。 – 2012-02-17 03:05:44
两个结果都会返回,因为两个结果都是最大值。 – 2012-02-17 03:09:46
谢谢,这个工程。当我在我的千行表上运行这个时,它耗时9秒,但是当我运行@johntotetwoo的解决方案时,耗时0.89秒。这是因为查询还是因为我先运行了解决方案? – Morpork 2012-02-17 03:37:35
是GamerID在该表中,意义独特每个玩家只玩o ne游戏? – 2012-02-17 07:00:50
@Naveen是的。我打算将这个例子作为我实际表格的一个模拟,所以我可以省略一大堆细节。 – Morpork 2012-02-17 07:04:44
那么当游戏玩家玩多个游戏时,该表格不会处理这种情况。 – 2012-02-17 07:32:12