`
谷超
  • 浏览: 165963 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle排名rank() dense_rank() row_number() 分区partition by

    博客分类:
  • DB
阅读更多

首先感谢生菜肥羊同学提供的问题和良好的解决方案,再次感谢!

 

一个查询需求,要查询出每个部门中工资排在前2名的员工信息

提供表结构和记录如下,方便大家进行学习

员工表(employee)

 

ID DEPTID NAME SALARY
1 1 刘德华 2000
2 1 张惠妹 2500
3 1 谷超 2500
4 2 王杰 2000
5 2 武力海 4000
6 3 张三 10000
7 3 李四 20000
8 3 王五 30000

 

填入记录后,思考这个需求,每个部门我们很容易想到分组group by,前两名,很容易想到是排序order by,容易想到用group by... order by ...来进行查询,但是这个需求是要查询出工资最大的前两名,而不是工资最大的或是工资最小的员工信息,因为group by deptid进行分组后,查询出来的结果只能是group by后面的字段,或是聚合函数(sum、max、min、count、avg等),查询的粒度比较粗,不够精细。就因为是前2名的信息,导致了不能很好的使用group by ... order by ...进行查询,即使能够查询出结果,效率方面也不会非常令人满意!故放弃这种解决方案

 

这里介绍Oracle中的内置函数rank() over(partition by ...order by ...)来解决,rank() over排名函数,partition by分区函数,order by排序函数,特别注意rank() over()中一定要有order by子句,就是一定要在排序的基础上进行排名

 

整个的意思是在分区、排序后的基础上进行排名,这样就能很好解决每个部门工资在前2名的需求了,语句如下

 

select *
  from (select e1.deptid,
               e1.name,
               e1.salary,
               rank() over(partition by e1.deptid order by e1.salary desc) rn
          from employee e1)
 where rn <= 2

 

要提出的一点是,如果出现重复的情况如何处理,大家从查询结果看到了张惠妹和谷超都是排在了第一名,而没有第二名,dense_rank()同样是排名函数,与rank的区别是它能够查询并列后的下一名,换成dense_rank()的查询结果是张惠妹和谷超都是排在了第一名,刘德华排在了第二名。从字面上也很容易理解,dense_rank意思是密集排名

 

另外在介绍函数row_number()分析函数,也可以用row_number()来代替rank()进行查询,row_number()并不会出现并列的情况,即使上面的工资相同,它也会排出一个第一第二来

 

rank、dense_rank、row_number在SQL Server中同样可以使用

2
1
分享到:
评论
1 楼 hanmiao 2012-11-26  
楼主执行那条 SQL 语句之后的结果是什么样的,能贴出来看看么?我比较困惑的是它执行完了之后到底返回几条记录,两条还是三条?还有如果把rank()换成了dense_rank()的话,返回结果又是怎么样的呢,有什么变化嘛?求解答。另外:希望楼主把查询语句和结果用代码高亮格式化壹下,看起来会舒服些。

相关推荐

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

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

    oracle_排列rank()函数

    `RANK()`函数是Oracle数据库中一个强大的工具,能够有效地在一组数据中进行排名操作,尤其是在需要对数据进行分区和排序的情况下。通过理解`RANK()`函数的特性和与其他窗口函数的差异,数据分析师和数据库管理员可以...

    深入探讨:oracle中row_number() over()分析函数用法

    Oracle数据库中的`row_number() over()`分析函数是一个非常实用的工具,用于为数据集中的每一行分配一个唯一的整数,这个数字基于指定的排序条件。在处理大数据集时,它可以帮助我们进行分页、排名或者在复杂的查询...

    Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数(转载)

    开窗函数包括但不限于 `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`, `RANK`, `DENSE_RANK`, `ROW_NUMBER` 等。 - **ROW_NUMBER()**:为每一行分配一个唯一的数字,通常用于消除重复记录。 - **RANK()** 和 **DENSE_RANK()...

    Oracle中rank,over partition函数的使用方法

    它可以与 `RANK()`, `ROW_NUMBER()`, `DENSE_RANK()` 等函数结合使用,以在特定的行集上执行计算。 `PARTITION BY` 子句则是在每个分区内独立执行操作。它将数据集划分为多个逻辑部分,每个部分有自己的排名或计算...

    ORACLE_培训之_分析函数

    - **rank()**, **dense_rank()** 和 **row_number()**: 用于排序,分别返回唯一的排名、无间隙的排名和行号。 - **lag()** 和 **lead()**: 提供对当前行前一行或后一行的访问,常用于时间序列分析。 - **rollup()** ...

    Oracle之分析函数讲解及PPT资源.zip

    1. 排名函数:RANK()、DENSE_RANK()和ROW_NUMBER()用于为数据集中的每一行分配一个唯一的排名。RANK()在遇到相同值时会跳过排名,DENSE_RANK()则不会,ROW_NUMBER()则总是递增,即使有相同值。 2. 窗口函数:如LEAD...

    oracle分析函数over_及开窗函数.txt

    本文将详细介绍Oracle中的`OVER`子句以及几种常用的开窗函数,包括`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`和`LAG()`等。 #### 二、基本概念 - **`OVER`子句**:`OVER`子句用于指定分析函数的作用范围,它可以...

    oracle 的几个分析函数

    SELECT department_id, employee_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; ``` 这将为每个部门的员工按工资降序分配一个行号。 二、RANK() 与ROW_...

    ORACLE分析函数

    根据提供的文件信息,我们可以深入探讨Oracle分析函数的相关知识点,特别是`SUM()`函数配合`OVER`子句的不同用法,以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...

    oracle分析函数

    ROW_NUMBER() OVER (PARTITION BY &lt;partition_column_list&gt; ORDER BY &lt;order_column_list&gt;) ``` - **PARTITION BY** 子句:将数据集分割成多个分区,每个分区内的行独立进行编号。 - **ORDER BY** 子句:确定每个...

    ORACLE OLAP函数语法的总结

    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) AS row_number FROM emp; ``` - **RANK()**:为...

    oracle分析函数的使用说明

    1. RANK(), DENSE_RANK(), ROW_NUMBER():这三个函数用于为每一行分配一个唯一的排名。RANK()在遇到相同值时跳过排名;DENSE_RANK()不跳过,连续的排名不会留下空缺;ROW_NUMBER()则总是为每行分配一个连续的整数。 ...

    oracle分析函数.doc

    - 排序函数:`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`,用于为数据行分配唯一的排名。 - 分组函数:如`NTILE()`,将数据行分成等大小或不等大小的组。 - 首尾函数:`FIRST_VALUE()`, `LAST_VALUE()`,返回指定...

    ORACLE_OVER函数

    OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql &lt;window_function&gt; OVER ( [window_spec] ) ``` 其中`&lt;window_function&gt;`代表具体的窗口...

    oracle常用分析函数与聚合函数的用法

    3. **Row_Number()**:这个函数为每个分组内的行分配一个唯一的数字,根据指定的排序规则(如果有partition by,会在每个分区内部进行排序)。 下面是一个示例,展示了这三个函数如何应用于数据: ```sql with lcy...

Global site tag (gtag.js) - Google Analytics