本文主要介紹博主在以往開發程序中，對于不同業務所對應的 sql 寫法進行歸納總結而來，進而分享給大家，

• 本文所講述 sql 語法都是基于 MySql 8.0
• 博主github地址：http://github.com/wayn111  歡迎大家關注，點個star

一、ORDER BY FIELD() 自定義排序邏輯

MySql 中的排序 ORDER BY 除了可以用 ASC 和 DESC，還可以通過ORDER BY FIELD(str,str1,...)自定義字串/數字來實作排序，這里用 order_diy 表舉例，結構以及表資料展示：

ORDER BY FIELD(str,str1,...) 自定義排序sql如下：

SELECT * from order_diy ORDER BY FIELD(title,'九陰真經', 
'降龍十八掌','九陰白骨爪','雙手互博','桃花島主',
'全真內功心法','蛤蟆功','銷魂掌','靈白山少主');

查詢結果如下：

如上，我們設定自定義排序欄位為 title 欄位，然后將我們自定義的排序結果跟在 title 后面，

二、CASE 運算式

case when then else end運算式功能非常強大可以幫助我們解決 if elseif else 這種問題，這里繼續用 order_diy 表舉例，假如我們想在 order_diy 表加一列 level 列，根據money 判斷大于60就是高級，大于30就是中級，其余顯示低級，sql 如下：

SELECT *, 
case when money > 60 then '高級' 
when money > 30 then '中級' 
else '低級' END level 
from order_diy;

查詢結果：

三、EXISTS 用法

我猜大家在日常開發中，應該都對關鍵詞 exists 用的比較少，估計使用 in 查詢偏多，這里給大家介紹一下 exists 用法，參考官網檔案:

可知 exists 后面是跟著一個子查詢陳述句，它的作用是根據主查詢的資料，每一行都放到子查詢中做條件驗證，根據驗證結果（TRUE 或者 FALSE），TRUE的話該行資料就會保留，下面用 emp 表和 dept 表進行舉例，表結構以及資料展示：

計入我們現在想找到 emp 表中 dept_name 與 dept表 中 dept_name 對應不上員工資料，sql 如下：

SELECT * from emp e where exists (
SELECT * from dept p where e.dept_id = p.dept_id 
and e.dept_name != p.dept_name
)

查詢結果：

我們通過 exists 語法將外層 emp 表全部資料 放到子查詢中與一一與 dept 表全部資料進行比較，只要有一行記錄回傳true，畫個圖展示主查詢所有記錄與子查詢互動如下：

• 第一條記錄與子查詢比較時，全部回傳 false，所以第一行不展示，
• 第二行記錄與子查詢比較時，發現 銷售部門 與 dept 表第二行 銷售部 對應不上，回傳 true，所以主查詢該行記錄會回傳，
• 第二行以后記錄執行結果同第一條，

四、GROUP_CONCAT(expr) 組連接函式

GROUP_CONCAT(expr) 組連接函式可以回傳分組后指定欄位的字串連接形式，并且可以指定排序邏輯，以及連接字串，默認為英文逗號連接，這里繼續用 order_diy 表舉例：sql 如下：

SELECT name, GROUP_CONCAT(title ORDER BY id desc  SEPARATOR '-') 
from order_diy GROUP BY name ORDER BY NULL;

查詢結果：

如上我們通過 GROUP_CONCAT(title ORDER BY id desc SEPARATOR '-') 陳述句，指定分組連接 title 欄位并按照 id 排序，設定連接字串為 -，

五、自連接查詢

自連接查詢是 sql 語法里常用的一種寫法，掌握了自連接的用法我們可以在 sql 層面輕松解決很多問題，這里用 tree 表舉例，結構以及表資料展示：

tree 表中通過 pid 欄位與 id 欄位進行父子關聯，假如現在有一個需求，我們想按照父子層級將 tree 表資料轉換成 一級職位 二級職位 三級職位 三個列名進行展示，sql 如下：

SELECT t1.job_name '一級職位', t2.job_name '二級職位', t3.job_name '三級職位' 
from tree t1 join tree t2 on t1.id = t2.pid left join tree t3 on t2.id = t3.pid 
where t1.pid = 0;

結果如下：

我們通過 tree t1 join tree t2 on t1.id = t2.pid 自連接展示 一級職位 二級職位，再用 left join tree t3 on t2.id = t3.pid 自連接展示 二級職位 三級職位，最后通過where 條件 t1.pid = 0過濾掉非一級職位的展示，完成這個需求，

六、更新 emp 表和 dept 表關聯資料

這里繼續使用上文提到的 emp 表和 dept 表，資料如下：

可以看到上述 emp 表中 jack 的部門名稱與 dept 表實際不符合，現在我們想將 jack 的部門名稱更新成 dept 表的正確資料，sql 如下：

update emp, dept set emp.dept_name = dept.dept_name
where emp.dept_id = dept.dept_id;

查詢結果：

我們可以直接關聯 emp 表和 dept 表并設定關聯條件，然后更新 emp 表的 dept_name 為 dept 表的 dept_name，

七、ORDER BY 空值 NULL 排序

ORDER BY 字句中可以跟我們要排序的欄位名稱，但是當欄位中存在 null 值時，會對我們的排序結果造成影響，我們可以通過 ORDER BY IF(ISNULL(title), 1, 0) 語法將 null 值轉換成0或1，來達到將 null 值放到前面還是后面進行排序的效果，

SELECT * FROM test_rollup ORDER BY  IF(ISNULL(title), 0, 1), money;

查詢結果：

八、with rollup 分組統計資料的基礎上再進行統計匯總

MySql 中可以使用 with rollup 在分組統計資料的基礎上再進行統計匯總，即用來得到 group by 的匯總資訊，這里繼續用order_diy 表舉例，sql 如下：

SELECT name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

查詢結果：

可以看到通過 GROUP BY name WITH ROLLUP 陳述句，查詢結果最后一列顯示了分組統計的匯總結果，但是 name 欄位最后顯示為 null，我們可以通過 coalesce() 比較函式，回傳第一個非空引數，

SELECT coalesce(name, '總金額') name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

查詢結果：

九、with as 提取臨時表別名

with as 語法需要 MySql 8.0以上版本，它的作用主要是提取子查詢，方便后續共用，更多情況下會用在資料分析的場景上，

如果一整句查詢中多個子查詢都需要使用同一個子查詢的結果，那么就可以用with as，將共用的子查詢提取出來，加個別名，后面查詢陳述句可以直接用，對于大量復雜的SQL陳述句起到了很好的優化作用，這里繼續用 order_diy 表舉例，這里使用with as給出sql 如下：

-- 使用 with as
with t1 as (SELECT * from order_diy where money > 30),
t2 as (SELECT * from order_diy where money > 60)
SELECT * from t1 
where t1.id not in (SELECT id from  t2) and t1.name = '周伯通';

查詢結果：

這個 sql 查詢了 order_diy 表中 money 大于30且小于等于60之間并且 name 是周伯通的記錄，

10、存在就更新，不存在就插入

MySql 中通過on duplicate key update語法來實作存在就更新，不存在就插入的邏輯，插入或者更新時，它會根據表中主鍵索引或者唯一索引進行判斷，如果主鍵索引或者唯一索引有沖突，就會執行on duplicate key update后面的賦值陳述句，
這里通過 news 表舉例，表結構和說資料展示，其中 news_code 欄位有唯一索引：

添加sql：

-- 第一次執行添加陳述句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新聞3', '小花', 'wx-0003') 
on duplicate key update news_title = '新聞3';
-- 第二次執行修改陳述句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新聞4', '小花', 'wx-0003') 
on duplicate key update news_title = '新聞4';

結果如下：

總結

到這里，本文所分享的10個高級sql寫法就全部介紹完了，希望對大家日常開發 sql 撰寫有所幫助，喜歡的朋友們可以點贊加關注??，

標籤：其他

上一篇：Flink模式

下一篇：對比分析數倉中行列存的特性