MySQL-在最高得分列表中排名用户 [英] MySQL - Rank user amongst list of top high-scores

问题描述

我有一个MySQL查询，该查询选择我的users表中得分最高的3名球员，然后创建一个额外的列，为他们分配排名:

I have a MySQL query that selects the players with the top 3 highest scores in my users table, then creates an extra column where their rank is assigned:

SELECT s.*, @curRank := @curRank + 1 AS rank
FROM users AS s
JOIN
( SELECT DISTINCT highscore
  FROM users
  ORDER BY highscore DESC
      LIMIT 3
) AS lim
ON s.highscore = lim.highscore 
, (SELECT @curRank := 0) r
ORDER BY s.highscore DESC ;

当前进度

因此，如果表如下所示:

Current progress

So, if the table looked like this:

userid   name     highscore
0        sam      20
1        james    39
2        jack     10
3        harry    46
4        jennie   7

查询结果将是这样:

userid  name    highscore   rank
3       harry   46          1
1       james   39          2
0       sam     20          3

问题

如何更改此查询，使其在结果列表中也显示用户及其排名?例如.如果是$userName = "jenny"，我怎么能返回:

Question

How could I alter this query so it also display's the user and their rank in the list of results? E.g. if $userName = "jenny", how could I return:

userid  name    highscore   rank
3       harry   46          1
1       james   39          2
0       sam     20          3
4       jenny   7           5   <-- Add this row and skip jack

我应该提到-我正在使用MySQL 5.0.96版

推荐答案

适用于8.0之前的版本...

For versions prior to 8.0...

SELECT *
  FROM 
     ( SELECT a.*
            , @i := @i+1 i
         FROM my_table a
         JOIN (SELECT @i:=0) vars
        ORDER 
           BY highscore DESC
            , userid 
     ) x
 WHERE name = 'jennie'
    OR i <= 3
     ORDER 
        BY i;

https://www.db-fiddle.com/f/hYsiCE1bXeTnR2HzoJkKiR/0

这篇关于MySQL-在最高得分列表中排名用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！