[Oracle]高效的SQL語句之分析函式（三）

fengzj發表於2008-12-17
OracleSQL函式

[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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-513636/，如需轉載，請註明出處，否則將追究法律責任。

相關文章