Oracle SQL - 如何使用 RANK() 或 DENSE_RANK() 或 ROW_NUMBER() 分析函数获取不同的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23452997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL - How to get distinct rows using RANK() or DENSE_RANK() or ROW_NUMBER() analytic function?
提问by Rakesh
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.
我希望获得每个部门的前 3 名不同工资。我能做到这一点无论是使用RANK()还是DENSE_RANK()和ROW_NUMBER()，但我的表是有一些记录有相同的工资。
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).
Dept 20 的前 3 个工资应该是 6000, 3000, 2975。但是有 2 个员工的工资是 3000，他们都是 2。所以它给了我这个部门的 4 条记录（1 条记录 1 条记录，2 条记录用于rank2 和 rank3 的 1 条记录）。
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
回答by zundarz
If you get more specific in row_number, with partitioning by dept,salarythen you can combine row_numberand dense_rankas in this query:
如果您在row_number, with 中获得更具体的信息，partitioning by dept,salary则可以在此查询中组合row_numberand 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
回答by Mureinik
The row_numberfunction you used should do the trick:
row_number您使用的函数应该可以解决问题：
SELECT *
FROM (SELECT empno, deptno, sal
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rk,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rowno
FROM emp)
WHERE rowno <= 3;
回答by user5178682
Please try:
请尝试：
SELECT empno, deptno, DISTINCT(sal)
FROM (SELECT empno, deptno, sal
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rk,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rowno
FROM emp)
WHERE rowno <= 3;
