找到每组中最高的两个值[重复]

This question already has an answer here:

这个问题在这里已有答案:

Selecting top n elements of a group in Oracle 5 answers

在Oracle 5中选择组中的前n个元素

I want to find top 2 earners in each department

我想找到每个部门的前2名收入者

table => staff_masters having "staff_name" column for staffs and "dept_code" for corresponding department.

table => staff_masters为员工提供“staff_name”列,为相应部门提供“dept_code”。

I tried to like this

我试着喜欢这个

SELECT TOP 2 staff_sal 
FROM staff_masters
GROUP BY dept_code;

Please use group by clause.

请使用group by子句。

2 个解决方案

#1

You would use row_number():

你会使用row_number():

select sm.*
from (select dept_code, staff_sal,
             row_number() over (partition by dept_code order by staff_sal desc) as seqnum
      from staff_masters
     ) sm
where seqnum <= 2;

If you want distinct salaries, you would use dense_rank() rather than row_number().

如果你想要不同的工资,你可以使用dense_rank()而不是row_number()。

#2

You can do this with a Row_Number() windowed function:

您可以使用Row_Number()窗口函数执行此操作:

Select  Dept_Code, Staff_Sal
From
(
    Select  Dept_Code, Staff_Sal, 
            Row_Number() Over (Partition By Dept_Code Order By Staff_Sal Desc) As RN
    From    Staff_Masters
) A
Where   RN <= 2
Order By Dept_Code, RN

#1