sql查询排名

Question

请问下如图所示数据表(test)中 查询出小红的分数(fenshu)排名的 sql语句怎么写?谢谢大佬~

Answer 1

select count(id)+1 as '排名' from test where fenshu > (select fenshu from test where name = '小红')

Answer 2

想要计算排第几名就是计算比他分数高的有几个，再加上自身就好了。

select count(1)+1 as '排名'
  from test
  where fenshu>(select fenshu from test where name='小红')

Answer 3

1:

select @x:=ifnull(@x,0)+1 as rownum,name,fenshu
from test
order by fenshu;

2:

select a.name,a.fenshu,count(*) as rownum
from test a,test b
where a.fenshu>=b.fenshu
group by a.name,a.fenshu;

3:

select a.*,
(select count(*) from test where fenshu<=a.fenshu) as rownum
from test a;

Answer 4

--我这里把所有每个人的成绩排名都能显示出来、如果你只想要小红的话
--在where后面
--加上name='小红'的条件即可

SELECT * FROM 
(
   SELECT Id,fenshu,name,

   RANK()--调用RANK函数

   OVER( --被调用的方法
         PARTITION BY name ORDER BY fenshu DESC
       ) 
       AS Ranking  

   FROM [text] 
) V
WHERE V.Ranking<=4 --Ranking为排名数列、此处则为前4名的排名、可以更改数字选择你想要前几名的排序、也可去掉不要为全部排名

Answer 5

order by fenshu desc

Answer 6

select * from test where name='小红' order by fenshu desc;

并且建议在name字段上加一个索引，这样查询速度更快。