一個SQL，6種寫法。
導讀
最近在刷LeetCode中數(shù)據(jù)庫題目時，有一道排名題目，用了6種寫法分別代表6種SQL思維來實現(xiàn)，想想也算是有趣。
題意理解不難，無非就是查找排名為N的記錄，但常用SQL的都知道這里存在一個歧義，即排名是否存在相同和是否跳級的問題。經(jīng)測試，這里的排名是"致密"排名（dense_rank），即同薪同名且不跳級那種。例如對于薪水3000/2000/2000/1000排名之后為1、2、2、3，若取N=3，則返回結(jié)果1000。另外，題目形式是一個自定義函數(shù)，但本質(zhì)仍是一個SQL查詢。
面對這樣的一道題，你能迅速想到幾種SQL寫法呢？
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 SET N = N - 1;
4 RETURN (
5 SELECT
6 salary
7 FROM
8 employee
9 GROUP BY
10 salary
11 ORDER BY
12 salary DESC
13 LIMIT 1 OFFSET N
14 );
15END
由于只進行單表查詢+單字段排序，對salary字段建立索引時查詢效率會非常高。
既然是排名為N，那么就意味著大于等于目標薪水的記錄數(shù)為N，更準確的說這里是去重后的記錄數(shù)為N。基于此想法，很快可以寫出相應SQL：
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e.salary
6 FROM
7 employee e
8 WHERE
9 (SELECT count(DISTINCT salary) FROM employee WHERE salary>=e.salary) = N
10 );
11END
個人認為，SQL最強大也最有代表性的操作在于多表關聯(lián)，這個問題自然也可以用連接查詢。MySQL中主要支持join、left join和right join三種連接方式。具體到這一題，可以選用任何一種。例如，如果限定連接條件是薪水大于等于（含等于），則可直接用join實現(xiàn)兩表自連接，然后對另一個計數(shù)即可；而如果限定連接條件是薪水大于（不含等于），則必須用left join，避免N取特殊值1時出現(xiàn)關聯(lián)結(jié)果為空而查詢失敗的情況。具體來說：
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1 JOIN employee e2 ON e1.salary <= e2.salary
8 GROUP BY
9 e1.salary
10 HAVING
11 count(DISTINCT e2.salary) = N
12 );
13END
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
8 GROUP BY
9 e1.salary
10 HAVING
11 count(DISTINCT e2.salary) = N-1
12 );
13END
用join連接方式實現(xiàn)的SQL，都能用笛卡爾積實現(xiàn)，且一般來說笛卡爾效率要略低于連接查詢，但很多情況下MySQL優(yōu)化器會將笛卡爾積形式的查詢優(yōu)化成join形式，此時二者執(zhí)行過程是一致的。可以很容易將解法3中的形式改成笛卡爾積形式的寫法。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1, employee e2
8 WHERE
9 e1.salary <= e2.salary
10 GROUP BY
11 e1.salary
12 HAVING
13 count(DISTINCT e2.salary) = N
14 );
15END
前面已經(jīng)介紹了4種解法，對比來看：解法2-4中都存在兩表關聯(lián)的問題，而解法1因為僅涉及到單表排序，所以效率相比之下更高；另一方面，解法2-4功能更具擴展性：例如可以很容易實現(xiàn)分組查詢排名第N高，而這是簡單的limit+offset寫法所不能實現(xiàn)的。那么，有沒有既能拓展到分組查詢、同時又具有單表查詢的高效呢？答案是肯定的，例如下面的自定義變量寫法，通過設定一個自變量，獲取每個薪水的排名信息，然后篩選排名為N的薪水即可。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT salary
6 FROM
7 (SELECT
8 salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
9 FROM
10 employee, (SELECT @r:=0, @p:=NULL)init
11 ORDER BY
12 salary DESC) tmp
13 WHERE rnk = N
14 );
15END
實際上，解法5中的自定義變量查詢寫法在MySQL8.0以后有相應的窗口函數(shù)可以實現(xiàn)。窗口函數(shù)在MySQL8.0版本首次引進，而其他很多SQL語言則早已內(nèi)置。具體而言，對于本題獲取"致密"排名的薪水，用到的窗口函數(shù)就是dense_rank()。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT salary
6 FROM
7 (SELECT
8 salary, dense_rank() over(ORDER BY salary DESC) AS rnk
9 FROM
10 employee) tmp
11 WHERE rnk = N
12 );
13END
以上用6種寫法實現(xiàn)同一需求，實際上這應該也代表了絕大多數(shù)寫SQL查詢的一般性思路：
能用單表優(yōu)先用單表，即便是需要用group by、order by、limit等，效率一般也比多表高
不能用單表時優(yōu)先用連接，連接是SQL中非常強大的用法，小表驅(qū)動大表+建立合適索引+合理運用連接條件，基本上連接可以解決絕大部分問題。但join級數(shù)不宜過多，畢竟是一個接近指數(shù)級增長的關聯(lián)效果
能不用子查詢、笛卡爾積盡量不用，雖然很多情況下MySQL優(yōu)化器會將其優(yōu)化成連接方式的執(zhí)行過程，但效率仍然難以保證
自定義變量在復雜SQL實現(xiàn)中會很有用，例如LeetCode中困難級別的數(shù)據(jù)庫題目很多都需要借助自定義變量實現(xiàn)
如果MySQL版本允許，窗口函數(shù)是一個最優(yōu)選擇，除了經(jīng)典的獲取3種排名信息，還有聚合函數(shù)、向前向后取值、百分位等，具體可參考官方指南（本號回復關鍵字"教程"提供網(wǎng)盤下載）
MySQL8.0內(nèi)置窗口函數(shù)
