已知:两种排名方式(分区和不分区):使用和不使用partition
两种计算方式(连续,不连续),对应函数:dense_rank,rank
语法:
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
dense_rank()同上!
例子:
·查询各学生科目为 Oracle排名(简单排名)select sc.s_id,sc.s_name,sub_name,sc.score,rank() over ( order by score desc) 名次from t_score scwhere sub_name='Oracle'
对比: rank()与dense_rank():非连续排名与连续排名(都是简单排名)select sc.s_id,sc.s_name,sub_name,sc.score,dense_rank() over ( order by score desc) 名次from t_score scwhere sub_name='Oracle'
查询各学生各科排名 (分区排名)select sc.s_id,sc.s_name,sub_name,sc.score,rank() over( partition by sub_name order by score desc) 名次from t_score sc
查询各科前2名(分区排名)
select
*
from
(
select
sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over
(partition
by
sub_name
order
by
score
desc
) 名次
from
t_score sc
) x
where
x.名次<=2
根据总分查询各同学名次select x.*,rank() over ( order by sum_score desc) 名次from (select s_id,s_name, sum(score) sum_score from t_scoregroup by s_id,s_name ) x