MySQL-如何选择具有字段最大值的行 [英] MySQL - How to select rows with max value of a field

问题描述

我有一张用户表，其中包含游戏每个级别的得分:

I have a table of users with their scores for each level of a game:

id | user_id | level | score
1  | David   | 1     | 20
2  | John    | 1     | 40
3  | John    | 2     | 30
4  | Mark    | 1     | 60
5  | David   | 2     | 10
6  | David   | 3     | 80
7  | Mark    | 2     | 20
8  | John    | 3     | 70
9  | David   | 4     | 50
10 | John    | 4     | 30

每个级别(得分最高的人)需要获得什么SQL查询?

What is the SQL query needed to get for each level, who has the highest score?

结果应为:

id | user_id | level | score
4  | Mark    | 1     | 60
3  | John    | 2     | 30
6  | David   | 3     | 80
9  | David   | 4     | 50

谢谢

推荐答案

如果想建立联系，则可以执行以下操作:

If you want to get ties, then you can do something like this:

select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level);

通过汇总以下内容，您可以在每个级别获得一行:

You could get one row per level by aggregating this:

select s.level, s.score, group_concat(s.user_id)
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level)
group by s.level, s.score;

这会将用户(如果有多个)组合到一个字段中.

This combines the users (if there is more than one) into a single field.

这篇关于MySQL-如何选择具有字段最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！