问题描述
我需要在表中找出每个部门员工的第三个最高工资.如果不存在第三个最高工资,则显示第二个最高工资.如果不存在第二个最高工资,则找到最高工资.如何在sql-server中实现这个结果?
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?
table 结构如下
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 函数获取每个类别最高工资的常用方法.
I have tried the common way of getting the maximum salary for each category using row_number function.
;with cte as ( select ROW_NUMBER( ) over( partition by deptid order by salary) as id, * from employee1 ) select * from cte
推荐答案
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
退货