题目描述
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
1
2
3
4
5
6
7
| +----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
|
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
1
2
3
4
5
| +---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
|
思路
- 排序，取出排名第二的值
1
2
3
| select Salary from Employee
order by Salary desc
limit 1, 1;
|
- group by 过滤掉相同薪水
1
2
3
4
| select Salary from Employee
group by Salary
order by Salary desc
limit 1, 1;
|
- 当不存在第二高的薪水时，会返回空而不是
null，做个是否为 null 的判断
1
2
3
4
5
| select
ifnull(
(select Salary from Employee group by Salary order by Salary desc limit 1, 1),
null
) as SecondHighestSalary;
|
可以简写为
1
2
3
| select
(select Salary from Employee group by Salary order by Salary desc limit 1, 1)
as SecondHighestSalary;
|