MySQL 我如何从员工表中找到第二大工资？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2160262/
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 would I find the second largest salary from the employee table?
提问by Dinesh
How would I go about querying for the second largest salary from all employees in my Employee table?
我将如何查询 Employee 表中所有员工的第二大薪水？
回答by deepa
Try this:
尝试这个：
SELECT max(salary)
FROM emptable
WHERE salary < (SELECT max(salary)
FROM emptable);
回答by SandeepGodara
Simple Answer:
简单回答：
SELECT distinct(sal)
FROM emp
ORDER BY sal DESC
LIMIT 1, 1;
You will get only the second max salary.
你只会得到第二个最高工资。
And if you need any 3rd or 4th or Nth value you can increase the first value followed by LIMIT (n-1)ie. for 4th salary : LIMIT 3, 1;
如果您需要任何第 3 个或第 4 个或第 N 个值，您可以增加第一个值，然后是LIMIT (n-1)ie。第四薪：LIMIT 3, 1;
回答by Dharmendra Tomar
Most of the other answers seem to be db specific.
大多数其他答案似乎是特定于数据库的。
General SQL query should be as follows:
一般的SQL查询应该如下：
select
sal
from
emp a
where
N = (
select
count(distinct sal)
from
emp b
where
a.sal <= b.sal
)
where
N = any value
and this query should be able to work on any database.
并且这个查询应该能够在任何数据库上工作。
回答by John Feminella
Try something like:
尝试类似：
SELECT TOP 1 compensation FROM (
SELECT TOP 2 compensation FROM employees
ORDER BY compensation DESC
) AS em ORDER BY compensation ASC
Essentially:
本质上：
- Find the top 2 salaries in descending order.
- Of those 2, find the top salary in ascending order.
- The selected value is the second-highest salary.
- 按降序查找前 2 个薪水。
- 在这 2 个中，按升序找到最高工资。
- 选定的值是第二高的工资。
If the salaries aren't distinct, you can use SELECT DISTINCT TOP ...instead.
如果薪水不明显，您可以SELECT DISTINCT TOP ...改用。
回答by CoderHawk
Maybe you should use DENSE_RANK.
也许你应该使用DENSE_RANK.
SELECT *
FROM (
SELECT
[Salary],
(DENSE_RANK()
OVER
(
ORDER BY [Salary] DESC)) AS rnk
FROM [Table1]
GROUP BY [Num]
) AS A
WHERE A.rnk = 2
回答by brundaban sathua
select max(Emp_Sal)
from Employee a
where 1 = ( select count(*)
from Employee b
where b.Emp_Sal > a.Emp_Sal)
Yes running man.
是的跑男。
回答by fazarul
To find second max salary from employee,
要从员工那里找到第二个最高工资，
SELECT MAX(salary) FROM employee
WHERE salary NOT IN (
SELECT MAX (salary) FROM employee
)
To find first and second max salary from employee,
要从员工那里找到第一和第二个最高工资，
SELECT salary FROM (
SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC
) WHERE rownum<=2
This queries are working fine because i have used
此查询工作正常，因为我使用过
回答by James Black
Try this:
尝试这个：
SELECT
salary,
employeeid
FROM
employees
ORDER BY
salary DESC
LIMIT 2
Then just get the second row.
然后只得到第二行。
回答by hanimi
select distinct(t1.sal)
from emp t1
where &n=(select count(distinct(t2.sal)) from emp t2 where t1.sal<=t2.sal);
Output: Enter value for n: if you want 2nd highest ,enter 2; if you want 5,enter n=3
输出：输入 n 的值：如果你想要第二高，输入 2；如果你想要 5，输入 n=3
回答by Himalaya Garg
//To select name of employee whose salary is second highest
//选择工资第二高的员工姓名
SELECT name
FROM employee WHERE salary =
(SELECT MIN(salary) FROM
(SELECT TOP (2) salary
FROM employee
ORDER BY salary DESC) )
