Leetcode176.第二高的薪水（简单）

题目
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

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

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

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

审题
乍一看这不就是把Salary排序然后用分页查询LIMIT把第二个取出来就可以了么。
(但其实有几个坑需要注意)

自己的解答

select Salary SecondHighestSalary
from employee
order by Salary DESC
limit 1,1;

没有通过，下面分析为什么没有通过

解析

如果薪水是这样的 1, 1, 1, 2, 2, ...
上述代码取出的依然是1 所以要利用DISTINCT去重。

2.如果薪水只有一个呢这样上述代码是有问题的
因此要排除这种情况

改进

利用IFNULL函数 ifnull(x，y)，若x不为空则返回x，否则返回y
利用子查询

select ifnull(
    (select DISTINCT Salary 
    from employee
    order by Salary DESC
    limit 1,1), null
) as SecondHighestSalary;

这样就通过了

再看看别的解法,比较一下快慢

其他解法
1.select null返回的是什么呢？返回的是NULL
可以利用这一点将前述的代码得到一个临时表,再用select语句

select (
    select DISTINCT Salary 
    from employee
    order by Salary DESC
    limit 1,1
) as SecondHighestSalary;

limit n子句表示查询结果返回前n条数据
offset m表示跳过m条语句
limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

2.也可以这样做先查出最大的salary 然后对小于最大的salary的salary去最大值不就是第二大了么

①先取出最大的salary

select max(salary)
from employee;

②取出小于①的最大的salary

select max(salary)  as SecondHighestSalary
from employee
where salary <(select max(salary)
                from employee);

3.(乍一看没理解)利用偏序关系

自己构造数据方便实践

CREATE DATABASE leetcode;

USE leetcode;
CREATE TABLE Employee (Id INT (10), Salary INT (10));
INSERT INTO Employee(Id, Salary) VALUE(1, 100), (2, 100), (3, 105), (4, 110), (5, 199);

SELECT *
FROM Employee;

什么是偏序关系呢好像离散数学学过的

SELECT l1.*, l2.*
FROM Employee l1 JOIN Employee l2 
WHERE l1.salary < l2.salary;

结果如下

再找次序的最大值，就一定是第二高的薪水。
max在没有元组输入时，还能返回NULL。

4.没看懂的方法。。

SELECT E1.`Salary`
FROM Employee AS E1
WHERE 1 = (
    SELECT COUNT(DISTINCT E2.`Salary`)
    FROM Employee AS E2
    WHERE E1.Salary < E2.Salary
);

先看一下这个代码

SELECT e1.`Salary`, COUNT(DISTINCT e2.salary)
FROM employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY e1.salary;

这个是干嘛呢，理解一下就是和上边的方法三类似构造偏序关系然后对e1.salary进行分组再对e2.salary去重后统计数量，得到的结果是什么呢?

其实得到的是Salary的排名。

其实很容易理解，构造偏序关系得到的是("小Salary", "大Salary"),对"小Salary"进行分组后对"大Salary"去重统计数量其实就是统计大于等于这个"小Salary"的数目。对于最大的值，数目显然只有一个，因为只有一个(最大值,最大值), 对于第二大的值，数目显然是两个(第二大值，第二大值)，(第二大值，最大值)。

因此还可以这样写

SELECT DISTINCT e1.`Salary`
FROM employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY e1.salary
HAVING COUNT(DISTINCT e2.salary) = 2;

其实这里把2可以任意修改就得到任意排名的值。

再返回去看之前的代码

SELECT E1.`Salary`
FROM Employee AS E1
WHERE 1 = (
    SELECT COUNT(DISTINCT E2.`Salary`)
    FROM Employee AS E2
    WHERE E1.Salary < E2.Salary
);

其实和后边补充的思想是一致的，只不过分别是用子查询和联结来实现，唯一的一点差别就是这里用了<而不是<=，因此这个代码等于1即可，但其选不出最大值。

与下面的代码是等价的

SELECT E1.`Salary` AS SecondHighestSalary
FROM Employee AS E1
WHERE 2 = (
    SELECT COUNT(DISTINCT E2.`Salary`)
    FROM Employee AS E2
    WHERE E1.Salary <= E2.Salary
);

提交了一下，居然还是报错。其实还是NULL的问题，可以在最开始取MAX()，也可以利用IFNULL函数。

SELECT MAX(E1.`Salary`) AS SecondHighestSalary
FROM Employee AS E1
WHERE 2 = (
    SELECT COUNT(DISTINCT E2.`Salary`)
    FROM Employee AS E2
    WHERE E1.Salary <= E2.Salary
);

5.利用窗口函数
这个好像是mysql8.0以上有的特性
挖个坑吧

完