mysql - MySQL在分组依据查询中选择错误的列值

这是一个真正的Noobish MySQL查询问题。
我在写的一个游戏里有一张高分表。高分数据库记录一个名称、级别和达到的分数。数据库中有许多几乎重复的内容。例如：

Name | Level | Score | Timestamp (key)
Bob    2       41    | 1234567.890
Bob    3       15    | 1234568.890
Bob    3       20    | 1234569.890
Joe    2       40    | 1234561.890
Bob    3       21    | 1234562.890
Bob    3       21    | 1234563.890

我想返回一个“最高级别成就”的高分列表，其输出类似于：

Name | Level | Score
Bob    3       21
Joe    2       40

我当前使用的SQL查询是：

SELECT *, MAX(level) as level
FROM highscores
GROUP BY name
ORDER BY level DESC, score DESC
LIMIT 5

但是这不太管用。“分数”字段输出似乎总是从组中随机抽取，而不是从所达到的最高级别中获取相应的分数。如：

Name | Level | Score
Bob    3       41
Joe    2       40

鲍勃在三级从未得到41分！我该怎么解决？

最佳答案

您需要使用子查询来提取分数。

select distinct
    name,
    max(level) as level,
    (select max(score) from highscores h2
        where h2.name = h1.name and h2.level = h1.level) as score
from highscores h1
group by name
order by level desc, score desc

干杯，
埃里克
当我发布答案时，我没有花时间解释为什么会出现这种情况，这让我很恼火，所以这里是：
当您向后拉所有的（*），然后是最大级别，您将得到的是每个记录顺序，加上一个列的最大级别。请注意，您没有按分数分组（这将为您提供bob 241和bob 321——我们的朋友bob的两个记录）。
那么，我们该怎么解决呢？你需要做一个子查询来额外过滤你的结果，这就是（选择max（score）…）的含义。现在，对于读到bob的每一行，您将得到他的最大级别（3），以及该级别的最大得分（21）。但是，不管bob有多少行（例如，-如果他有5行，您将得到bob 3 21的5行）。要将此限制为只返回最高分，我们需要在select语句中使用DISTINCT子句来只返回唯一行。
更新：正确的SQL（无法对Le Dorfier的帖子发表评论）：

SELECT h1.Name, h1.Level, MAX(h1.Score)
    FROM highscores h1
    LEFT OUTER JOIN highscores h2 ON h1.name = h2.name AND h1.level < h2.level
    LEFT OUTER JOIN highscores h3 ON h1.name = h3.name AND h2.level = h3.level AND h1.score < h3.score
    WHERE h2.Name IS NULL AND h3.Name IS NULL
    GROUP BY h1.Name, h1.Level