本文介紹了根據(jù)表格數(shù)據(jù)找到每個部門的第三個最高工資的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我需要在表中找出每個部門員工的第三個最高工資.如果不存在第三個最高工資，則顯示第二個最高工資.如果不存在第二個最高工資，則找到最高工資.如何在sql-server中實現(xiàn)這個結(jié)果?
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 結(jié)構(gòu)如下
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
我已經(jīng)嘗試了使用 row_number 函數(shù)獲取每個類別最高工資的常用方法.
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
退貨
這篇關(guān)于根據(jù)表格數(shù)據(jù)找到每個部門的第三個最高工資的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！