SQL 如何从工资表中找到第三个或第 n 个最高工资？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16234983/
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 find third or nth maximum salary from salary table?
提问by Karan Gandhi
How to find third or nthmaximum salary from salary table(EmpID,EmpName,EmpSalary)in Optimized way?
如何以优化的方式third or nth从工资table(EmpID,EmpName,EmpSalary)中找到最高工资？
采纳答案by Tim Schmelter
Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):
使用ROW_NUMBER（如果你想要一个）或DENSE_RANK（对于所有相关的行）：
WITH CTE AS
(
SELECT EmpID, EmpName, EmpSalary,
RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow
回答by Kumar Manish
Row Number :
行号：
SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
FROM EMPLOYEE
) As A
WHERE A.RowNum IN (2,3)
Sub Query :
子查询：
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary
)
Top Keyword :
热门关键词：
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC
) a
ORDER BY salary
回答by Codesen
Try this
尝试这个
SELECT TOP 1 salary FROM (
SELECT TOP 3 salary
FROM employees
ORDER BY salary DESC) AS emp
ORDER BY salary ASC
For 3 you can replace any value...
对于 3，您可以替换任何值...
回答by Rajesh Pathakoti
If you want optimize way means use TOPKeyword, So the nth max and min salaries query as follows but the queries look like a tricky as in reverse order by using aggregate function names:
如果您想要优化方式意味着使用TOP关键字，因此第 n 个最大和最小工资查询如下，但查询看起来像使用聚合函数名称相反顺序的棘手：
N maximum salary:
N 最高工资：
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC)
for Ex: 3 maximum salary:
例如：3 最高工资：
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC)
N minimum salary:
N 最低工资：
SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)
for Ex: 3 minimum salary:
例如：3 最低工资：
SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)
回答by devutkarsh
Too simple if you use the sub query!
如果使用子查询就太简单了！
SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);
You can here just change the nth value after the LIMIT constraint.
您可以在此处更改 LIMIT 约束后的第 n 个值。
Here in this the Sub query Select EmpSalary from Employee Order by EmpSalary DESC Limit 3; would return the top 3 salaries of the Employees. Out of the result we will choose the Minimum salary using MIN command to get the 3rd TOP salary of the employee.
在这里，子查询 Select EmpSalary from Employee Order by EmpSalary DESC Limit 3; 将返回员工的前 3 名工资。在结果中，我们将使用 MIN 命令选择最低工资以获取员工的第 3 名最高工资。
回答by Luv
Replace N with your Max Number
用您的最大数量替换 N
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
Explanation
解释
The query above can be quite confusing if you have not seen anything like it before – the inner query is what's called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it's WHERE clause.
如果您以前从未见过类似的查询，则上面的查询可能会非常令人困惑——内部查询是所谓的相关子查询，因为内部查询（子查询）使用来自外部查询（在本例中为 Emp1 表）的值) 在它的 WHERE 子句中。
And Source
和来源
回答by Darvi Sunny
Third or nth maximum salary from salary table without using subquery
不使用子查询的工资表中的第三个或第 n 个最高工资
select salary from salary
ORDER BY salary DESC
OFFSET N-1 ROWS
FETCH NEXT 1 ROWS ONLY
For 3rd highest salary put 2 in place of N-1
对于第三高的工资，用 2 代替 N-1
回答by Pankaj Gaikwad
SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from EMPLOYEE
) tbl
WHERE Rno=3
回答by Vijay Bhatt
Refer following query for getting nth highest salary. By this way you get nth highest salary in MYSQL. If you want get nth lowest salary only you need to replace DESC by ASC in the query.
请参阅以下查询以获取第 n 个最高工资。通过这种方式，您在 MYSQL 中获得了第 n 高的薪水。如果您只想获得第 n 个最低工资，则需要在查询中将 DESC 替换为 ASC。
回答by Vijay Bhatt
Method 1:
方法一：
SELECT TOP 1 salary FROM (
SELECT TOP 3 salary
FROM employees
ORDER BY salary DESC) AS emp
ORDER BY salary ASC
Method 2:
方法二：
Select EmpName,salary from
(
select EmpName,salary ,Row_Number() over(order by salary desc) as rowid
from EmpTbl)
as a where rowid=3
