Oracle SQL-如何使用RANK()或DENSE_RANK()或ROW_NUMBER()分析函数获取不同的行? [英] Oracle SQL - How to get distinct rows using RANK() or DENSE_RANK() or ROW_NUMBER() analytic function?
问题描述
我希望获得每个部门的前3名薪水.我可以使用RANK()或DENSE_RANK()或ROW_NUMBER()来做到这一点，但是我的表中有一些薪水相同的记录.
下面提到的是我的查询及其结果.
部门20的前3名工资应为6000、3000、2975. 但是有2名员工的薪水为3000，而他们两个都具有等级2.因此，这给了我该部门4条记录(等级1为1，等级2为2，等级3为1.)
请就如何获得每个部门的前三名薪水提出建议/建议.
查询:
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_number和dense_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
I am looking to get the top 3 distinct salaries of each department. I was able to do it either using RANK() or DENSE_RANK() or ROW_NUMBER() but my table is having some records with same salaries.
Mentioned below is my query and its result.
The top 3 salaries of Dept 20 should be 6000, 3000, 2975. But there are 2 employees with salary 3000 and both of them have rank 2. So it is giving me 4 records for this department (1 for rank 1, 2 records for rank2 and 1 record for rank3).
Please suggest/advise about how can get the distinct top 3 salaries for each department.
Query:
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;
RESULT:
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
If you get more specific in row_number, with partitioning by dept,salary then you can combine row_number and dense_rank as in this query:
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
这篇关于Oracle SQL-如何使用RANK()或DENSE_RANK()或ROW_NUMBER()分析函数获取不同的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
