SQL查询以查找第二高工资？

考虑下面的简单表格：

Name     Salary---------------abc     100000bcd     1000000efg     40000ghi     500000

如何找到薪水第二高的员工。例如，在上表中，“ghi”的工资排名第二，为50万。

下面是一个简单的查询，可以找到薪资最高的员工。

  select *from employee where salary=(select Max(salary) from employee);

我们可以嵌套上面的查询来查找第二大薪资。

select *from employee group by salary order by  salary desc limit 1,1;

还有其他方法：

SELECT name, MAX(salary) AS salary FROM employee WHERE salary IN(SELECT salary FROM employee MINUS SELECT MAX(salary) FROM employee); 

SELECT name, MAX(salary) AS salary FROM employee WHERE salary <> (SELECT MAX(salary) FROM employee);

在SQL Server中使用公共表表达式或 CTE ，我们可以找到第二高的薪水：

WITH T AS(SELECT *   DENSE_RANK() OVER (ORDER BY Salary Desc) AS RnkFROM Employees)SELECT NameFROM TWHERE Rnk=2;

如何找到第三大薪资？ 很简单，我们可以再做一次嵌套。

SELECT name, MAX(salary) AS salary  FROM employee WHERE salary < (SELECT MAX(salary)                  FROM employee                 WHERE salary < (SELECT MAX(salary)                 FROM employee)                ); 

请注意，我们不用嵌套第二、第三等最大薪资，而是可以使用MySQL中的常规查询找到第n个薪资：

SELECT salary FROM employee ORDER BY salary desc limit n-1,1

SELECT name, salaryFROM employee AWHERE n-1 = (SELECT count(1)              FROM employee B              WHERE B.salary>A.salary)

如果多个员工的工资相同。 假设你必须找到第四高的薪水

SELECT * FROM employee WHERE salary= (SELECT DISTINCT(salary) FROM employee ORDER BY salary LIMIT 3,1);

通用查询将被删除

SELECT * FROM employee WHERE salary= (SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1);

该解决方案由卫生部提供。

本文由Kartik撰稿。如果您发现任何不正确的地方，或者您想分享有关上述主题的更多信息，请写下评论。