gpt4 book ai didi

sql - Oracle SQL - 如何使用 RANK() 或 DENSE_RANK() 和 ROW_NUMBER() 分析函数获取不同的行?

转载 作者:行者123 更新时间:2023-12-05 01:04:21 24 4
gpt4 key购买 nike

我希望获得每个部门的前 3 名不同工资。我可以使用 RANK() 做到这一点或 DENSE_RANK()ROW_NUMBER()但是我的 table 上有一些工资相同的记录。

下面提到的是我的查询及其结果。

20部门工资前三名应该是6000、3000、2975。
但是有 2 名员工的薪水为 3000,他们都是 2 级。所以它给了我这个部门的 4 条记录(1 条记录为 1 级,2 条记录为 2 级记录,1 条记录为 3 级记录)。

请建议/建议如何获得每个部门的不同前三名工资。

询问:

SELECT * FROM (
SELECT EMPNO, DEPTNO, SAL,
DENSE_RANK() over (partition by deptno order by sal DESC) as RANK,
row_number() over (partition by deptno order by sal DESC) as ROWNO
from EMP)
WHERE RANK <= 3;

结果:
Empno Deptno    Salary Rank   Rowno
----------------------------------------
7839 10 5000 1 1
7782 10 2450 2 2
7934 10 1300 3 3
7935 20 6000 1 1
7788 20 3000 2 2
7902 20 3000 2 3
7566 20 2975 3 4
7698 30 2850 1 1
7499 30 1600 2 2
7844 30 1500 3 3

最佳答案

如果您在 row_number 中获得更具体的信息, 与 partitioning by dept,salary那么你可以结合row_numberdense_rank在这个查询中:

with data_row as 
(
select 7839 as empno, 10 as deptno, 5000 as salary from dual union all
select 7782 as empno, 10 as deptno, 2450 as salary from dual union all
select 7934 as empno, 10 as deptno, 1300 as salary from dual union all
select 1111 as empno, 10 as deptno, 1111 as salary from dual union all
select 7935 as empno, 20 as deptno, 6000 as salary from dual union all
select 7788 as empno, 20 as deptno, 3000 as salary from dual union all
select 7902 as empno, 20 as deptno, 3000 as salary from dual union all
select 7566 as empno, 20 as deptno, 2975 as salary from dual union all
select 2222 as empno, 20 as deptno, 2222 as salary from dual union all
select 7698 as empno, 30 as deptno, 2850 as salary from dual union all
select 7499 as empno, 30 as deptno, 1600 as salary from dual union all
select 7844 as empno, 30 as deptno, 1500 as salary from dual union all
select 3333 as empno, 30 as deptno, 1333 as salary from dual
)
select *
from
(
select
deptno,
salary,
dense_rank() over (partition by deptno order by salary desc) as drank,
row_number() over (partition by deptno, salary order by salary desc) as rowno

from data_row
)
where drank <=3 and
rowno =1

关于sql - Oracle SQL - 如何使用 RANK() 或 DENSE_RANK() 和 ROW_NUMBER() 分析函数获取不同的行?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23452997/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com