MySQL在分组依据查询中选择错误的列值 [英] MySQL Selecting wrong column value in Group By query

问题描述

我在写一个游戏时有一个高分表。高分数据库记录了名称，等级和分数。数据库中有很多重复项。例如：

  Name |级别|分数|时间戳（键）
 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 
  

我想返回一个达到最高水平的高分列表，输出类似于：

 名称|级别| Score 
 Bob 3 21 
 Joe 2 40 
  

目前使用的SQL查询是：

  SELECT *，MAX（级别）作为级别
 FROM highscores 
 GROUP BY名称
 ORDER BY等级DESC，得分DESC 
极限5 
  

然而这并不完全工作。 分数字段输出似乎总是随机从组中取出，而不是取得相应分数达到最高水平。例如：

  Name |级别|得分
鲍勃3 41 
乔2 40 
  

鲍勃从来没有得到41分3级！我该如何解决这个问题？

解决方案

您需要使用子查询来将分数拉出。

 选择不同的
名称，
 max（级别）作为级别，
（选择最大值（得分）从高分h2 
其中h2.name = h1.name和h2.level = h1.level）得分高
 h1 
按名称分组
按等级排序desc，score desc 
  

干杯，

埃里克

令我感到厌烦的是，当我发布答案时，我没有花时间解释为什么会发生这种情况，所以这里是：

当你拉回所有的东西（*），然后是最高等级时，你会得到的是每个记录的顺序，再加上一个最高等级的列。请注意，你不是按分数分组的（这会给你Bob 2 41和Bob 3 21-我们的朋友Bob的两条记录）。

所以，我们怎么解决这个问题？你需要做一个子查询来过滤结果，这就是（select max（score）...）。现在，对于读取鲍勃的每一行，您将获得他的最高等级（3），以及他在该等级（21）的最高分数。但是，这仍然给了我们无数的鲍勃行（例如，如果他有5行，你会得到5行鲍勃3 21）。为了限制这只是最高分，我们需要在select语句中使用DISTINCT子句来仅返回唯一行。

更新：正确的SQL（无法评论在le dorfier的帖子中）：

  SELECT h1.Name，h1.Level，MAX（h1.Score）
 FROM highscores h1 
 LEFT OUTER JOIN高分h2 ON h1.name = h2.name AND h1.level< h2.level 
 LEFT OUTER JOIN高分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 
  

Here's a real noobish MySQL query problem I'm having.

I have a high score table in a game I'm writing. The high score DB records a name, level, and score achieved. There are many near duplicates in the db. For example:

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

I want to return a "highest level achieved" high score list, with an output similar to:

Name | Level | Score
Bob    3       21
Joe    2       40

The SQL Query I currently use is:

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

However this doesn't quite work. The "Score" field output always seems to be randomly pulled from the group, instead of taking the corresponding score for the highest level achieved. Eg:

Name | Level | Score
Bob    3       41
Joe    2       40

Bob never got 41 points on level 3! How can I fix this?

解决方案

You'll need to use a subquery to pull the score out.

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

Cheers,

Eric

It irks me that I didn't take the time to explain why this is the case when I posted the answer, so here goes:

When you pull back everything (*), and then the max level, what you'll get is each record sequentially, plus a column with the max level on it. Note that you're not grouping by score (which would have given you Bob 2 41, and Bob 3 21--two records for our friend Bob).

So, how the heck do we fix this? You need to do a subquery to additionally filter your results, which is what that (select max(score)...) is. Now, for each row that reads Bob, you will get his max level (3), and his max score at that level (21). But, this still gives us however many rows Bob has (e.g.-if he has 5 rows, you'll get 5 rows of Bob 3 21). To limit this to only the top score, we need to use a DISTINCT clause in the select statement to only return unique rows.

UPDATE: Correct SQL (can't comment on le dorfier's post):

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

这篇关于MySQL在分组依据查询中选择错误的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！