SQL 如何在表格中获得薪水第二高的员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7417415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get second-highest salary employees in a table
提问by Quan Mai
It's a question I got this afternoon:
这是我今天下午收到的一个问题：
There a table contains ID, Name, and Salary of Employees, get names of the second-highest salary employees, in SQL Server
有一个表包含员工的 ID、姓名和薪水，在 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?
你能建议我一个更好的查询吗？
Thank you very much.
非常感谢。
采纳答案by Martin Smith
To get the names of the employees with the 2nd highest distinct salary amount you can use.
要获取您可以使用的具有第二高不同工资金额的员工的姓名。
;WITH T AS
(
SELECT *,
DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;
If Salary is indexed the following may well be more efficient though especially if there are many employees.
如果将工资编入索引，以下内容可能会更有效，但尤其是在有很多员工的情况下。
SELECT Name
FROM Employees
WHERE Salary = (SELECT MIN(Salary)
FROM (SELECT DISTINCT TOP (2) Salary
FROM Employees
ORDER BY Salary DESC) T);
Test Script
测试脚本
CREATE TABLE Employees
(
Name VARCHAR(50),
Salary FLOAT
)
INSERT INTO Employees
SELECT TOP 1000000 s1.name,
abs(checksum(newid()))
FROM sysobjects s1,
sysobjects s2
CREATE NONCLUSTERED INDEX ix
ON Employees(Salary)
SELECT Name
FROM Employees
WHERE Salary = (SELECT MIN(Salary)
FROM (SELECT DISTINCT TOP (2) Salary
FROM Employees
ORDER BY Salary DESC) T);
WITH T
AS (SELECT *,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
FROM Employees)
SELECT Name
FROM T
WHERE Rnk = 2;
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 DESC)
ORDER BY Salary DESC)
SELECT Name
FROM Employees
WHERE Salary = (SELECT TOP 1 Salary
FROM (SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC) sel
ORDER BY Salary ASC)
回答by amateur
SELECT * from Employee
WHERE Salary IN (SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary)
FFROM employee));
Try like this..
试试这样..
回答by meekash55
This might help you
这可能会帮助你
SELECT
MIN(SALARY)
FROM
EMP
WHERE
SALARY in (SELECT
DISTINCT TOP 2 SALARY
FROM
EMP
ORDER BY
SALARY DESC
)
We can find any nthhighest salary by putting n(where n > 0) in place of 2
我们可以通过用(where ) 代替nthnn > 02
Example for 5thhighest salary we put n = 5
我们给出的第5高工资示例n = 5
回答by marc_s
How about a CTE?
CTE怎么样？
;WITH Salaries AS
(
SELECT Name, Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS 'SalaryRank'
FROM
dbo.Employees
)
SELECT Name, Salary
FROM Salaries
WHERE SalaryRank = 2
DENSE_RANK()will give you all the employees who have the second highest salary - no matter how many employees have the (identical) highest salary.
DENSE_RANK()将为您提供所有拥有第二高薪水的员工 - 无论有多少员工拥有（相同的）最高薪水。
回答by Kaushik Lele
Another intuitive way is :- Suppose we want to find Nth highest salary then
另一种直观的方法是：-假设我们要找到第 N 个最高薪水
1) Sort Employee as per descending order of salary
1）按工资降序对员工进行排序
2) Take first N records using rownum. So in this step Nth record here is Nth highest salary
2) 使用rownum 取前N 条记录。所以在这一步中，第 N 个记录是第 N 个最高工资
3) Now sort this temporary result in ascending order. Thus Nth highest salary is now first record
3) 现在按升序对这个临时结果进行排序。因此，第 N 个最高工资现在是第一个记录
4) Get first record from this temporary result.
4) 从这个临时结果中获取第一条记录。
It will be Nth highest salary.
这将是第 N 高的工资。
select * from
(select * from
(select * from
(select * from emp order by sal desc)
where rownum<=:N )
order by sal )
where rownum=1;
In case there are repeating salaries then in innermost query distinct can be used.
如果有重复的薪水，则可以在最内层的查询中使用 distinct。
select * from
(select * from
(select * from
(select distinct(sal) from emp order by 1 desc)
where rownum<=:N )
order by sal )
where rownum=1;
回答by steave
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
回答by Pratik Patil
All of the following queries work for MySQL:
以下所有查询都适用于MySQL：
SELECT MAX(salary) FROM Employee WHERE Salary NOT IN (SELECT Max(Salary) FROM Employee);
SELECT MAX(Salary) From Employee WHERE Salary < (SELECT Max(Salary) FROM Employee);
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1;
SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2) AS Emp ORDER BY Salary LIMIT 1;
回答by Kaushik Lele
Simple way WITHOUT using any special feature specific to Oracle, MySQL etc.
不使用任何特定于 Oracle、MySQL 等的特殊功能的简单方法。
Suppose EMPLOYEE table has data as below. Salaries can be repeated.
假设 EMPLOYEE 表有如下数据。工资可以重复。
By manual analysis we can decide ranks as follows :-
通过手动分析，我们可以确定排名如下：-
Same result can be achieved by query
查询可以得到同样的结果
select *
from (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from
EMPLOYEE ) where distsal >tout.sal) as rank from EMPLOYEE tout
) result
order by rank
First we find out distinct salaries. Then we find out count of distinct salaries greater than each row. This is nothing but the rank of that id. For highest salary, this count will be zero. So '+1' is done to start rank from 1.
首先，我们找出不同的工资。然后我们找出大于每一行的不同工资的数量。这只不过是那个 id 的等级。对于最高薪水，此计数为零。所以'+1'是为了从1开始排名。
Now we can get IDs at Nth rank by adding where clause to above query.
现在我们可以通过在上面的查询中添加 where 子句来获取第 N 级的 ID。
select *
from (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from
EMPLOYEE ) where distsal >tout.sal) as rank from EMPLOYEE tout
) result
where rank = N;
回答by ericb
I think you would want to use DENSE_RANKas you don't know how many employees have the same salary and you did say you wanted nameS of employees.
我想你会想要使用，DENSE_RANK因为你不知道有多少员工有相同的薪水，而且你确实说过你想要员工的名字。
CREATE TABLE #Test
(
Id INT,
Name NVARCHAR(12),
Salary MONEY
)
SELECT x.Name, x.Salary
FROM
(
SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as Rnk
FROM #Test
) x
WHERE x.Rnk = 2
ROW_NUMBERwould give you unique numbering even if the salaries tied, and plain RANKwould not give you a '2' as a rank if you had multiple people tying for highest salary. I've corrected this as DENSE_RANKdoes the best job for this.
ROW_NUMBER即使工资相同，也会给你唯一的编号，RANK如果你有多个人并列最高工资，plain不会给你一个“2”作为排名。我已经纠正了这一点，因为DENSE_RANK这是最好的工作。
回答by Gopal Sanodiya
Below query can be used to find the nth maximum value, just replace 2 from nth number
下面的查询可用于查找第 n 个最大值，只需从第 n 个数字中替换 2
select * from emp e1 where 2 =(select count(distinct(salary)) from emp e2
where e2.emp >= e1.emp)
