這幾天在看Leetcode的時(shí)候逐步開始留意SQL題目，不做不知道，一做才感覺自己的SQL太弱了，因此將一道經(jīng)典題目：求第N高的薪水的解法進(jìn)行匯總（MySQL）。相關(guān)解法的原文鏈接已標(biāo)注在文末~

題目的鏈接為：第N高的薪水

一、題干

第N高的薪水：編寫一個(gè) SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）。

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

例如上述 Employee 表，n = 2 時(shí)，應(yīng)返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查詢應(yīng)返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

初始化代碼片段。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.

  );
END

二、前置知識(shí)碎片

簡(jiǎn)單總結(jié)，有時(shí)間再詳細(xì)補(bǔ)充知識(shí)點(diǎn)~

1. limit用法

limit子句用于限制查詢結(jié)果返回的數(shù)量。

用法：[select * from tableName limit i,n]

參數(shù)：

tableName : 為數(shù)據(jù)表；
i : 為查詢結(jié)果的索引值（默認(rèn)從0開始）；
n : 為查詢結(jié)果返回的數(shù)量

2. order by

order by為排序，ASC（默認(rèn)升序）和DESC`（降序）

適用于單列升序、單列降序和多列排序，示例：

SELECT * FROM Websites
ORDER BY alexa DESC;

教程鏈接

3. declare 和 set

[declare 字段名字段類型]

[set 賦值表達(dá)式]

4. if和ifnull

f(true,a,b), if(false,a,b) 這個(gè)就是第一個(gè)如果是true，就等于a，false就等于b，有點(diǎn)像三元表達(dá)式;

ifnull(a, b) ifnull里有兩個(gè)值，如果a不是null，則返回a，如果a=null，則返回b。

5. 窗口函數(shù)

實(shí)際上，在mysql8.0中有相關(guān)的內(nèi)置函數(shù)，而且考慮了各種排名問題：

row_number(): 同薪不同名，相當(dāng)于行號(hào)，例如3000、2000、2000、1000排名后為1、2、3、4
rank(): 同薪同名，有跳級(jí)，例如3000、2000、2000、1000排名后為1、2、2、4
dense_rank(): 同薪同名，無跳級(jí)，例如3000、2000、2000、1000排名后為1、2、2、3
ntile(): 分桶排名，即首先按桶的個(gè)數(shù)分出第一二三桶，然后各桶內(nèi)從1排名，實(shí)際不是很常用

顯然，本題是要用第三個(gè)函數(shù)。另外這三個(gè)函數(shù)必須要要與其搭檔over()配套使用，over()中的參數(shù)常見的有兩個(gè)，分別是

partition by，按某字段切分
order by，與常規(guī)order by用法一致，也區(qū)分ASC（默認(rèn)升序）和DESC（降序）

三、解法

1. 解法一：set賦值+distinct去重+limit取值的單表查詢

題目要求：

顯示沒有固定的N高薪水，N的確定由自定義函數(shù)傳入。

解題思路：

使用limit
limit start, count。其中start的顯示值是從start+1開始的。但limit后面輸入不能是計(jì)算式，比如：N-1
第N高的N，是通過自定義函數(shù)getNthHighestSalary的(N INT)中N傳入。start必須是從N-1開始，才能顯示符合題目要求的結(jié)果。比如第N=2高，如果直接用N值到limit，limit 2,1，意為從第3行開始，顯示一行。所以要用N-1=1，才能表示從第二行開始。
這時(shí)，應(yīng)通過一個(gè)替代參數(shù)實(shí)現(xiàn)。MySQL自定函數(shù)中的參數(shù)是靜態(tài)參數(shù)，即要先定義后使用。先用declare定義類型，后通過set進(jìn)行賦值。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m INT;
    set m=N-1; 
    RETURN (
        # Write your MySQL query statement below.
        select ifnull(
          (
            select distinct Salary 
            from Employee 
            order by Salary 
            desc limit m,1
          ),
          null
        )
    );
END

注意：此處的set，可以直接set N=N-1，而不用declare m，但聲明m會(huì)更加明晰一些~

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1; 
    RETURN (
        # Write your MySQL query statement below.
        select ifnull(
          (
            select distinct Salary 
            from Employee 
            order by Salary 
            desc limit N,1
          ),
          null
        )
    );
END

2. 解法二：set賦值+group by去重 + limit取值

主體思路同方法一，group by同樣可以起到分組去重的效果，用以代替distinct

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1; 
    RETURN (
        # Write your MySQL query statement below.
        select ifnull(
          (
            select Salary 
            from Employee 
            group by Salary
            order by Salary 
            desc limit N,1
          ),
          null
        )
    );
END

解法一和解法二最為簡(jiǎn)潔直觀，但僅適用于查詢?nèi)峙琶麊栴}，如果要求各分組的每個(gè)第N名，則該方法不適用；而且也不能處理存在重復(fù)值的情況。

3. 解法三：連續(xù)排名解法

計(jì)算出每一個(gè)薪水的連續(xù)排名
找到排名等于N的薪水，并輸出

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        # Write your MySQL query statement below.
        select min(a.salary)
        from employee a 
        where (
            select count(*)+1 
            from  (select distinct salary from employee) b 
            where b.salary>a.salary
        )=N
    );
END

4. 解法四：使用子查詢&笛卡爾積

排名第N的薪水意味著該表中存在N-1個(gè)比其更高的薪水
注意這里的N-1個(gè)更高的薪水是指去重后的N-1個(gè)，實(shí)際對(duì)應(yīng)人數(shù)可能不止N-1個(gè)
最后返回的薪水也應(yīng)該去重，因?yàn)榭赡懿恢挂粋€(gè)薪水排名第N
由于對(duì)于每個(gè)薪水的where條件都要執(zhí)行一遍子查詢，注定其效率低下

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        # Write your MySQL query statement below.
        SELECT DISTINCT e.salary
        FROM employee e
        WHERE (
            SELECT count(DISTINCT salary) 
            FROM employee 
            WHERE salary>e.salary
        ) = N-1
    );
END

當(dāng)然，可以很容易將上面的代碼改為笛卡爾積連接形式，其執(zhí)行過程實(shí)際上一致的，甚至MySQL執(zhí)行時(shí)可能會(huì)優(yōu)化成相同的查詢語句。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        # Write your MySQL query statement below.
        SELECT e1.salary
        FROM employee e1, employee e2 
        WHERE e1.salary <= e2.salary
        GROUP BY e1.salary
        HAVING count(DISTINCT e2.salary) = N
    );
END

5. 解法五：使用自連接

一般來說，能用子查詢解決的問題也能用連接解決。具體到本題：

兩表自連接，連接條件設(shè)定為表1的salary小于表2的salary
以表1的salary分組，統(tǒng)計(jì)表1中每個(gè)salary分組后對(duì)應(yīng)表2中salary唯一值個(gè)數(shù)，即去重
限定步驟2中having 計(jì)數(shù)個(gè)數(shù)為N-1，即實(shí)現(xiàn)了該分組中表1salary排名為第N個(gè)
考慮N=1的特殊情形(特殊是因?yàn)镹-1=0，計(jì)數(shù)要求為0)，此時(shí)不存在滿足條件的記錄數(shù)，但仍需返回結(jié)果，所以連接用left join
如果僅查詢薪水這一項(xiàng)值，那么不用left join當(dāng)然也是可以的，只需把連接條件放寬至小于等于、同時(shí)查詢個(gè)數(shù)設(shè)置為N即可。因?yàn)檫B接條件含等號(hào)，所以一定不為空，用join即可。
注：個(gè)人認(rèn)為無需考慮N<=0的情形，畢竟無實(shí)際意義。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        # Write your MySQL query statement below.
        SELECT e1.salary
        FROM  employee e1 JOIN employee e2 
        ON e1.salary <= e2.salary
        GROUP BY e1.salary
        HAVING count(DISTINCT e2.salary) = N
    );
END

但需要注意的是在題目測(cè)試時(shí)候，這種解法戶報(bào)錯(cuò)，因?yàn)槭亲赃B接兩個(gè)數(shù)比較。如果求第一個(gè)數(shù)，其中有一個(gè)必為空，會(huì)出錯(cuò) ~

6. 解法六：使用窗口函數(shù)

窗口函數(shù)用法具體見2.5

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT DISTINCT salary
        FROM (
            SELECT salary, dense_rank() over(ORDER BY salary DESC) AS rnk
            FROM employee
        ) tmp
        WHERE rnk = N
  );
END

四、總結(jié)

至此，可以總結(jié)MySQL查詢的一般性思路是：

能用單表優(yōu)先用單表，即便是需要用group by、order by、limit等，效率一般也比多表高
不能用單表時(shí)優(yōu)先用連接，連接是SQL中非常強(qiáng)大的用法，小表驅(qū)動(dòng)大表+建立合適索引+合理運(yùn)用連接條件，基本上連接可以解決絕大部分問題。但join級(jí)數(shù)不宜過多，畢竟是一個(gè)接近指數(shù)級(jí)增長(zhǎng)的關(guān)聯(lián)效果
能不用子查詢、笛卡爾積盡量不用，雖然很多情況下MySQL優(yōu)化器會(huì)將其優(yōu)化成連接方式的執(zhí)行過程，但效率仍然難以保證
如果MySQL版本允許，某些帶聚合功能的查詢需求應(yīng)用窗口函數(shù)是一個(gè)最優(yōu)選擇。除了經(jīng)典的獲取3種排名信息，還有聚合函數(shù)、向前向后取值、百分位等，具體可參考官方指南。以下是官方給出的幾個(gè)窗口函數(shù)的介紹：

五、參考鏈接

leetcode-cn.com/problems/nt…
leetcode-cn.com/problems/nt…
leetcode-cn.com/problems/nt…
leetcode-cn.com/problems/nt…

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

經(jīng)典SQL題目-求第N高的薪水的解法匯總及知識(shí)點(diǎn)復(fù)習(xí)

經(jīng)典SQL題目-求第N高的薪水的解法匯總及知識(shí)點(diǎn)復(fù)習(xí)

一、題干

二、前置知識(shí)碎片

1. limit用法

2. order by

3. declare 和 set

4. if和ifnull

5. 窗口函數(shù)

三、解法

1. 解法一：set賦值+distinct去重+limit取值的單表查詢

2. 解法二：set賦值+group by去重 + limit取值

3. 解法三：連續(xù)排名解法

4. 解法四：使用子查詢&笛卡爾積

5. 解法五：使用自連接

6. 解法六：使用窗口函數(shù)

四、總結(jié)

五、參考鏈接

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

經(jīng)典SQL題目-求第N高的薪水的解法匯總及知識(shí)點(diǎn)復(fù)習(xí)

一、題干

二、前置知識(shí)碎片

1. limit用法

2. order by

3. declare 和 set

4. if和ifnull

5. 窗口函數(shù)

三、解法

1. 解法一：set賦值+distinct去重+limit取值的單表查詢

2. 解法二：set賦值+group by去重 + limit取值

3. 解法三：連續(xù)排名解法

4. 解法四：使用子查詢&笛卡爾積

5. 解法五：使用自連接

6. 解法六：使用窗口函數(shù)

四、總結(jié)

五、參考鏈接

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

一、題干

二、前置知識(shí)碎片

三、解法

五、參考鏈接