技術文章 » 分享6個SQL小技巧

分享6個SQL小技巧

2023-06-18 06:01:28

原創：扣釘日記（微信公眾號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看起來簡單，其實有很多細節與技巧，如果你也有其它技巧，歡迎留言分享討論