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

本系列文章导航

[Oracle]高效的SQL语句之分析函数(一)--sum()

[Oracle]高效的SQL语句之分析函数(二)--max()

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

[Oracle]高效的SQL语句之分析函数(四)--lag()/lead()

有些时候我们希望得到指定数据中的前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

博文来源：http://blog.youkuaiyun.com/huanghui22/archive/2007/05/03/1595166.aspx