查找员工表 MySQL 的最大和第二个最大工资
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21520038/
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
Find max and second max salary for a employee table MySQL
提问by dpsdce
Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary:
假设您有一个名为 Employee 的简单数据库表，它有 2 个名为 Employee ID 和 Salary 的列：
Employee
Employee ID Salary
3 200
4 800
7 450
I wish to write a query select max(salary) as max_salary, 2nd_max_salary from employee
我想写一个查询 select max(salary) as max_salary, 2nd_max_salary from employee
then it should return
那么它应该返回
max_salary 2nd_max_salary
800 450
i know how to find 2nd highest salary
我知道如何找到第二高的工资
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )
or to find the nth
或者找到第n个
SELECT FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
but i am unable to figureout how to join these 2 results for the desired result
但我无法弄清楚如何加入这 2 个结果以获得所需的结果
回答by Szymon
You can just run 2 queries as inner queries to return 2 columns:
您可以只运行 2 个查询作为内部查询以返回 2 列：
select
(SELECT MAX(Salary) FROM Employee) maxsalary,
(SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]
回答by Vignesh Kumar A
Try like this
像这样尝试
SELECT (select max(Salary) from Employee) as MAXinmum),(max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary)) FROM Employee);
(Or)
（或者）
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
In your case
在你的情况下
SELECT DISTINCT(column_name) FROM table_name ORDER BY column_name DESC limit 2,1;
回答by Swapnil Kumbhar
Simplest way to fetch second max salary & nth salary
获取第二个最高工资和第 n 个工资的最简单方法
select
DISTINCT(salary)
from employee
order by salary desc
limit 1,1
Note:
笔记：
limit 0,1 - Top max salary
limit 1,1 - Second max salary
limit 2,1 - Third max salary
limit 3,1 - Fourth max salary
回答by Abhishek Sarkar
The Best & Easiest solution:-
最好和最简单的解决方案：-
SELECT
max(salary)
FROM
salary
WHERE
salary < (
SELECT
max(salary)
FROM
salary
);
回答by Yogesh Suthar
You can write 2 subqueries like this example
你可以像这个例子一样编写 2 个子查询
SELECT (select max(Salary) from Employee) as max_id,
(select Salary from Employee order by Salary desc limit 1,1) as max_2nd
回答by Ahmed Januhasan
$q="select * from info order by salary desc limit 1,0"; // display highest 2 salary
or
或者
$q="select * from info order by salary desc limit 1,0"; // display 2nd highest salary
回答by Md. Rezwanul Haque
I think, It is the simplest way to find MAXand second MAXSalary.You may try this way.
我认为，这是最简单的查找MAX和第二MAX薪水的方法。您可以尝试这种方式。
SELECT MAX(Salary) FROM Employee; -- For Maximum Salary.
SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee); -- For Second Maximum Salary
回答by Faisal
You can write SQL query in any of your favorite database e.g. MySQL, Microsoft SQL Serveror Oracle. You can also use database specific feature e.g. TOP, LIMIT or ROW_NUMBER to write SQL query, but you must also provide a generic solution which should work on all database. In fact, there are several ways to find second highest salary and you must know a couple of them e.g. in MySQL without using the LIMITkeyword, in SQL Server without using TOPand in Oracle without using RANKand ROWNUM.
您可以在任何您喜欢的数据库中编写 SQL 查询，例如MySQL、Microsoft SQL Server或Oracle。您还可以使用特定于数据库的功能，例如 TOP、LIMIT 或 ROW_NUMBER 来编写 SQL 查询，但您还必须提供适用于所有数据库的通用解决方案。事实上，有几种方法可以找到第二高的薪水，您必须知道其中的几种，例如在不使用LIMIT关键字的MySQL 、不使用TOP 的SQL Server和不使用RANK和ROWNUM 的Oracle 中。
Generic SQL query:
通用 SQL 查询：
SELECT
MAX(salary)
FROM
Employee
WHERE
Salary NOT IN (
SELECT
Max(Salary)
FROM
Employee
);
Another solution which uses sub query instead of NOT IN clause. It uses <operator.
另一种使用子查询而不是 NOT IN 子句的解决方案。它使用<运算符。
SELECT
MAX(Salary)
FROM
Employee
WHERE
Salary < (
SELECT
Max(Salary)
FROM
Employee
);
回答by Jatin Phulera
Find Max salary of an employee
查找员工的最高工资
SELECT MAX(Salary) FROM Employee
Find Second Highest Salary
找到第二高的薪水
SELECT MAX(Salary) FROM Employee
Where Salary Not In (Select MAX(Salary) FROM Employee)
OR
或者
SELECT MAX(Salary) FROM Employee
WHERE Salary <> (SELECT MAX(Salary) FROM Employee )
Find Nth Max Salaryand for brief detail www.gurujipoint.com
查找Nth Max Salary并了解详细信息 www.gurujipoint.com
回答by Kaja Mydeen
`select max(salary) as first, (select salary from employee order by salary desc limit 1, 1) as second from employee limit 1`
For max salary simply we can use max function, but second max salary we should use sub query. in sub query we can use where condition to check second max salary or simply we can use limit.
对于最大工资，我们可以使用 max 函数，但是第二个最大工资我们应该使用子查询。在子查询中，我们可以使用 where 条件来检查第二个最大工资，或者我们可以简单地使用限制。
