用于查找第二高的SQL

Question

假设我有一个雇员表，其中我在一列中保存了雇员的姓名，在另一列中保存了他们领取的薪水。我应该写什么SQL来找到拿到第二高薪水的员工的名字？

Answer 1

我会使用row_number()

select e.*
from (select e.*, row_number() over (order by salary desc) as seqnum
      from employees e
     ) e
where seqnum = 1

其他人提到了limit/top/rownum方法：

select e.*
from (select e.*
      from employees e
      order by salary desc
      limit 2
     ) t
order by salary
limit 1

您可以使用offset执行相同的操作：

select e.*
from employees e
order by salary desc
limit 2, 1

然而，如果你有一条领带来获得最高的薪水，那么所有这些都有问题。第一种方法很容易修复，只需用dense_rank()替换row_number()即可

select e.*
from (select e.*, dense_rank() over (order by salary desc) as seqnum
      from employees e
     ) e
where seqnum = 1

另外，另一种方法是：

select e.*
from employees e
where e.salary < (select max(salary) from employees)
order by salary desc
limit 1  -- or top 1 or rownum = 1 etc.

Answer 2

您可以尝试从"Top 2“中进行选择，按升序排序，然后是"Top 1”，它应该会给出正确的行。

Answer 3

你可以这样做：

select * from (select * from Employees order by salary desc limit 2) as A order by salary asc limit 1;