玖叶教程网

--说明，把aa当成一张数据库表

--1、row_number函数，值是否相等，排序都连续

select aa.code,aa.score,row_number() over(order by aa.score desc) as px from (

select '001' as code,99.1 as score from dual

union all

select '002' as code,97 as score from dual

union all

select '003' as code,97 as score from dual

union all

select '004' as code,95 as score from dual

union all

select '005' as code,98 as score from dual

)aa;

--2、rank函数，相等值排序相同，排序跳跃

select aa.code,aa.score,rank() over(order by aa.score desc) as px from (

select '001' as code,99.1 as score from dual

union all

select '002' as code,97 as score from dual

union all

select '003' as code,97 as score from dual

union all

select '004' as code,95 as score from dual

union all

select '005' as code,98 as score from dual

)aa;

--3、dense_rank函数，相等值排序相同，排序连续

select aa.code,aa.score,dense_rank() over(order by aa.score desc) as px from (

select '001' as code,99.1 as score from dual

union all

select '002' as code,97 as score from dual

union all

select '003' as code,97 as score from dual

union all

select '004' as code,95 as score from dual

union all

select '005' as code,98 as score from dual

)aa;

--4、row_number函数，分组排序

select aa.code,aa.grade,aa.score,row_number() over(partition by aa.grade order by aa.grade,aa.score desc) as px from (

select '001' as code,99.1 as score,'一年级' as grade from dual

union all

select '002' as code,97 as score,'二年级' as grade from dual

union all

select '003' as code,97 as score,'一年级' as grade from dual

union all

select '004' as code,95 as score,'二年级' as grade from dual

union all

select '005' as code,98 as score,'三年级' as grade from dual

)aa;