MySQL 无限制地在mysql中找到第三高的薪水
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22854687/
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
finding the 3rd highest salary in mysql without limit
提问by black
i have been trying to find the 3rd highest salary of a table without using limit.
我一直试图在不使用限制的情况下找到一张桌子的第三高工资。
In case of 2nd highest salary i use
在第二高工资的情况下，我使用
SELECT salary FROM table WHERE salary < ( SELECT MAX( salary ) order by salary desc
SELECT salary FROM table WHERE salary < ( SELECT MAX( salary ) order by salary desc
to find the 3rd highest with limit i use
找到我使用的限制的第三高
select salary from one order by salary desc limit 3,1
select salary from one order by salary desc limit 3,1
now how to find nth salary without using limit?
现在如何在不使用限制的情况下找到第 n 个薪水？
回答by Jay Patel
Try this Query,
试试这个查询，
SELECT *
FROM one one1
WHERE ( 3 ) = ( SELECT COUNT( one2.salary )
FROM one one2
WHERE one2.salary >= one1.salary
)
Here WHERE ( n )yo can put any number to return that highest salary.
这里WHERE ( n )你可以输入任何数字来返回最高工资。
Check this Demo SQLFiddle
检查这个 Demo SQLFiddle
回答by John Woo
how about doing it with variables? just wondering why you don't want to use LIMIT.
用变量来做怎么样？只是想知道为什么你不想使用LIMIT.
SELECT salary
FROM
(
SELECT @rn := @rn + 1 rn,
a.salary
FROM tableName a, (SELECT @rn := 0) b
ORDER BY salary DESC
) sub
WHERE sub.rn = 3
回答by Sadikhasan
Try this
尝试这个
SELECT *
FROM table t1
WHERE (3) = (
SELECT COUNT(DISTINCT(t2.Salary))
FROM table t2
WHERE t2.Salary >= t1.Salary
);
