根据表格数据查找每个部门的第3个最高工资

I need to find out the 3rd maximum salary for an employee for each department in a table. if no 3rd maximum salary exists then display 2nd maximum salary. if no 2nd maximum salary exist then find the highest salary. How to achieve this result in sql-server?

我需要找出表中每个部门的员工的第3个最高工资。如果没有第三个最高工资，则显示第二个最高工资。如果没有第二个最高工资，那么找到最高工资。如何在sql-server中实现这个结果？

The table structure is given below

表结构如下

create table employee1(empid int, empname varchar(10), deptid int, salary money)

insert into employee1
select 1,'a',1, 1000
union
select 1,'b',1, 1200 
union
select 1,'c',1, 1500 
union
select 1,'c',1, 15700 
union
select 1,'d',2, 1000 
union
select 1,'e',2, 1200 
union
select 1,'g',3, 1500 

I have tried the common way of getting the maximum salary for each category using row_number function.

我尝试了使用row_number函数获取每个类别的最高工资的常用方法。

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 
)
select * from cte

6 个解决方案

#1

Select EmpID,empname,deptid,salary
 From (
Select *
      ,RN  = Row_Number() over (Partition By deptid Order By Salary)
      ,Cnt = sum(1) over (Partition By deptid)
 From  employee1
      ) A
 Where RN = case when Cnt<3 then Cnt else 3 end

Returns

返回

#2

The answer will depend if you want ties and how to handle them. If you want no ties and even if one employee ties with another it becomes the next highest salary then the trick is to use a row_number like the one you are showing only with descending on salary and then use another row_number to reverse it. If you did not want to use row_number a second time you could do it with a few other techniques as well but step 1 is find highest step 2 is to reverse that order

答案将取决于您是否想要关系以及如何处理它们。如果你不想要任何关系，即使一个员工与另一个员工联系也会成为下一个最高薪水，那么诀窍就是使用row_number，就像你只显示工资下降那样，然后使用另一个row_number来反转它。如果你不想第二次使用row_number，你也可以使用其他一些技术，但第1步是找到最高的第2步是颠倒那个顺序

; WITH cteRankSalariesByDepartment AS (
    SELECT
       *
       ,RowNum = DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary DESC)
    FROM
       employee1
)

, cteReverseRankHighestSalaries AS (
    SELECT
       *
       ,RowNum2 = DENSE_RANK() OVER (PARTITION BY deptid ORDER BY RowNum DESC)
    FROM
       cteRankSalariesByDepartment
    WHERE
       RowNum <= 3
)

SELECT *
FROM
    cteReverseRankHighestSalaries
WHERE
    RowNum2 = 1

Per your comment updated to DENSE_RANK() you could simply use it in place of row_number() and you will get your ties.

根据你的评论更新到DENSE_RANK（）你可以简单地用它代替row_number（），你就会得到你的联系。

#3

Just you query needs count and row_number with condition as below:

只需查询需要count和row_number的条件如下：

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary desc) as id, 
    Cnt = count(*) over(partition by deptid), * from employee1 
)
select * from cte where ( cnt >= 3 and id = 3 )
or ( cnt < 3 and id = 1 )

#4

you can try a query like below:

您可以尝试如下查询：

select * from 
(
select 
 empid, 
 empname ,
 deptid ,
 salary ,
 ROW_NUMBER( ) over( partition by deptid order by id desc) as rev_id
from
( 
select 
 ROW_NUMBER( ) over( partition by deptid order by salary) as id, 
 empid, 
 empname ,
 deptid ,
 salary 
from employee1 
)
t where id<=3 
)t where rev_id=1

working demo

工作演示

#5

You could use UNION

你可以使用UNION

;with cte
as
( 
select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 
)

--get the 3rd highest
select 
   * 
from cte
where id = 3 

union 

--get the highest / max
select 
   c.*
from cte c
--this determines the highest which salary for each dept
inner join
   (select deptid, max(id) id 
    from cte 
    group by deptid) x on x.deptid = c.deptid and x.id = c.id
--this limits it on depts that aren't in the list in the first part of the query
where 
   c.deptid not in (select deptid from cte where id = 3)

#6

To increase your question I added two employees with the same salary in the third position.

为了增加你的问题，我在第三个位置添加了两名薪水相同的员工。

To get this you need the first dense_rank the salary by department. After you need to reverse l the salary rank and get the position 1

要得到这个，你需要按部门的第一个dense_rank工资。在您需要反转l工资等级并获得位置1之后

try it

尝试一下

    DECLARE @employee1 TABLE 
      ( 
         empid   INT, 
         empname VARCHAR(10), 
         deptid  INT, 
         salary  MONEY 
      ) 

    INSERT @employee1 Values 
    (1,'a',1, 1000  )
,(1,'b',1, 1200 )
,(2,'bb',1, 1200 )
,(1,'c',1, 1500 )
,(3,'ccc',1, 1500 )
,(1,'c',1, 15700) 
,(1,'d',2, 1000 )
,(1,'e',2, 1200 )
,(1,'g',3, 1500 ) 

    WITH cte_rank 
         AS (SELECT Dense_rank() 
                      OVER ( 
                        partition BY deptid 
                        ORDER BY salary) SalaryRank, 
                    * 
             FROM   @employee1), 
         cte_final 
         AS (SELECT Dense_rank() 
                      OVER ( 
                        partition BY deptid 
                        ORDER BY salaryrank DESC) SalaryRankReverse, 
                    * 
             FROM   cte_rank 
             WHERE  salaryrank <= 3) 
    SELECT * 
    FROM   cte_final 
    WHERE  salaryrankreverse = 1 

Result

结果

SalaryRankReverse    SalaryRank           empid       empname    deptid      salary
-------------------- -------------------- ----------- ---------- ----------- ---------------------
1                    3                    1           c          1           1500.00
1                    3                    3           ccc        1           1500.00
1                    2                    1           e          2           1200.00
1                    1                    1           g          3           1500.00