获取 MySQL 表中的第二大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5360894/
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
Get the second highest value in a MySQL table
提问by laurent
I have a table of employees and salaries defined that way:
我有一个以这种方式定义的员工和工资表：
"name" (type: VARCHAR)
"salary" (type: INTEGER)
What query can I use to get the second highest salary in this table?
我可以使用什么查询来获得此表中第二高的薪水？
回答by Dawson
Here's one that accounts for ties.
这是一个说明关系的方法。
Name Salary
Jim 6
Foo 5
Bar 5
Steve 4
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))
Result --> Bar 5, Foo 5
EDIT:I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.
编辑：我接受了 Manoj 的第二篇文章，对其进行了调整，使其更具人类可读性。对我来说n-1不直观；但是，使用我想要的值，2=2nd、3=3rd 等是。
/* looking for 2nd highest salary -- notice the '=2' */
SELECT name,salary FROM employees
WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2
WHERE e1.salary <= e2.salary)) ORDER BY name
Result --> Bar 5, Foo 5
回答by Mr.M
A straight forward answer for second highest salary
对第二高薪水的直接回答
SELECT name, salary
FROM employees ORDER BY `employees`.`salary` DESC LIMIT 1 , 1
another interesting solution
另一个有趣的解决方案
SELECT salary
FROM emp
WHERE salary = (SELECT DISTINCT(salary)
FROM emp as e1
WHERE (n) = (SELECT COUNT(DISTINCT(salary))
FROM emp as e2
WHERE e1.salary <= e2.salary))
回答by Sunny Sharma
Seems I'm much late to answer this question. How about this one liner to get the same output?
看来我回答这个问题已经晚了。这一个班轮如何获得相同的输出？
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1,1 ;
sample fiddle: https://www.db-fiddle.com/f/v4gZUMFbuYorB27AH9yBKy/0
回答by Nicola Cossu
create table svalue (
name varchar(5),
value int
) engine = myisam;
insert into svalue value ('aaa',30),('bbb',10),('ccc',30),('ddd',20);
select * from svalue where value = (
select value
from svalue
group by value
order by value desc limit 1,1)
回答by sandeepKumar
FOR SECOND LAST:
倒数第二：
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 1 , 1
FOR THIRD LAST:
倒数第三：
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 2 , 1
回答by Mr.M
Found another interesting solution
找到另一个有趣的解决方案
SELECT salary
FROM emp
WHERE salary = (SELECT DISTINCT(salary)
FROM emp as e1
WHERE (n) = (SELECT COUNT(DISTINCT(salary))
FROM emp as e2
WHERE e1.salary <= e2.salary))
Sorry. Forgot to write. n is the nth number of salary which you want.
对不起。忘记写了。n 是您想要的第 n 个薪水。
回答by SUJI KUMAR.T.S
To display records having second largest value of mark:
要显示具有第二大标记值的记录：
SELECT username, mark
FROM tbl_one
WHERE mark = (
SELECT DISTINCT mark
FROM tbl_one
ORDER by mark desc
LIMIT 1,1
);
回答by Sonia G
simple solution
简单的解决方案
SELECT * FROM TBLNAME ORDER BY COLNAME ASC LIMIT (n - x), 1
Note: n = total number of records in column
注意：n = 列中的记录总数
x = value 2nd, 3rd, 4th highest etc
e.g
例如
//to find employee with 7th highest salary
n = 100
x = 7
SELECT * FROM tbl_employee ORDER BY salary ASC LIMIT 93, 1
hope this helps
希望这可以帮助
回答by Vishnu
You can use this below mentioned query
您可以使用下面提到的查询
SELECT emp.name, emp.salary
FROM employees emp
WHERE 2 = (SELECT COUNT(DISTINCT salary)
FROM employees
WHERE emp.salary<=salary
);
You can change 2 to your desired highest record.
您可以将 2 更改为您想要的最高记录。
回答by jeswin
To get the second highest salary just use the below query
要获得第二高的薪水，只需使用以下查询
SELECT salary FROM employees
ORDER BY salary DESC LIMIT 1,1;
