MySQL 从工资表中查找第 N 个最高工资的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11771984/
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 jauwad
some one help me to find out nth highest salary from the salary table in MYSQL
有人帮我从 MYSQL 的薪水表中找出第 n 位最高薪水
回答by babooney
Try this, n would be the nth item you would want to return
试试这个，n 将是你想要返回的第 n 个项目
SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC LIMIT n,1
回答by babooney
If you want to find nth Salary from a table (here n should be any thing like 1st or 2nd or 15th highest Salaries)
如果你想从一个表中找到第 n 个薪水（这里 n 应该是任何像 1st、2nd 或 15th 最高薪水的东西）
This is the Query for to find nth Salary:
这是查找第 n 个薪水的查询：
SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET (n-1)
If you want to find 8th highest salary, query should be :
如果你想找到第 8 高的薪水，查询应该是：
SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET 7
Note:OFFSET starts from 0th position, and hence use N-1 rule here
注意：OFFSET 从第 0 个位置开始，因此这里使用 N-1 规则
回答by Omesh
To get nth highest salary you need to first sort data by using ORDER BYand then select the nth highest record using LIMITwith OFFSET.
要获得第 n 个最高薪水，您需要先使用 using 对数据进行排序ORDER BY，然后使用LIMITwith选择第 n 个最高记录OFFSET。
SELECT DISTINCT(salary) AS salary
FROM tbl_salary
ORDER BY salary DESC
LIMIT 1 OFFSET (n - 1);
回答by Abhishek Kulkarni
SELECT * FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return N-1.
对于外部查询处理的每条记录，将执行内部查询，并返回有多少条记录的工资低于当前工资。如果您正在寻找第二高的薪水，那么只要内部查询返回 N-1，您的查询就会停止。
回答by 2787184
finding the highest salary
寻找最高薪水
select MAX(Salary) from Employee;
finding the 2nd highest salary
找到第二高的工资
Query-1
查询 1
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
Query-2
查询 2
select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )
finding the nth highest salary
找到第 n 个最高工资
Query-1
查询 1
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)
Query-2
查询 2
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
nth highest salary using the TOP keyword in SQL Server
在 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
Find the nth highest salary in MySQL
在 MySQL 中查找第 n 个最高薪水
SELECT Salary FROM Employee
ORDER BY Salary DESC LIMIT n-1,1
Find the nth highest salary in SQL Server
在 SQL Server 中查找第 n 个最高薪水
SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY
Find the nth highest salary in Oracle using rownum
使用 rownum 在 Oracle 中查找第 n 个最高薪水
select * from (
select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n; /*n is nth highest salary*/
Find the nth highest salary in Oracle using RANK
使用 RANK 在 Oracle 中查找第 n 个最高薪水
select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;
回答by Joe G Joseph
try this:
尝试这个：
select MIN(sal) from salary where sal in
(select sal from salary order by sal desc limit 9)
回答by sel
if wanna specified nth highest,could use rank method.
如果想指定第 n 个最高，可以使用排名方法。
To get the third highest, use
要获得第三高，请使用
SELECT * FROM
(SELECT @rank := @rank + 1 AS rank, salary
FROM tbl,(SELECT @rank := 0) r
order by salary desc ) m
WHERE rank=3
回答by Abhijeet Navgire
Here we can create the MYSQL function for this. nth highest salary from the Employee table.
在这里我们可以为此创建 MYSQL 函数。Employee 表中的第 n 个最高工资。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
例如，给定上面的 Employee 表，n = 2 的第 n 个最高工资是 200。如果没有第 n 个最高工资，则查询应返回 null。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE limitv INT;
SET limitv = N - 1;
RETURN (
Select IFNULL(
(select Distinct Salary from Employee order by Salary Desc limit limitv, 1),
NULL
) as getNthHighestSalary
);
END
回答by Omkar Singh
+-------+--------+
| name | salary |
+-------+--------+
| A | 100 |
| B | 200 |
| C | 300 |
| D | 400 |
| E | 500 |
| F | 500 |
| G | 600 |
+-------+--------+
IF YOU WANT TO SELECT ONLY Nth HIGHEST SALARY THEN:
如果您只想选择第 N 个最高薪水，那么：
SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1;
IF YOU WANT TO SELECT ALL EMPLOYEE WHO GETTING Nth HIGHEST SALARY THEN:
如果您想选择所有获得第 N 个最高薪水的员工，那么：
SELECT * FROM emp WHERE salary = (
SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1
);
回答by Nilesh Gosai
SELECT * FROM employe e1 WHERE n-1 = ( SELECT COUNT(DISTINCT(e2.salary)) FROM employe e2 WHERE e2.salary > e1.salary)
Where n = highest number of salary like 1,2,3
