题目
编写一个SQL查询,获取Employee表中第二高的薪水(Salary)。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述Employee表,SQL查询应该返回200作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/second-highest-salary
答案及分析
方法一:使用子查询和 LIMIT 子句
将不同的薪资按降序排序,然后使用 LIMIT 子句获得第二高的薪资。
SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
然而,如果没有这样的第二最高工资,这个解决方案将被判断为 “错误答案”,因为本表可能只有一项记录。为了克服这个问题,可以将其作为临时表。
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary
方法二:使用 IFNULL 和 LIMIT 子句
解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数,如下所示。
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
方法三
从排除了最大值的数据中取最大值,取得就是第二高的薪水
select max(Salary) SecondHighestSalary
from Employee
where Employee.Salary not in (select max(Salary) from Employee )
或者
select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)
或者
SELECT MAX(Salary) SecondHighestSalary
FROM Employee
WHERE Salary <> (SELECT MAX(Salary) FROM Employee)