oracle sql之分组取排名前N位的记录（rank(),dense_rank(),row_number()函数的应用）

      这里我们拥有一张英雄信息表，包含字段ID（编号），name(英雄名称)，attack_power(攻击力),item(类别):

    我们的目的是统计各类别当中攻击力排名前3的英雄；下面就比较下rank(),dense_rank(),row_number()函数三个函数的用法差异：

    1.rank()函数使用

    首先我们用rank()来对数据排序：

select rank()over(partition by t1.item order by t1.attack_power desc) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1

    结果为：

    结论：rank over ()可以实现影响用攻击力来排名，特点是攻击力相同的两名是并列

    其次我们取每类中攻击力排名前三的英雄信息：

  

select t.* from (select rank()over(partition by t1.item order by t1.attack_power desc) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1)t
where t.rn<=3

    结果为：

    另外，在使用rank()函数排序的时候，默认空值（null)是最大的，因此为了避免空值的存在干扰排序的正确性，可以添加nulls last，

            即将缺失值排在最后，这样就不会影响最后结果了：

select t.* from (select row_number()over(partition by t1.item order by t1.attack_power desc nulls last) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1)t
where t.rn<=3

 

     2.dense_rank()函数使用

     首先我们用dense_rank()来对数据排序：

 

select dense_rank()over(partition by t1.item order by t1.attack_power desc) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1

 

 

 

   结果为：

   结论：dense_rank()和rank over()功能很类似，但并列结果的后面并不会空出并列所占的名次；

   其次我们取每类中攻击力排名前三的英雄信息：

select t.* from (select dense_rank()over(partition by t1.item order by t1.attack_power desc) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1)t
where t.rn<=3

 

 

 

     结果为：

     3.row_number()函数使用    

    首先我们用row_number()来对数据排序：

select row_number()over(partition by t1.item order by t1.attack_power desc) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1

 

 

 

    结果为：             

   结论为：row_number()函数在排序时压根不会出现并列的现象，查出来的相同值都会按照连续顺序排序

   其次我们取每类中攻击力排名前三的英雄信息：

 

select t.* from (select row_number()over(partition by t1.item order by t1.attack_power desc nulls last) rn,
                     t1.id,t1.name,t1.attack_power,t1.item from hero_info t1)t
where t.rn<=3

 

结果为：