从工资表中查找第 N 个最高工资的 SQL 查询

2021-12-10 00:00:00 sql tsql sql-server

如何在 SQL Server 中包含工资的表中找到第 N 个最高工资?

How can I find the Nth highest salary in a table containing salaries in SQL Server?

推荐答案

您可以使用通用表表达式 (CTE) 来获得答案.

You can use a Common Table Expression (CTE) to derive the answer.

假设您的工资表中有以下工资:

Let's say you have the following salaries in the table Salaries:

 EmployeeID  Salary
--------------------
     10101   50,000
     90140   35,000
     90151   72,000
     18010   39,000
     92389   80,000

我们将使用:

DECLARE @N int
SET @N = 3  -- Change the value here to pick a different salary rank

SELECT Salary
FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N

这将在按薪水降序排序后为每一行创建一个行号，然后检索第三行(包含第三高的记录).

This will create a row number for each row after it has been sorted by the Salary in descending order, then retrieve the third row (which contains the third-highest record).

  • SQL 小提琴

对于那些不想要 CTE(或陷入 SQL 2000)的人:

For those of you who don't want a CTE (or are stuck in SQL 2000):

[注意:这明显比上面的例子差；将它们与执行计划并排运行显示 CTE 的查询成本为 36%，子查询的查询成本为 64%]:

[Note: this performs noticably worse than the above example; running them side-by-side with an exceution plans shows a query cost of 36% for the CTE and 64% for the subquery]:

SELECT TOP 1 Salary
FROM 
(
    SELECT TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

其中 N 由您定义.

SalarySubquery 是我给子查询或括号中的查询的别名.

SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.

子查询的作用是选择前 N 个薪水(在本例中我们将说 3)，并按最高薪水对它们进行排序.

What the subquery does is it selects the top N salaries (we'll say 3 in this case), and orders them by the greatest salary.

如果我们想查看第三高的薪水，子查询将返回:

If we want to see the third-highest salary, the subquery would return:

 Salary
-----------
80,000
72,000
50,000

外部查询然后从子查询中选择第一个薪水，除了这次我们将它升序排序，从最小到最大排序，所以 50,000 将是第一个升序排序的记录.

The outer query then selects the first salary from the subquery, except we're sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.

如您所见，50,000 确实是示例中第三高的薪水.

As you can see, 50,000 is indeed the third-highest salary in the example.

相关文章