簡介

經(jīng)常有小哥發(fā)出疑問，SQL還能這么寫？我經(jīng)常笑著回應(yīng)，SQL確實(shí)可以這么寫。其實(shí)SQL學(xué)起來簡單，用起來也簡單，但它還是能寫出很多變化，這些變化讀懂它不難，但要自己Get到這些變化，可能需要想一會或在網(wǎng)上找一會。

各種join

關(guān)于join的介紹，比較流行的就是這張圖了，如下：

• join：內(nèi)聯(lián)接，也可寫成inner join，取兩表關(guān)聯(lián)字段相交的那部分?jǐn)?shù)據(jù)。
• left join：左外聯(lián)接，也可寫成left outer join，取左表數(shù)據(jù)，若關(guān)聯(lián)不到右表，右表為空。
• right join：右外聯(lián)接，也可寫成right outer join，取右表數(shù)據(jù)，若關(guān)聯(lián)不到左表，左表為空。
• full join：全聯(lián)接，也可寫成full outer join，取左表和右表中所有數(shù)據(jù)。

但注意上圖，里面還有幾個Key is null的情況，它可以將兩表相交的那部分?jǐn)?shù)據(jù)排除掉！
也正是因?yàn)檫@個特性，一種很常見的SQL技巧是，用left join可替換not exists、not in等相關(guān)子查詢，如下：

select * from tableA A 
where not exists (select 1 from tableB B where B.Key=A.Key)

-- 使用left join的等價寫法
select * from tableA A 
left join tableB B on B.Key=A.Key where B.Key is null

也比較好理解，只有當(dāng)左表的數(shù)據(jù)在右表中不存在時，B.Key is null才成立。

查詢各類別最大的那條數(shù)據(jù)

比如在學(xué)籍管理系統(tǒng)中，有一類很常見的需求，查詢每學(xué)科分?jǐn)?shù)最高的那條數(shù)據(jù)，有如下幾種寫法：

select * from stu_score s 
where s.course_id in ('Maths','English') 
and s.score = (select max(score) from stu_score s1 where s1.course_id = s.course_id)

比較好理解，考分最高其實(shí)就是過濾出分?jǐn)?shù)等于最大分?jǐn)?shù)的記錄。

在不能使用子查詢的場景下，也可轉(zhuǎn)換成join，如下：

select * from stu_score s 
left join stu_score s1 on s1.course_id = s.course_id and s1.score > s.score
where s.course_id in ('Maths','English') and s1.id is null

這和前面用left join改寫not exists類似，通過s1.id is null過濾出left join關(guān)聯(lián)條件不滿足時的數(shù)據(jù)，什么情況left join關(guān)聯(lián)條件不滿足呢，當(dāng)s表記錄是分?jǐn)?shù)最大的那條記錄時，s1.score > s.score條件自然就不成立了，所以它過濾出來的數(shù)據(jù)，就是學(xué)科中分?jǐn)?shù)最大的那條記錄。

一直以來，我看到SQL的join的條件大都是a.field=b.field這種形式，導(dǎo)致我以為join只能寫等值條件，實(shí)際上，join條件和where中一樣，支持>、<、like、in甚至是exists子查詢等條件，大家也一定不要忽視了這一點(diǎn)。

上面場景還有一種寫法，就是使用group by先把各學(xué)科最大分算出來，然后再關(guān)聯(lián)出相應(yīng)數(shù)據(jù)，如下：

select * from
(select s.course_id,max(s.score) max_score stu_score s where s.course_id in ('Maths','English') group by s.course_id) sm
join stu_score s1 on s1.course_id = sm.course_id and s1.score=sm.max_score

查詢各類別top n數(shù)據(jù)

比如在學(xué)籍管理系統(tǒng)中，查詢每學(xué)科分?jǐn)?shù)前5的記錄，類似這種需求也很常見，比較簡單明了的寫法如下：

select * from stu_score s 
where s.course_id in ('Maths','English') 
and (select count(*) from stu_score s1 where s1.course_id = s.course_id and s1.score > s.score) < 5

很顯然，第5名只有4個學(xué)生比它分?jǐn)?shù)高，第4名只有3個學(xué)生比它分?jǐn)?shù)高，依此類推。

LATERAL join

MySQL8為join提供了一個新的語法LATERAL，使得被關(guān)聯(lián)表B在聯(lián)接前可以先根據(jù)關(guān)聯(lián)表A的字段過濾一下，然后再進(jìn)行關(guān)聯(lián)。

這個新的語法，可以非常簡單的解決上面top n的場景，如下：

select * from stu_course c 
join LATERAL (select * from stu_score s where c.course_id = s.course_id order by s.score desc limit 5) s1 on c.course_id = s1.course_id
where c.course_name in ('數(shù)學(xué)','英語')

如上，每個學(xué)科查詢出它的前5名記錄，然后再關(guān)聯(lián)起來。

統(tǒng)計多個數(shù)量

使用count(*)可以統(tǒng)計數(shù)量，但有些場景想統(tǒng)計多個數(shù)量，如統(tǒng)計1天內(nèi)單量、1周內(nèi)單量、1月內(nèi)單量。

用count(*)的話，需要掃描3次表，如下：

select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 DAY)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 WEEK)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

其實(shí)掃描一次表也可以實(shí)現(xiàn)，用sum來代替count即可，如下：

select sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 DAY)), 1, 0) day_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 WEEK)), 1, 0) week_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 MONTH)), 1, 0) month_order_cnt
from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

IF是mysql的邏輯判斷函數(shù)，當(dāng)其第一個參數(shù)為true時，返回第二個參數(shù)值，即1，否則返回第三個參數(shù)值0，然后再使用sum加起來，就是各條件為true的數(shù)量了。

數(shù)據(jù)對比

有時，我們需要對比兩個表的數(shù)據(jù)是否一致，最簡單的方法，就是在兩邊查詢出結(jié)果集，然后逐行逐字段對比。

但是這樣對比的效率比較低下，因?yàn)樗獌蓚€表的數(shù)據(jù)全都查出來，其實(shí)我們不一定非要都查出來，只要計算出一個hash值，然后對比hash值即可，如下：

select BIT_XOR(CRC32(CONCAT(ifnull(column1,''),ifnull(column2,'')))) as checksum 
from table_name where add_time > '2020-02-20' and add_time < '2020-02-21';  

先使用CONCAT將要對比的列連接起來，然后使用CRC32或MD5計算hash值，最后使用聚合函數(shù)BIT_XOR將多行hash值異或合并為一個hash值。

這個查詢最終只會返回1條hash值，查詢數(shù)據(jù)量大大減少了，數(shù)據(jù)對比效率就上去了。

總結(jié)

SQL看起來簡單，其實(shí)有很多細(xì)節(jié)與技巧，如果你也有其它技巧，歡迎留言分享討論??