原創：扣釘日記（微信公眾號ID：codelogs），歡迎分享，非公眾號轉載保留此聲明。
簡介
經常有小哥發出疑問，SQL還能這么寫？我經常笑著回應，SQL確實可以這么寫。其實SQL學起來簡單，用起來也簡單，但它還是能寫出很多變化，這些變化讀懂它不難，但要自己Get到這些變化，可能需要想一會或在網上找一會。
各種join
關于join的介紹，比較流行的就是這張圖了，如下：
簡單的解釋如下：
-
join：內聯接，也可寫成inner join，取兩表關聯字段相交的那部分數據。 -
left join：左外聯接，也可寫成left outer join，取左表數據，若關聯不到右表，右表為空。 -
right join：右外聯接，也可寫成right outer join，取右表數據，若關聯不到左表，左表為空。 -
full join：全聯接，也可寫成full outer join，取左表和右表中所有數據。
但注意上圖，里面還有幾個Key is null的情況，它可以將兩表相交的那部分數據排除掉！
也正是因為這個特性，一種很常見的SQL技巧是，用left join可替換not exists、not in等相關子查詢，如下：
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
也比較好理解，只有當左表的數據在右表中不存在時，B.Key is null才成立。
查詢各類別最大的那條數據
比如在學籍管理系統中，有一類很常見的需求，查詢每學科分數最高的那條數據，有如下幾種寫法：
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)
比較好理解，考分最高其實就是過濾出分數等于最大分數的記錄。
在不能使用子查詢的場景下，也可轉換成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關聯條件不滿足時的數據，什么情況left join關聯條件不滿足呢，當s表記錄是分數最大的那條記錄時，s1.score > s.score條件自然就不成立了，所以它過濾出來的數據，就是學科中分數最大的那條記錄。
一直以來，我看到SQL的join的條件大都是a.field=b.field這種形式，導致我以為join只能寫等值條件，實際上，join條件和where中一樣，支持>、<、like、in甚至是exists子查詢等條件，大家也一定不要忽視了這一點。
上面場景還有一種寫法，就是使用group by先把各學科最大分算出來，然后再關聯出相應數據，如下：
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數據
比如在學籍管理系統中，查詢每學科分數前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個學生比它分數高，第4名只有3個學生比它分數高，依此類推。
LATERAL join
MySQL8為join提供了一個新的語法LATERAL，使得被關聯表B在聯接前可以先根據關聯表A的字段過濾一下，然后再進行關聯。
這個新的語法，可以非常簡單的解決上面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 ('數學','英語')
如上，每個學科查詢出它的前5名記錄，然后再關聯起來。
統計多個數量
使用count(*)可以統計數量，但有些場景想統計多個數量，如統計1天內單量、1周內單量、1月內單量。
用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)
其實掃描一次表也可以實現，用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的邏輯判斷函數，當其第一個參數為true時，返回第二個參數值，即1，否則返回第三個參數值0，然后再使用sum加起來，就是各條件為true的數量了。
數據對比
有時，我們需要對比兩個表的數據是否一致，最簡單的方法，就是在兩邊查詢出結果集，然后逐行逐字段對比。
但是這樣對比的效率比較低下，因為它要兩個表的數據全都查出來，其實我們不一定非要都查出來，只要計算出一個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值，最后使用聚合函數BIT_XOR將多行hash值異或合并為一個hash值。
這個查詢最終只會返回1條hash值，查詢數據量大大減少了，數據對比效率就上去了。
總結
SQL看起來簡單，其實有很多細節與技巧，如果你也有其它技巧，歡迎留言分享討論
