如何获得表中薪水第二高的员工

小编典典

如何获得表中薪水第二高的员工

sql

我今天下午有一个问题：

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

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

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)

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

你能建议我一个更好的查询吗？

非常感谢你。

阅读 150

2021-03-17

共1个答案

小编典典

要获取具有第二高不同薪水金额的雇员的姓名，您可以使用。

;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 MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

测试脚本

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)

2021-03-17