从工资表中查找第 N 个最高工资的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3850034/
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
SQL query to find Nth highest salary from a salary table
提问by NoviceToDotNet
How can I find the Nth highest salary in a table containing salaries in SQL Server?
如何在包含 SQL Server 薪水的表中找到第 N 个最高薪水？
回答by LittleBobbyTables - Au Revtheitroad
You can use a Common Table Expression (CTE) to derive the answer.
您可以使用公用表表达式 (CTE) 来得出答案。
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
We will use:
我们将使用：
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).
这将在每行按薪水降序排序后为每一行创建一个行号，然后检索第三行（包含第三高的记录）。
For those of you who don't want a CTE (or are stuck in SQL 2000):
对于那些不想要 CTE（或被困在 SQL 2000 中）的人：
[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]:
[注意：这明显比上面的例子差；将它们与执行计划并排运行显示 CTE 的查询成本为 36%，子查询的查询成本为 64%]：
SELECT TOP 1 Salary
FROM
(
SELECT TOP N Salary
FROM Salaries
ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC
where N is defined by you.
其中 N 由您定义。
SalarySubqueryis the alias I have given to the subquery, or the query that is in parentheses.
SalarySubquery是我给子查询或括号中的查询的别名。
What the subquery does is it selects the top N salaries (we'll say 3in this case), and orders them by the greatest salary.
子查询的作用是选择前 N 个薪水（在本例中我们将说3），并按最高薪水对它们进行排序。
If we want to see the third-highest salary, the subquery would return:
如果我们想查看第三高的薪水，子查询将返回：
Salary
-----------
80,000
72,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.
如您所见，50,000 确实是示例中的第三高薪水。
回答by Andomar
You could use row_numberto pick a specific row. For example, the 42nd highest salary:
您可以row_number用来选择特定的行。例如，第 42 位最高薪水：
select *
from (
select row_number() over (order by Salary desc) as rn
, *
from YourTable
) as Subquery
where rn = 42
Windowed functions like row_numbercan only appear in selector order byclauses. The workaround is placing the row_numberin a subquery.
窗口函数 likerow_number只能出现在selectororder by子句中。解决方法是将 放在row_number子查询中。
回答by Mayank
select MIN(salary) from (
select top 5 salary from employees order by salary desc) x
回答by Rajiv Saxena
EmpID Name Salary
1 A 100
2 B 800
3 C 300
4 D 400
5 E 500
6 F 200
7 G 600
SELECT * FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary
)
Suppose you want to find 5th highest salary, which means there are total 4 employees who have salary greater than 5th highest employee. So for each row from the outer query check the total number of salaries which are greater than current salary. Outer query will work for 100 first and check for number of salaries greater than 100. It will be 6, do not match (5-1) = 6where clause of outerquery. Then for 800, and check for number of salaries greater than 800, 4=0false then work for 300 and finally there are totally 4 records in the table which are greater than 300. Therefore 4=4will meet the where clause and will return
3 C 300.
假设您要查找第 5 高工资，这意味着总共有 4 名雇员的工资高于第 5 高雇员。因此，对于来自外部查询的每一行，检查大于当前工资的工资总数。外部查询将首先为 100 工作并检查大于 100 的工资数。它将是 6，不匹配外部查询的(5-1) = 6where 子句。然后是800，检查工资数是否大于800，4=0false然后工作300，最后表中总共有4条记录大于300。因此4=4会遇到where子句，返回
3 C 300。
回答by Himanshu Namdeo
try it...
尝试一下...
use table_name
select MAX(salary)
from emp_salary
WHERE marks NOT IN (select MAX(marks)
from student_marks )
回答by Sonu Yadav
The easiest method is to get 2nd higest salaryfrom tablein SQL:
最简单的方法是2nd higest salary从tablein获取SQL：
sql> select max(sal) from emp where sal not in (select max(sal) from emp);
回答by Hassan Arafat
Dont forget to use the distinctkeyword:-
不要忘记使用distinct关键字：-
SELECT TOP 1 Salary
FROM
(
SELECT Distinct TOP N Salary
FROM Salaries
ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC
回答by Kaushik Lele
Simple way WITHOUT using any special feature specific to Oracle, MySQL etc. Suppose in EMPLOYEE table Salaries can be repeated. Use query to find out rank of each ID.
不使用任何特定于 Oracle、MySQL 等的特殊功能的简单方法。假设在 EMPLOYEE 表中工资可以重复。使用查询找出每个 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
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 Zinan Xing
Solution 1:This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS: (note: low performance because of subquery)
解决方案 1：这个查找第 N 个最高薪水的 SQL 应该可以在 SQL Server、MySQL、DB2、Oracle、Teradata 和几乎所有其他 RDBMS 中工作：（注意：由于子查询导致性能低下）
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.
在上面的查询中要理解的最重要的事情是每次外部查询处理一行时都会评估子查询。换句话说，内部查询不能独立于外部查询进行处理，因为内部查询也使用 Emp1 值。
In order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself.
为了找到第 N 个最高薪水，我们只需要找到比其本身大 N-1 个薪水的薪水。
Solution 2:Find the nth highest salary using the TOP keyword in SQL Server
解决方案2：在SQL Server中使用TOP关键字查找第n个最高薪水
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
Solution 3:Find the nth highest salary in SQL Server without using TOP
解决方案三：不使用TOP在SQL Server中查找第n个最高薪水
SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY
Note that I haven't personally tested the SQL above, and I believe that it will only work in SQL Server 2012 and up.
请注意，我没有亲自测试过上面的 SQL，我相信它只能在 SQL Server 2012 及更高版本中工作。
回答by Saif Grover
SELECT * FROM
(select distinct postalcode from Customers order by postalcode DESC)
limit 4,1;
4 here means leave first 4 and show the next 1.
4 这里的意思是留下第一个 4 并显示下一个 1。
Try this it works for me.
试试这个它对我有用。
