一.整排
要求:根据score进行排名,分数相同,名次相同,且连续
表如下图:
sql语句:
方法一:
select
a.score,
(select count(distinct b.score) from test01 b where b.score >=a.score) as rank1
from
test01 a order by score desc;
结果如下图:
方法二:
select score,dense_rank() over(order by score desc) rank2 from test01
结果如下图:
注意:重新命名时不要使用rank,会报错
二.分组后排序
要求:每一门课的最高两名
表:test01
方法一:
select *
from
(
select
subject,score,
@ROW:=case when @su=subject then @ROW+1 else 1 end rn,
@su:=subject
from
(select subject,score from test01 order by subject,score desc) t1,
(select @ROW:=0, @su:='') t2
)t3
where rn<=2
注意:@ROW:=是用来赋值的
结果如下图:
方法二:
select *
from
(select subject,score,row_number() over(partition by subject order by score desc) rn from test01) t
where t.rn <=2
结果如下图: