如何在mysql查询中获取行的排名 [英] How to get the rank of a row in mysql query

问题描述

这是我的数据库结构

表:球员

id  | name      |  score
1   | Bob       |  600
2   | Alex      |  1400
3   | John      |  800
4   | sara      |  2000

我需要选择约翰的行并计算约翰的排名OrderBy分数

I need to select john's row and count what is the john' rank OrderBy score

如您所见，john 排名第三 (800)，sara 排名第一 (2000)，Alex 排名第二 (1400)

as you see john is 3rd (800) , sara is 1st (2000), Alex is 2nd (1400) in score ranks

Select @rownum := @rownum + 1 AS rank form players where id=3 OrderBy score

有什么想法吗?

推荐答案

你可以通过子查询来计算得分超过某个 certian id 得分的玩家

You can do it by a subquery and count the players who has score more than the score of a certian id

Select count(*) as rank 
from players 
where score > (select score from players where id=3)

但是如果你想获得排名之外的其他信息，你可以通过

But if you want to have other information beside the rank you can do it by

SELECT ranks . *
FROM (    
   SELECT @rownum := @rownum +1 ‘rank’, p.id, p.score
   FROM players p, (SELECT @rownum :=0)r
   ORDER BY score DESC
) ranks
WHERE id =3

这篇关于如何在mysql查询中获取行的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！