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是什么，会做这样的查询后？

是GamerID在该表中，意义独特每个玩家只玩o ne游戏？ – 2012-02-17 07:00:50

@Naveen是的。我打算将这个例子作为我实际表格的一个模拟，所以我可以省略一大堆细节。 – Morpork 2012-02-17 07:04:44

那么当游戏玩家玩多个游戏时，该表格不会处理这种情况。 – 2012-02-17 07:32:12

答

试试这个：

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;

感谢这工作。它花了0.12秒。我想知道这是否是由以前的查询造成的？ – Morpork 2012-02-17 03:44:57

@Morpork查询/结果集很可能被缓存，这可能是为什么。临时或视图应加载更快的数据 – 2012-02-17 03:53:24

我怀疑这一点，并reran所有4个答案。所花的时间对每个人都是一样的。不知道该怎么做。我预计第二轮运行会因为缓存而变得更快。（显示我所知道的关于mysql哈哈） – Morpork 2012-02-17 03:55:36

答

你可以这样使用一个子选择，像下面完成：

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

答

看这里http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

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

相关推荐