Leetcode176.第二高的薪水（簡單）

題目
編寫一個 SQL 查詢污秆，獲取 Employee 表中第二高的薪水（Salary）劈猪。

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

例如上述 Employee 表，SQL查詢應(yīng)該返回 200 作為第二高的薪水良拼。如果不存在第二高的薪水战得，那么查詢應(yīng)返回 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.如果薪水只有一個呢這樣上述代碼是有問題的
因此要排除這種情況

改進(jìn)

利用IFNULL函數(shù) 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子句表示查詢結(jié)果返回前n條數(shù)據(jù)
offset m表示跳過m條語句
limit y offset x 分句表示查詢結(jié)果跳過 x 條數(shù)據(jù)杀捻，讀取前 y 條數(shù)據(jù)

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.(乍一看沒理解)利用偏序關(guān)系

自己構(gòu)造數(shù)據(jù)方便實踐

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;

什么是偏序關(guān)系呢好像離散數(shù)學(xué)學(xué)過的

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

結(jié)果如下

再找次序的最大值，就一定是第二高的薪水蚓庭。
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;

這個是干嘛呢墓拜，理解一下就是和上邊的方法三類似構(gòu)造偏序關(guān)系然后對e1.salary進(jìn)行分組再對e2.salary去重后統(tǒng)計數(shù)量港柜，得到的結(jié)果是什么呢?

其實得到的是Salary的排名。

其實很容易理解，構(gòu)造偏序關(guān)系得到的是("小Salary", "大Salary"),對"小Salary"進(jìn)行分組后對"大Salary"去重統(tǒng)計數(shù)量其實就是統(tǒng)計大于等于這個"小Salary"的數(shù)目夏醉。對于最大的值爽锥，數(shù)目顯然只有一個，因為只有一個(最大值,最大值), 對于第二大的值畔柔，數(shù)目顯然是兩個(第二大值氯夷，第二大值)，(第二大值靶擦，最大值)腮考。

因此還可以這樣寫

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
);

其實和后邊補充的思想是一致的玄捕，只不過分別是用子查詢和聯(lián)結(jié)來實現(xiàn)踩蔚，唯一的一點差別就是這里用了<而不是<=，因此這個代碼等于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函數(shù)柬姚。

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.利用窗口函數(shù)
這個好像是mysql8.0以上有的特性
挖個坑吧

完