對(duì)SQL語(yǔ)句不熟悉歡迎查看我整理的筆記：[SQL] MySQL基礎(chǔ) + python交互

轉(zhuǎn)載請(qǐng)注明：陳熹 chenx6542@foxmail.com （簡(jiǎn)書(shū)號(hào)：半為花間酒）
若公眾號(hào)內(nèi)轉(zhuǎn)載請(qǐng)聯(lián)系公眾號(hào)：早起Python

題目：

SQL架構(gòu)：

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

題解：

如果對(duì)子查詢不熟悉的伙伴可以品一品下面三種解法，建立子查詢的位置各不相同：

1. 第一種：SELECT中建立
2. 第二種：FROM中建立
3. 第三種：WHERE中建立

- 第一種解法

這題按照題意就是對(duì)薪水去重、降序排序后取第二個(gè)，可以有如下寫(xiě)法：

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

我比較喜歡用OFFSET，限制分頁(yè)代碼有兩種主流寫(xiě)法：

LIMIT X, Y
# 等價(jià)于
LIMIT Y OFFSET X

這個(gè)SQL語(yǔ)句已經(jīng)可以應(yīng)對(duì)多數(shù)場(chǎng)景，但忽略了一個(gè)特殊情況：

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

因此需要加一個(gè)IFNULL判斷，補(bǔ)充這個(gè)知識(shí)點(diǎn)

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;

實(shí)際上不用IFNULL也可，直接將第一步的代碼作為臨時(shí)表，選擇失敗就會(huì)返回NULL

但用IFNULL可以增強(qiáng)可讀性，代碼不僅是跟電腦交互，也是給人讀的

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

- 第二種解法

用到排名函數(shù)：DENSE_RANK
DENSE_RANK函數(shù)出現(xiàn)相同排名時(shí)，將不跳過(guò)相同排名號(hào)，rank值緊接上一次的rank值

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

利用排名行數(shù)就可能穩(wěn)妥選出第二高的薪水了，利用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時(shí)可能會(huì)返回多個(gè)結(jié)果。更重要的是，當(dāng)同樣是全部人薪水相同時(shí)，用salary是選行號(hào)為2會(huì)返回空值，但用MAX(salary)會(huì)由于匹配不到返回NULL，真正滿足特殊情況

- 第三種解法

只要記住利用MAX()這類(lèi)函數(shù)無(wú)需特別考慮NULL后，還有一種比較直接的思路：

比最大值小的所有數(shù)據(jù)中，最大的就是第二大

在WHERE里建立子查詢

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