[SQL] LeetCode题解 #176 第二高的薪水

对SQL语句不熟悉欢迎查看我整理的笔记：[SQL] MySQL基础 + python交互

转载请注明：陈熹 chenx6542@foxmail.com （简书号：半为花间酒）
若公众号内转载请联系公众号：早起Python

题目：

SQL架构：

Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values ('1', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');

题解：

如果对子查询不熟悉的伙伴可以品一品下面三种解法，建立子查询的位置各不相同：

第一种：SELECT中建立
第二种：FROM中建立
第三种：WHERE中建立

- 第一种解法

这题按照题意就是对薪水去重、降序排序后取第二个，可以有如下写法：

SELECT DISTINCT Salary AS SecondHighestSalary
FROM Employee 
ORDER BY Salary DESC 
LIMIT 1 OFFSET 1;

我比较喜欢用OFFSET，限制分页代码有两种主流写法：

LIMIT X, Y
# 等价于
LIMIT Y OFFSET X

这个SQL语句已经可以应对多数场景，但忽略了一个特殊情况：

所有人的薪水一样，不存在第二高

因此需要加一个IFNULL判断，补充这个知识点

IFNULL(x，y)
# 若x不为空则返回x，否则返回y

因此，配合IFNULL可以将上述代码作为子查询：

SELECT
IFNULL (
    (SELECT DISTINCT Salary
    FROM Employee 
    ORDER BY Salary DESC 
    LIMIT 1 OFFSET 1),
    NULL)
AS SecondHighestSalary;

实际上不用IFNULL也可，直接将第一步的代码作为临时表，选择失败就会返回NULL

但用IFNULL可以增强可读性，代码不仅是跟电脑交互，也是给人读的

SELECT (
     SELECT DISTINCT Salary
     FROM Employee 
     ORDER BY Salary DESC 
     LIMIT 1 OFFSET 1)
AS SecondHighestSalary;

- 第二种解法

用到排名函数：DENSE_RANK
DENSE_RANK函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值

SELECT salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) rank_num 
FROM Employee;

利用排名行数就可能稳妥选出第二高的薪水了，利用WHERE判断2即可

完整代码如下：

SELECT MAX(salary) SecondHighestSalary
FROM (SELECT salary, 
      DENSE_RANK() OVER (ORDER BY salary DESC) rank_num 
      FROM Employee) Q
WHERE rank_num = 2;

注意一定要用MAX(salary)而不能直接用salary

用salary时可能会返回多个结果。更重要的是，当同样是全部人薪水相同时，用salary是选行号为2会返回空值，但用MAX(salary)会由于匹配不到返回NULL，真正满足特殊情况

- 第三种解法

只要记住利用MAX()这类函数无需特别考虑NULL后，还有一种比较直接的思路：

比最大值小的所有数据中，最大的就是第二大

在WHERE里建立子查询

SELECT MAX(Salary) SecondHighestSalary
FROM Employee
WHERE salary < (
    SELECT MAX(salary) 
    FROM Employee);

最后编辑于：2020.05.06 11:06:46