[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()

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

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

先创建示例表

createtableemp
as
select*fromscott.emp;

altertableemp
addconstraintemp_pk
primarykey(empno);

createtabledept
as
select*fromscott.dept;

altertabledept
addconstraintdept_pk
primarykey(deptno);

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

selectemp.deptno,emp.sal,emp.empno,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2

结果如下:

105000.007839111
102450.007782222
101300.007934333
203000.007788111
203000.007902211
202975.007566332
201100.007876443
20800.007369554
302850.007698111
301600.007499222

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

selectt.deptno,t.rank,t.salfrom
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3

结果如下：

1015000.00
1022450.00
1031300.00
2013000.00
2013000.00
2032975.00
3012850.00
3021600.00
3031500.00

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

selectt.deptno,decode(row_number,1,sal)sal1,decode(row_number,2,sal)sal2,decode(row_number,3,sal)sal3from
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3

结果如下：

105000
10 2450
10 1300
203000
20 3000
20 2975
302850
30 1600
30 1500

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

selectt.deptno,max(decode(row_number,1,sal))sal1,max(decode(row_number,2,sal))sal2,max(decode(row_number,3,sal))sal3from
(
selectemp.*,row_number()over(partitionbydeptnoorderbysaldesc)row_number,--1,2,3
rank()over(partitionbydeptnoorderbysaldesc)rank,--1,1,3
dense_rank()over(partitionbydeptnoorderbysaldesc)dense_rankfromemp--1,1,2
)t
wheret.rank<=3
groupbyt.deptno

结果如下：

10500024501300
20300030002975
30285016001500