Loading

LeetCode176——第二高的薪水

题目描述

编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

思路

  1. 排序,取出排名第二的值
select Salary from Employee
  order by Salary desc
  limit 1, 1;
  1. group by 过滤掉相同薪水
select Salary from Employee
  group by Salary
  order by Salary desc
  limit 1, 1;
  1. 当不存在第二高的薪水时,会返回空而不是 null,做个是否为 null 的判断
select
  ifnull(
    (select Salary from Employee group by Salary order by Salary desc limit 1, 1),
    null
  ) as SecondHighestSalary;

可以简写为

select 
  (select Salary from Employee group by Salary order by Salary desc limit 1, 1)
  as SecondHighestSalary;
posted @ 2019-06-19 11:42  超超不会飞  阅读(242)  评论(0编辑  收藏  举报