mysql - SQL 查询不返回不同的值

Question

我有一个由 500 行数据组成的游戏排行榜，我编写了一个脚本来返回该数据并且没有重复的分数。但是，我收到了重复的分数。这是我的脚本。

SELECT DISTINCT 
    username, score, 
    FIND_IN_SET(score, (SELECT DISTINCT GROUP_CONCAT(score ORDER BY score DESC) 
                        FROM TPS_STATS)) AS rank
FROM 
    TPS_STATS 
ORDER BY
    rank ASC
LIMIT 100;

我看到的重复结果的示例已作为图像发布。

Answer 1

如果您的 MySql 版本是 8.0，那么您可以使用 row_number():

SELECT 
  username, 
  score, 
  row_number() OVER (ORDER BY score desc, username) rn 
FROM TPS_STATS 
ORDER BY score desc, username 
LIMIT 100

请参阅demo .
如果较低:

select 
  username,
  score,
  (select count(*) from TPS_STATS where score > t.score) +
  (select count(*) from TPS_STATS where score = t.score and username < t.username) + 1
  rank
from TPS_STATS t
order by rank, username
limit 100

请参阅demo