高级排序函数:
[ ROW_NUMBER()| RANK() | DENSE_RANK ] OVER (partition by xx order by xx)
1.row_number() 连续且递增的数字 1 2 3 4
row_number() over (partition by xx order by xx )
--学生表中按照所在专业分组,同专业内按成绩倒序排序,成绩相同则按学号正序排序,并给予组内等级
select row_number() over(partition by class_id order by score desc)rn,t.* from student2016 t
2.rank() 跳跃排序 若有相同数据则排名相同 然后跳跃排序 1 2 2 2 5
rank() over (partition by xx order by xx )
select rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
3.dense_rank 若有相同数据则排名相同 然后递增排序
dense_rank over (partition by xx order by xx ) 1 2 2 2 3
select dense_rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
开窗函数、高级排序函数:ROW_NUMBER(),RANK() , DENSE_RANK ,OVER (partition by xx order by xx)
最新推荐文章于 2024-07-29 09:03:14 发布