`

DENSE_RANK() 、RANK() 和 ROW_NUMBER() 运用

阅读更多
关键字: oracle:DENSE_RANK() 、RANK() 和 ROW_NUMBER()  --转载自javaeye sun

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

分享到:
评论

相关推荐

    SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较

    - `row_number`:为每一行生成一个唯一的序号,不受相同值的影响。 - `rank`:在考虑相同值的情况下分配序号,出现相同值时跳过序号。 - `dense_rank`:同样考虑相同值,但保证序号的连续性,不会跳过序号。 - `...

    ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

    ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 排名函数 MS SQL 2005 中的排名函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE,这四个函数可以有效地分析数据并提供排序值。下面将详细介绍每个函数的用法和示例。 一、...

    分析函数ROW_NUMBER、RANK、DENSE_RANK的用法

    通过以上介绍可以看出,ROW_NUMBER()、RANK() 和 DENSE_RANK() 是SQL Server中非常有用的窗口函数,可以帮助我们在查询结果中添加行号、排名或密集排名。这些函数在处理大型数据集时尤其有用,能够帮助我们更方便地...

    "rank()、dense_rank()和row_number()的区别"score.txt

    “rank()、dense_rank()和row_number()的区别”文章的date

    Hive中分组取topN_row_number-rank和dense_rank的使用.pdf

    在这篇文档中,我们将详细学习在Hive中如何进行分组取topN,以及如何使用row_number()、rank()和dense_rank()三种窗口函数进行数据排序和排名。 首先,Hive中的数据表创建和数据插入操作是数据查询和分析的前提。...

    Oracle开发之分析函数(Rank, Dense_rank, row_number)

    Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...

    SQL server中row_number(),rank(),dense_rank()排序

    `row_number()`、`rank()`和`dense_rank()`都是窗口函数,用于对查询结果集进行排序并分配唯一的序列号。它们在处理分组数据、排名问题或者实现数据分页时特别有用。让我们详细探讨这三个函数的用法和差异。 首先,...

    实例讲解sql server排名函数DENSE_RANK的用法

    传统的`ROW_NUMBER()`函数可以做到这一点,但当存在相同分数时,它会给每个相同分数的学生分配不同的排名。而`DENSE_RANK`则可以解决这个问题,确保相同分数的学生共享相同的排名。 让我们看看如何使用`DENSE_RANK`...

    SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    在SQL Server 2005中,`ROW_NUMBER()`、`RANK()`和`DENSE_RANK()`是三种常用的排名函数,它们在处理大数据集时非常有用,尤其在数据分析和报表生成方面。这些函数都可以帮助我们根据指定的条件对数据进行排序并赋予...

    sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍

    本文主要讲解SQL中的四大排名函数:ROW_NUMBER、RANK、DENSE_RANK以及NTILE,它们在数据分析和报表生成中起到关键作用。 1. ROW_NUMBER() ROW_NUMBER()函数用于为查询结果集中的每一行分配一个唯一的数字序列。这个...

    ROW_NUMBER(),RANK()和DENSE_RANK()之间的区别

    ROW_NUMBER()、RANK()和DENSE_RANK()是其中的三个关键函数,它们都用于对数据进行排序并分配唯一的序列号,但各有其特点和适用场景。以下是对这三个函数的详细解释: 1. ROW_NUMBER() ROW_NUMBER()函数为每一行提供...

    [sql server]SQL Server2005杂谈(3):四个排名函数的比较.doc

    SQL Server 2005 中提供了四个排名函数,分别是 row_number、rank、dense_rank 和 ntile。这四个函数的主要功能是为查询结果生成一个序号,用于实现查询指定范围的记录或实现分页功能。 一、row_number 函数 row_...

    oracle row_number用法

    `ROW_NUMBER()`可以与`RANK()`和`DENSE_RANK()`进行比较。这些函数都可以用来给数据排序并分配等级,但是它们在处理并列的情况时有所不同: - **Rank**:如果两个或多个行具有相同的排序值,则这些行都将获得相同...

    SQL 获取顺序号的四种方法 IDENTITYRANKDENSE RANKROW NUMBER

    SQL 获取顺序号的四种方法:IDENTITY、RANK、DENSE_RANK、ROW_NUMBER 在 SQL 中,获取顺序号是非常常见的操作。今天,我们将讨论四种获取顺序号的方法:IDENTITY、RANK、DENSE_RANK、ROW_NUMBER。每种方法都有其优...

Global site tag (gtag.js) - Google Analytics