前三分-MySQL [英] Top 3 Scores - MySQL

问题描述

我正在建立前三名"排行榜.我想显示前三名的分数，绘制每人的最大分数，但我不想限制3，因为我想显示任何拥有前三名的分数的人.因此，例如，使用下面的数据，

I'm building a "Top 3 Scores" Leaderboard. I want to display the top three scores, drawing the max per person, but I don't want to limit 3, because I want to display anyone that has the top 3 scores. So for example, with the data below,

+----+-----+
|Name|Score|
+----+-----+
|Matt|   17|
|Mark|   29|
|Luke|   28|
|John|   29|
|Paul|   27|
|Matt|   29|
|Mark|   22|
+----+-----+

我要显示:

+------+-----+
|Name  |Score|
+------+-----+
|1.Matt|   30|
|2.Mark|   29|
|2.John|   29|
|3.Luke|   28|
+------+-----+

我的第一个想法是为每个人提取最大分数，然后在分数变化后停止显示(使用PHP).

My first thought is to extract the max for everyone, and then stop displaying after the score changes (using PHP).

select name, max(score)
from SCORES
group by name
order by name

有什么方法可以直接在SQL中执行此操作吗?

Is there any way to do this directly in SQL?

推荐答案

SELECT name, score
FROM SCORES
JOIN (SELECT distinct score score3
      FROM scores
      ORDER BY score DESC
      LIMIT 2, 1) x
ON score >= score3
ORDER by score DESC

字段

这篇关于前三分-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！