sql - 如何获取表格中薪资第二高的员工

Question

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

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

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

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)

我认为这很丑陋，但这是我想到的唯一解决方案。

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

非常感谢。

Answer 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)