如何获得表中薪水第二高的员工 [英] How to get second-highest salary employees in a table

问题描述

这是我今天下午遇到的一个问题:

It's a question I got this afternoon:

在SQL Server中有一个表，包含ID，姓名和员工薪水，获取薪水第二高的员工的姓名

There a table contains ID, Name, and Salary of Employees, get names of the second-highest salary employees, in SQL Server

这是我的答案，我只是将其写在纸上，不确定它是否完全有效，但似乎可行:

Here's my answer, I just wrote it in paper and not sure that it's perfectly valid, but it seems to work:

SELECT Name FROM Employees WHERE Salary = 
( SELECT DISTINCT TOP (1) Salary FROM Employees WHERE Salary NOT IN
 (SELECT DISTINCT TOP (1) Salary FROM Employees ORDER BY Salary DESCENDING)
ORDER BY Salary DESCENDING)

我认为这很丑陋，但这是我脑海中唯一的解决方案.

I think it's ugly, but it's the only solution come to my mind.

您能建议我一个更好的查询吗?

Can you suggest me a better query?

非常感谢您.

推荐答案

SELECT * from Employee 
WHERE Salary IN (SELECT MAX(Salary) 
                 FROM Employee 
                 WHERE Salary NOT IN (SELECT MAX(Salary) 
                                      FFROM employee));

尝试这样..

这篇关于如何获得表中薪水第二高的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！