本文介紹了根據表格數據找到每個部門的第三個最高工資的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我需要在表中找出每個部門員工的第三個最高工資.如果不存在第三個最高工資，則顯示第二個最高工資.如果不存在第二個最高工資，則找到最高工資.如何在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
退貨
這篇關于根據表格數據找到每個部門的第三個最高工資的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！