sql - 根据表格数据找到每个部门的第三个最高工资

Question

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

表结构如下所示

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 

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

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

Answer 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

返回