[Oracle]高效的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