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

转载 2007年09月21日 15:19:00

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

数据库SQL中对查询结果排序排列序号编号,Oracle分析函数 rank,dense_rank,row_number使用和区别

Oracle从8i开始就提供了3个分析函数:rank,dense_rank,row_number (1)Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,    ...

oracle 分析函数Rank, Dense_rank, row_number

目录 =============================================== 1.使用rownum为记录排名 2.使用分析函数来为记录排名 3.使用分析函数为记录进行分...

Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)

目录 =============================================== 1.使用rownum为记录排名 2.使用分析函数来为记录排名 3.使用分析函数为记录进行分...

Oracle:分析函数2(Rank, Dense_rank, row_number)

目录 ===============================================1.使用rownum为记录排名2.使用分析函数来为记录排名3.使用分析函数为记录进行分组排名 一...

Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead

分析函数的基本概念和语法 -> http://blog.csdn.net/fw0124/article/details/7842039 1) rank(),dense_rank(),row_...
  • fw0124
  • fw0124
  • 2012年08月08日 16:44
  • 5224

oracle分析函数Rank, Dense_rank, row_number

原文地址:http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html 分析函数2(Rank, Dense_rank,...

oracle分析函数系列之rank,dense_rank,row_number:实现排名策略

Rank,Dense_rank,Row_number函数为每条记录产生一个从1开始至N的自然数,N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。 ①ROW_NU...

oracle分析函数系列之rank,dense_rank,row_number:实现排名策略

Rank,Dense_rank,Row_number函数为每条记录产生一个从1开始至N的自然数,N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。 ①ROW_NU...

Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解

Oracle分析函数RANK()|ROW_NUMBER()|LAG()使用详解   ROW_NUMBER()函数: row_number()和rownum差不多,功能更强一点(可以在各个分组内从1...
  • haiross
  • haiross
  • 2013年11月01日 10:12
  • 2607
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章: [Oracle]高效的SQL语句之分析函数--row_number() /rank()/dense_rank()
举报原因:
原因补充:

(最多只允许输入30个字)