本文介绍了当多个记录存在且第三高薪时，如何从员工表中获得第三高薪的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有员工表，上面有身份证，姓名和工资。
1 E1 10,000
2 E2 20,000
3 E3 30,000
4 E4 20,000
5 E5 50,000
我需要第三高的全部记录。当有多个薪水第三高的记录时，我的查询应该是什么。 20,000是第三高的工资，在数据库中是两倍。
Hi,
I have employee table with Id,Name and salary.
1E110,000
2E220,000
3E330,000
4E420,000
5E550,000
I need third highest all record. What should be my query when there are multiple record with third highest salary. 20,000 is third highest salary and it is twice in database.
推荐答案
SELECT Id, Name, Salary
FROM (
SELECT Id, Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary, Id) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum = 3
select
*
from employee e
inner join (
select salary
from employee
order by salary desc
offset 3 rows fetch next 1 rows only
) ThirdHighest
on e.salary = ThirdHighest.salary
;
重点是，获得第三高薪，可以是以多种不同方式完成。
此示例使用OFFSET FETCH子句，这是一个SQL Server 2012+功能。
The main point is, is getting the third highest salary, which can be done in many different ways.
This example uses the OFFSET FETCH Clause, which is a SQL Server 2012+ feature.
这篇关于当多个记录存在且第三高薪时，如何从员工表中获得第三高薪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！