获取分数表中用户的排名

我有一个非常简单的MySQL表，用于保存高分。看起来像：

Id     Name     Score

到目前为止一切顺利。问题是：如何获得用户等级？例如，我有一个用户Name或Id，想获得他的等级，其中所有行均按Score的降序顺序排列。

示例

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

在这种情况下，Assem的排名为3，因为他获得了第三高的得分。

查询应返回一行，其中仅包含所需的Rank。

#1 楼

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

给出此列表：

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

获得单人得分：

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

< br给出以下结果：

id name score rank
5 Assem 99 3

您将进行一次扫描以获取得分列表，然后进行另一次扫描或尝试对其进行一些有用的操作。 score列上的索引将有助于大型表的性能。

关联（从TheWholeTable中选择SELECT GROUP_CONCAT（score））不是最佳方法。并且它可能对创建的行的大小有问题。

–超立方体ᵀᴹ
2012年2月23日在8:04

如果是平局，这将失败。

– Arvind07
13年5月3日，11：14

对于较大的表，单人得分查询的速度非常慢。确定单人得分的排名（带关系的差距）的更好的查询是：SELECT 1 + COUNT（*）AS排名FROM得分WHERE得分>（SELECT得分FROM得分WHERE name ='Assem'）。哪个“只是”计算得分高于当前条目的条目数。（如果添加DISTINCT，您将获得无间隙的排名。）

– Paul
19年1月10日在13:43

重要说明：GROUP_CONTAT的默认限制为1024个字符，在大型数据集上，它将导致错误的排名，例如，它可能会停止在排名100处，然后报告0作为排名

– 0plus1
19年6月14日在5:01

#2 楼

当多个条目具有相同分数时，下一个等级不应连续。下一个等级应增加具有相同等级的分数的数量。

要显示类似的分数，需要两个等级变量

等级变量才能显示
用于计算的排名变量

以下是具有联系的排名的更稳定版本：

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

让我们尝试使用示例数据进行尝试。首先，这里是示例数据：

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

让我们加载示例数据

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.04 sec)
Records: 20  Duplicates: 0  Warnings: 0

接下来，让我们初始化用户变量：

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

现在，这是查询的输出：

mysql> SELECT score,ID,rank FROM
    -> (
    ->     SELECT AA.*,BB.ID,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    ->     (@curscore:=score) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (SELECT COUNT(1) scorecount,score
    ->         FROM scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.18 sec)

请注意多个具有相同分数的ID具有相同的排名。另请注意，等级不是连续的。

尝试一下！

由于这是使用会话范围的变量，如果有多个最终用户同时请求记分板，这是否安全？由于另一个用户也在执行此查询，结果集是否可能具有不同的结果？想象一下，此查询前面有一个API，有许多客户端一次将其击中。

– Xaero Degreaz
19年5月21日在20:21

@XaeroDegreaz是的，这是可能的。想象一下计算游戏排名。在某人击败高分或输入最高X分数后，一个用户查询排名，另一用户查询5秒。尽管如此，如果排名是在应用程序级别而不是服务器级别进行的，则可能会发生同样的情况。

– RolandoMySQLDBA
19年5月21日在20:24

谢谢回复。我真正关心的不是数据是否随时间自然地移动，我担心的是，执行查询的多个用户将修改/覆盖存储在会话范围变量中的数据，而其他用户也在执行查询。那有意义吗？

– Xaero Degreaz
19年5月21日在20:32

@XaeroDegreaz就是会话作用域变量的美。它们仅在您的会话中，其他人不在。您将看不到其他用户的会话变量，也没人会看到您的会话变量。

– RolandoMySQLDBA
19年5月21日在20:45

好的，这就是我倾向于相信的东西-会话变量的作用范围是连接，并且一个连接不能一次被多个人占用。一旦释放了连接，或者将其扔回到了池中，另一个用户就可以跳上连接，并重新初始化会话变量（执行此查询时）。再次感谢您提供信息。

– Xaero Degreaz
19年5月21日在20:47

#3 楼

SELECT 
    id, 
    Name,
    1+(SELECT count(*) from table_name a WHERE a.Score > b.Score) as RNK,
    Score
FROM table_name b;

#4 楼

一种选择是使用USER变量：

SET @i=0;
SELECT id, name, score, @i:=@i+1 AS rank 
 FROM ranking 
 ORDER BY score DESC;

#5 楼

接受的答案有潜在的问题。如果存在两个或两个以上相同的分数，则排名中将存在差距。在此修改示例中：

 id name  score rank
 1  Ida   100   2
 2  Boo    58   5
 3  Lala   99   3
 4  Bash  102   1
 5  Assem  99   3

58分的得分为5，而没有得分为4。

如果要确保排名没有差距，请在DISTINCT中使用GROUP_CONCAT建立不同得分的列表：

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( DISTINCT score
ORDER BY score DESC ) FROM scores)
) AS rank
FROM scores

结果：

id name  score rank
1  Ida   100   2
2  Boo    58   4
3  Lala   99   3   
4  Bash  102   1
5  Assem  99   3

这也适用于获得单个用户的排名：

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT(DISTINCT score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Boo'

结果：

id name score rank
 2  Boo   58    4

通过使用COUNT和子查询，可以极大地优化单个用户的排名查询。在已接受的答案中查看我的评论

– Paul
19年1月10日在13:47

好的注意事项和增强功能。效果很好

– S.M. Mousavi
19年7月15日在14:09

#6 楼

最佳答案是：

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score ) AS rank FROM
highscores b WHERE Name = 'Assem' ORDER BY rank LIMIT 1 ;

此查询将返回：

3

我的想法有一个小问题。例如：如果前两个用户的得分不同，其余所有用户的得分均为0，则零得分人员的排名为＃4，而不是＃3。但是第一个是正确获取＃1，第二个是正确获取。有任何想法吗？

–fersarr
2014年4月30日在5:15

#7 楼

如果有平局，此解决方案给出DENSE_RANK：

SELECT *,
IF (@score=s.Score, @rank:=@rank, @rank:=@rank+1) rank,
@score:=s.Score score
FROM scores s,
(SELECT @score:=0, @rank:=0) r
ORDER BY points DESC

#8 楼

以下工作（假设您的表称为“分数”）是否可以？

SELECT COUNT(id) AS rank FROM Scores 
WHERE score <= (SELECT score FROM Scores WHERE Name = "Assem")

#9 楼

我有这个，它给出的结果与带有变量的结果相同。它可以配合领带使用，并且可能更快：

SELECT COUNT(*)+1 as rank
FROM 
(SELECT score FROM scores ORDER BY score) AS sc
WHERE score <
(SELECT score FROM scores WHERE Name="Assem")

我没有测试过，但是我使用的是完美的，我对此进行了调整您在此处使用的变量。

编程黑洞网