SQL 如何在不使用 TOP 和子查询的情况下从表中获取第 n 个最高工资？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3645528/
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 fetch the nth highest salary from a table without using TOP and sub-query?
提问by HotTester
Recently in an interview I was asked to write a query where I had to fetch nth highest salary from a table without using TOP and any sub-query ?
最近在一次采访中，我被要求编写一个查询，我必须在不使用 TOP 和任何子查询的情况下从表中获取第 n 个最高工资？
I got totally confused as the only way I knew to implement it uses both TOP and sub-query.
我完全困惑，因为我知道实现它的唯一方法是同时使用 TOP 和子查询。
Kindly provide its solution.
请提供其解决方案。
Thanks in advance.
提前致谢。
回答by marc_s
Try a CTE - Common Table Expression:
尝试 CTE - 通用表表达式：
WITH Salaries AS
(
SELECT
SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum'
FROM
dbo.SalaryTable
)
SELECT
SalaryAmount
FROM
Salaries
WHERE
RowNum <= 5
This gets the top 5 salaries in descending order - you can play with the RowNumnvalue and basically retrieve any slice from the list of salaries.
这将按降序获取前 5 个薪水 - 您可以使用该RowNumn值并基本上从薪水列表中检索任何部分。
There are other ranking functionsavailable in SQL Server that can be used, too - e.g. there's NTILEwhich will split your results into n groups of equal size (as closely as possible), so you could e.g. create 10 groups like this:
SQL Server 中还有其他可用的排名功能，也可以使用 - 例如NTILE，将您的结果分成 n 个大小相等的组（尽可能接近），因此您可以创建 10 个这样的组：
WITH Salaries AS
(
SELECT
SalaryAmount, NTILE(10) OVER(ORDER BY SalaryAmount DESC) AS 'NTile'
FROM
dbo.SalaryTable
)
SELECT
SalaryAmount
FROM
Salaries
WHERE
NTile = 1
This will split your salaries into 10 groups of equal size - and the one with NTile=1is the "TOP 10%" group of salaries.
这会将您的工资分成 10 个大小相等的组——其中一组NTile=1是“前 10%”的工资组。
回答by Martin Smith
;with cte as(
Select salary,
row_number() over (order by salary desc) as rn
from salaries
)
select salary
from cte
where rn=@n
(or use dense_rankin place of row_numberif you want the nth highest distinct salary amount)
（或者如果您想要第 n 个最高的不同工资金额dense_rank，row_number则使用代替）
回答by Venkatesh
Select *
From Employee E1
Where
N = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary >= E1.Salary)
回答by abc
with cte as(
select VendorId,IncomeDay,IncomeAmount,
Row_Number() over ( order by IncomeAmount desc) as RowNumber
from DailyIncome
)
select * from cte
where RowNumber=2
回答by Siddharth Arekar
Display 5th Min Sal Emp Table.
显示第 5 个 Min Sal Emp 表。
SELECT * FROM (SELECT Dense_Rank () Over (ORDER BY Sal ASC) AS Rnk, Emp.* FROM Emp) WHERE
Rnk=5;
回答by Rehan Shikkalgar
To find the Nth highest salary :
Table name - Emp
找到第 N 个最高工资：
表名 - Emp
emplyee_id salary
1 2000
2 3000
3 5000
4 8000
5 7000
6 2000
7 1000
sql query -> here N is higest salary to be found :
sql 查询 -> 这里 N 是要找到的最高工资：
select salary from (select salary from Emp order by salary DESC LIMIT N) AS E order by ASC LIMIT 1;
回答by ullas kishan
try this. It may very easy to find nth rank items by using CTE **
尝试这个。使用 CTE 可以很容易地找到第 n 个项目 **
with result AS
(
SELECT *,dense_rank() over( order by Salary) as ranks FROM Employee
)
select *from RESULT Where ranks = 2
**
**
回答by Prabhu shanmughapriyan
Try this.
尝试这个。
SELECT * FROM
(SELECT Salary,
rownum AS roworder
FROM (select distinct Salary from employer)
ORDER BY Salary
)
where roworder = 6
;
回答by manish
It can simply be done as following for second highest-
对于第二高，它可以简单地按照以下方式完成-
Select MAX(Salary) from employer where Salary NOT IN(Select MAX(Salary) from employer);
But for Nth highest we have to use CTE(Common Table Expression).
但是对于第 N 个最高值，我们必须使用 CTE（通用表表达式）。
回答by Harshal S
If there are duplicate entries of
如果有重复的条目
30,000,
23,000,
23,000,
15,000,
14,800
30,000,
23,000,
23,000,
15,000,
14,800
then above selected query will not return correct output.
那么上面选择的查询将不会返回正确的输出。
find correct query as below:
找到正确的查询如下：
with salaries as
(
select Salary,DENSE_RANK() over (order by salary desc) as 'Dense'
from Table_1
)
select distinct salary from salaries
where dense=3
