获取MySQL表中的第二高值

本文介绍了获取MySQL表中的第二高值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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