首先感谢生菜肥羊同学提供的问题和良好的解决方案,再次感谢!
一个查询需求,要查询出每个部门中工资排在前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中同样可以使用
分享到:
相关推荐
Oracle分析函数Rank、Dense_rank和row_number是用于处理数据集的高级工具,它们在数据库查询中发挥着关键作用,特别是在需要对数据进行排序和分组时。这三种函数都有各自的特点,适用于不同的业务场景。 1. **row_...
`RANK()`函数是Oracle数据库中一个强大的工具,能够有效地在一组数据中进行排名操作,尤其是在需要对数据进行分区和排序的情况下。通过理解`RANK()`函数的特性和与其他窗口函数的差异,数据分析师和数据库管理员可以...
Oracle数据库中的`row_number() over()`分析函数是一个非常实用的工具,用于为数据集中的每一行分配一个唯一的整数,这个数字基于指定的排序条件。在处理大数据集时,它可以帮助我们进行分页、排名或者在复杂的查询...
开窗函数包括但不限于 `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`, `RANK`, `DENSE_RANK`, `ROW_NUMBER` 等。 - **ROW_NUMBER()**:为每一行分配一个唯一的数字,通常用于消除重复记录。 - **RANK()** 和 **DENSE_RANK()...
它可以与 `RANK()`, `ROW_NUMBER()`, `DENSE_RANK()` 等函数结合使用,以在特定的行集上执行计算。 `PARTITION BY` 子句则是在每个分区内独立执行操作。它将数据集划分为多个逻辑部分,每个部分有自己的排名或计算...
- **rank()**, **dense_rank()** 和 **row_number()**: 用于排序,分别返回唯一的排名、无间隙的排名和行号。 - **lag()** 和 **lead()**: 提供对当前行前一行或后一行的访问,常用于时间序列分析。 - **rollup()** ...
1. 排名函数:RANK()、DENSE_RANK()和ROW_NUMBER()用于为数据集中的每一行分配一个唯一的排名。RANK()在遇到相同值时会跳过排名,DENSE_RANK()则不会,ROW_NUMBER()则总是递增,即使有相同值。 2. 窗口函数:如LEAD...
本文将详细介绍Oracle中的`OVER`子句以及几种常用的开窗函数,包括`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`和`LAG()`等。 #### 二、基本概念 - **`OVER`子句**:`OVER`子句用于指定分析函数的作用范围,它可以...
SELECT department_id, employee_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; ``` 这将为每个部门的员工按工资降序分配一个行号。 二、RANK() 与ROW_...
根据提供的文件信息,我们可以深入探讨Oracle分析函数的相关知识点,特别是`SUM()`函数配合`OVER`子句的不同用法,以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...
ROW_NUMBER() OVER (PARTITION BY <partition_column_list> ORDER BY <order_column_list>) ``` - **PARTITION BY** 子句:将数据集分割成多个分区,每个分区内的行独立进行编号。 - **ORDER BY** 子句:确定每个...
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()**:为...
1. RANK(), DENSE_RANK(), ROW_NUMBER():这三个函数用于为每一行分配一个唯一的排名。RANK()在遇到相同值时跳过排名;DENSE_RANK()不跳过,连续的排名不会留下空缺;ROW_NUMBER()则总是为每行分配一个连续的整数。 ...
- 排序函数:`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`,用于为数据行分配唯一的排名。 - 分组函数:如`NTILE()`,将数据行分成等大小或不等大小的组。 - 首尾函数:`FIRST_VALUE()`, `LAST_VALUE()`,返回指定...
OVER函数通常与窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG()等)一起使用。其基本语法形式如下: ```sql <window_function> OVER ( [window_spec] ) ``` 其中`<window_function>`代表具体的窗口...
3. **Row_Number()**:这个函数为每个分组内的行分配一个唯一的数字,根据指定的排序规则(如果有partition by,会在每个分区内部进行排序)。 下面是一个示例,展示了这三个函数如何应用于数据: ```sql with lcy...