--已知:两种排名方式(分区和不分区):使用和不使用partition
--两种计算方式(连续,不连续),对应函数:dense_rank,rank
select sc.s_id,sc.s_name,sub_name,sc.score,
rank() over (order by score desc) 名次
对比:rank()与dense_rank():非连续排名与连续排名(都是简单排名)
select sc.s_id,sc.s_name,sub_name,sc.score,
dense_rank() over (order by score desc) 名次
select sc.s_id,sc.s_name,sub_name,sc.score,
(partition by sub_name order by score desc) 名次
select sc.s_id,sc.s_name,sub_name,sc.score,
(partition by sub_name order by score desc) 名次
select s_id,s_name,sum(score) sum_score from t_score
rank() over (order by sum_score desc) 名次
select s_id,s_name,sum(score) sum_score from t_score
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意:聚合。