编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
例如上述 Employee
表,SQL查询应该返回 200
作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null
。
SQL Sever & MySQL:
思路:先找出小于表中第一高的薪水的所有薪水,去重,然后从中选出最高的薪水(即第二高薪水)
select DISTINCT max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary)
from Employee);
MySQL:先去重,然后降序,offset 1条过第一条,limit 1只取1条
select(
select DISTINCT Salary
from Employee
Order By Salary DESC
limit 1 offset 1
)
as SecondHighestSalary;