[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语句分清楚RANK(),DENSE_RANK(),ROW_NUMBER()三个排序的不同

在SCOTT用户下,执行下面SQL; SELECT  s.deptno,s.ename,s.sal, RANK() over(partition by s.deptno order by s.sa...
  • S630730701
  • S630730701
  • 2016年07月14日 00:21
  • 6132

SQL中ROW_NUMBER()/RANK() /DENSE_RANK() OVER函数的基本用法

转自:http://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html
  • biaobiao1217
  • biaobiao1217
  • 2014年11月13日 13:20
  • 2663

Oracle-分析函数之排序值rank()和dense_rank()

概述Oracle常见函数大全Oracle-分析函数之连续求和sum(…) over(…)Oracle-分析函数之排序值rank()和dense_rank()Oracle-分析函数之排序后顺序号row_...
  • yangshangwei
  • yangshangwei
  • 2016年11月04日 20:16
  • 5831

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

Oracle分析函数RANK()|ROW_NUMBER()|LAG()使用详解   ROW_NUMBER()函数: row_number()和rownum差不多,功能更强一点(可以在各个分组内从1...
  • haiross
  • haiross
  • 2013年11月01日 10:12
  • 2845

oracle中rank() over, dense_rank(), row_number() 的区别

假设现在有一张学生表student,学生表中有姓名、分数、课程编号,现在我需要按照课程对学生的成绩进行排序。 select * from student 1. rank over ()可以实现对...
  • zdp072
  • zdp072
  • 2015年04月16日 15:01
  • 1353

RANK、DENSE_RANK以及ROW_NUMBER区别

场景 数据库查询中,很多时候都会遇到对数据进行分组,然后组内排序加序号的需求。 应用 对于组内排序一般有三个函数可供使用,它们是RANK()、DENSE_RANK()以及ROW_NUMBER()。它们...
  • hustzw07
  • hustzw07
  • 2016年04月20日 16:54
  • 1229

Oracle rank和dense_rank排名函数

1.rank函数 rank计算一组值的排名,返回数字类型。排名可能是不连续。如果有5人,其中有2个人排名第一,则rank返回的排名结果为:1 1 3 4 5。 作为一个聚合函数,返回虚拟行在样...
  • chiclewu
  • chiclewu
  • 2013年12月10日 23:49
  • 6519

SQL语句之分析函数 row_number() /rank()/dense_rank()

有些时候我们希望得到指定数据中的前n列,示例如下:得到每个部门薪水最高的三个雇员:先创建示例表create table empasselect * from scott.emp;alter table...
  • yihui1983
  • yihui1983
  • 2009年10月12日 14:17
  • 198

hive的row_number()、rank()和dense_rank()的区别以及具体使用

row_number()、rank()和dense_rank()这三个是hive内置的分析函数,下面我们来看看他们的区别和具体的使用案例。 首先创建一个文件test: A,1 B,3 C,2 D,3 ...
  • qq_20641565
  • qq_20641565
  • 2016年10月17日 20:05
  • 2786

SQL 分析函数之KEEP (DENSE_RANK FIRST/LAST)

KEEP (DENSE_RANK FIRST/LAST)
  • hustzw07
  • hustzw07
  • 2016年04月19日 14:53
  • 1576
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章: [Oracle]高效的SQL语句之分析函数--row_number() /rank()/dense_rank()
举报原因:
原因补充:

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