获取MySQL表中的第二高值 [英] Get the second highest value in a MySQL table

问题描述

我有这样定义的员工和薪水表:

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?

推荐答案

以下是解决关系的一种方法.

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

我接受了Manoj的第二篇文章，对其进行了调整，并使它更具可读性.对我而言， n-1 不直观；但是，使用我想要的值是2 = 2nd，3 = 3rd等.

I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.

/* 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

这篇关于获取MySQL表中的第二高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！