Oracle中几种排序函数及验证(row_number、rank、dense_rank)

1.数据表准备

drop table ORACLE_ORDER_PRACTISE;
create table ORACLE_ORDER_PRACTISE(
id number,
course varchar2(50),
score number);

insert all
into ORACLE_ORDER_PRACTISE values(1,'001',100)
into ORACLE_ORDER_PRACTISE values(2,'001',99)
into ORACLE_ORDER_PRACTISE values(3,'001',99)
into ORACLE_ORDER_PRACTISE values(4,'001',98)
into ORACLE_ORDER_PRACTISE values(5,'001',97)
select * from dual;

2.数据验证

select id,course,score
,row_number() over(partition by course order by score desc) row_number
,rank() over(partition by course order by score desc) rank
,dense_rank() over(partition by course order by score desc) dense_rank
from ORACLE_ORDER_PRACTISE;

函数效果展示:

3.各函数归纳总结

row_number():相同值排名会递增(不会出现重复排名的情况,若值相同则继续向下递增);
rank():相同值的排名相同,但排名相同的会占用序号,后续排号会跳号;
dense_rank():相同值的排名相同,但排名相同的不会占用序号,后续排号继续.