开窗函数、高级排序函数:ROW_NUMBER(),RANK() , DENSE_RANK ,OVER (partition by xx order by xx),
高级排序函数:
[ 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
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。