当多个记录存在且第三高薪时，如何从员工表中获得第三高薪 [英] How to get third highest salary from employee table when multiple record exist with third highest salary

问题描述

我有员工表，上面有身份证，姓名和工资。



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.

1 E1 10,000
2 E2 20,000
3 E3 30,000
4 E4 20,000
5 E5 50,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.

推荐答案

尝试：

Try:

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

我相信你要的是获得第三高薪，然后重新查询员工表和让所有员工获得相同的第三高工资。



如果是这样，这是一种方法：

I believe what you are asking is to get the third highest salary and then re-query the employee table and get all the employees with the same third highest salary.

If that is the case, here is one way of doing it:

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.

这篇关于当多个记录存在且第三高薪时，如何从员工表中获得第三高薪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！