SQL语句之分析函数 row_number() /rank()/dense_rank()

有些时候我们希望得到指定数据中的前n列，示例如下：

得到每个部门薪水最高的三个雇员：

先创建示例表

create table emp as select * from scott.emp; alter table emp add constraint emp_pk primary key(empno); create table dept as select * from scott.dept; alter table dept add constraint dept_pk primary key(deptno);

 

先看一下row_number() /rank()/dense_rank()三个函数之间的区别

select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2

结果如下:

 

10    5000.00    7839    1    1    1
10    2450.00    7782    2    2    2
10    1300.00    7934    3    3    3
20    3000.00    7788    1    1    1
20    3000.00    7902    2    1    1
20    2975.00    7566    3    3    2
20    1100.00    7876    4    4    3
20    800.00    7369    5    5    4
30    2850.00    7698    1    1    1
30    1600.00    7499    2    2    2

取每个部门的薪水前三位雇员：

select t.deptno,t.rank,t.sal from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3

结果如下：

 

10    1    5000.00
10    2    2450.00
10    3    1300.00
20    1    3000.00
20    1    3000.00
20    3    2975.00
30    1    2850.00
30    2    1600.00
30    3    1500.00

如果想输出成deptno  sal1   sal2   sal3这种类型的格式
步骤一（decode）：

 

select t.deptno,decode(row_number,1,sal) sal1,decode(row_number,2,sal) sal2,decode(row_number,3,sal) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3

 

结果如下：

 

10    5000       
10                  2450   
10                             1300
20    3000       
20                  3000   
20                              2975
30    2850       
30                 1600   
30                             1500

步骤二（使用聚合函数去除null,得到最终结果）：

 

select t.deptno,max(decode(row_number,1,sal)) sal1,max(decode(row_number,2,sal)) sal2,max(decode(row_number,3,sal)) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3 group by t.deptno

结果如下：

10    5000    2450    1300
20    3000    3000    2975
30    2850    1600    1500