Oracle排名rank() dense_rank() row_number() 分区partition by

首先感谢生菜肥羊同学提供的问题和良好的解决方案，再次感谢！

 

一个查询需求，要查询出每个部门中工资排在前2名的员工信息

提供表结构和记录如下，方便大家进行学习

员工表（employee）

 

ID	DEPTID	NAME	SALARY
1	1	刘德华	2000
2	1	张惠妹	2500
3	1	谷超	2500
4	2	王杰	2000
5	2	武力海	4000
6	3	张三	10000
7	3	李四	20000
8	3	王五	30000

 

填入记录后，思考这个需求，每个部门我们很容易想到分组group by，前两名，很容易想到是排序order by，容易想到用group by... order by ...来进行查询，但是这个需求是要查询出工资最大的前两名，而不是工资最大的或是工资最小的员工信息，因为group by deptid进行分组后，查询出来的结果只能是group by后面的字段，或是聚合函数（sum、max、min、count、avg等），查询的粒度比较粗，不够精细。就因为是前2名的信息，导致了不能很好的使用group by ... order by ...进行查询，即使能够查询出结果，效率方面也不会非常令人满意！故放弃这种解决方案

 

这里介绍Oracle中的内置函数rank() over(partition by ...order by ...)来解决，rank() over排名函数，partition by分区函数，order by排序函数，特别注意rank() over()中一定要有order by子句，就是一定要在排序的基础上进行排名

 

整个的意思是在分区、排序后的基础上进行排名，这样就能很好解决每个部门工资在前2名的需求了，语句如下

 

select *
  from (select e1.deptid,
               e1.name,
               e1.salary,
               rank() over(partition by e1.deptid order by e1.salary desc) rn
          from employee e1)
 where rn <= 2

 

要提出的一点是，如果出现重复的情况如何处理，大家从查询结果看到了张惠妹和谷超都是排在了第一名，而没有第二名，dense_rank()同样是排名函数，与rank的区别是它能够查询并列后的下一名，换成dense_rank()的查询结果是张惠妹和谷超都是排在了第一名，刘德华排在了第二名。从字面上也很容易理解，dense_rank意思是密集排名

 

另外在介绍函数row_number()分析函数，也可以用row_number()来代替rank()进行查询，row_number()并不会出现并列的情况，即使上面的工资相同，它也会排出一个第一第二来

 

rank、dense_rank、row_number在SQL Server中同样可以使用