mysql最大数据行,MySQL-如何选择具有字段最大值的行

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

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

The result should be:

id | user_id | level | score

4 | Mark | 1 | 60

3 | John | 2 | 30

6 | David | 3 | 80

9 | David | 4 | 50

Thank you

解决方案

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.