SQL CASE 表達(dá)式的具體使用
CASE 表達(dá)式分為簡(jiǎn)單表達(dá)式與搜索表達(dá)式,其中搜索表達(dá)式可以覆蓋簡(jiǎn)單表達(dá)式的全部能力,我也建議只寫搜索表達(dá)式,而不要寫簡(jiǎn)單表達(dá)式。
簡(jiǎn)單表達(dá)式:
SELECT CASE city WHEN '北京' THEN 1 WHEN '天津' THEN 2 ELSE 0 END AS abc FROM test
搜索表達(dá)式:
SELECT CASE WHEN city = '北京' THEN 1 WHEN city = '天津' THEN 2 ELSE 0 END AS abc FROM test
明顯可以看出,簡(jiǎn)單表達(dá)式只是搜索表達(dá)式 a = b
的特例,因?yàn)闊o(wú)法書寫任何符號(hào),只要條件換成 a > b
就無(wú)法勝任了,而搜索表達(dá)式不但可以輕松勝任,甚至可以寫聚合函數(shù)。
CASE 表達(dá)式里的聚合函數(shù)
為什么 CASE 表達(dá)式里可以寫聚合函數(shù)?
因?yàn)楸旧肀磉_(dá)式就支持聚合函數(shù),比如下面的語(yǔ)法,我們不會(huì)覺(jué)得奇怪:
SELECT sum(pv), avg(uv) from test
本身 SQL 就支持多種不同的聚合方式同時(shí)計(jì)算,所以將其用在 CASE 表達(dá)式里,也是順其自然的:
SELECT CASE WHEN count(city) = 100 THEN 1 WHEN sum(dau) > 200 THEN 2 ELSE 0 END AS abc FROM test
只要 SQL 表達(dá)式中存在聚合函數(shù),那么整個(gè)表達(dá)式都聚合了,此時(shí)訪問(wèn)非聚合變量沒(méi)有任何意義。所以上面的例子,即便在 CASE 表達(dá)式中使用了聚合,其實(shí)也不過(guò)是聚合了一次后,按照條件進(jìn)行判斷罷了。
這個(gè)特性可以解決很多實(shí)際問(wèn)題,比如將一些復(fù)雜聚合判斷條件的結(jié)果用 SQL 結(jié)構(gòu)輸出,那么很可能是下面這種寫法:
SELECT CASE WHEN 聚合函數(shù)(字段) 符合什么條件 THEN xxx ... 可能有 N 個(gè) ELSE NULL END AS abc FROM test
這也可以認(rèn)為是一種行轉(zhuǎn)列的過(guò)程,即 把行聚合后的結(jié)果通過(guò)一條條 CASE 表達(dá)式形成一個(gè)個(gè)新的列。
聚合與非聚合不能混用
我們希望利用 CASE 表達(dá)式找出那些 pv 大于平均值的行,以下這種想當(dāng)然的寫法是錯(cuò)誤的:
SELECT CASE WHEN pv > avg(pv) THEN 'yes' ELSE 'no' END AS abc FROM test
原因是,只要 SQL 中存在聚合表達(dá)式,那么整條 SQL 就都是聚合的,所以返回的結(jié)果只有一條,而我們期望查詢結(jié)果不聚合,只是判斷條件用到了聚合結(jié)果,那么就要使用子查詢。
為什么子查詢可以解決問(wèn)題?因?yàn)樽硬樵兊木酆习l(fā)生在子查詢,而不影響當(dāng)前父查詢,理解了這一點(diǎn),就知道為什么下面的寫法才是正確的了:
SELECT CASE WHEN pv > ( SELECT avg(pv) from test ) THEN 'yes' ELSE 'no' END AS abc FROM test
這個(gè)例子也說(shuō)明了 CASE 表達(dá)式里可以使用子查詢,因?yàn)樽硬樵兪窍扔?jì)算的,所以查詢結(jié)果在哪兒都能用,CASE 表達(dá)式也不例外。
WHERE 中的 CASE
WHERE 后面也可以跟 CASE 表達(dá)式的,用來(lái)做一些需要特殊枚舉處理的篩選。
比如下面的例子:
SELECT * FROM demo WHERE CASE WHEN city = '北京' THEN true ELSE ID > 5 END
本來(lái)我們要查詢 ID 大于 5 的數(shù)據(jù),但我想對(duì)北京這個(gè)城市特別對(duì)待,那么就可以在判斷條件中再進(jìn)行 CASE 分支判斷。
這個(gè)場(chǎng)景在 BI 工具里等價(jià)于,創(chuàng)建一個(gè) CASE 表達(dá)式字段,可以拖入篩選條件生效。
GROUP BY 中的 CASE
想不到吧,GROUP BY 里都可以寫 CASE 表達(dá)式:
SELECT isPower, sum(gdp) FROM test GROUP BY CASE WHEN isPower = 1 THEN city, area ELSE city END
上面例子表示,計(jì)算 GDP 時(shí),對(duì)于非常發(fā)達(dá)的城市,按照每個(gè)區(qū)粒度查看聚合結(jié)果,也就是看的粒度更細(xì)一些,而對(duì)于欠發(fā)達(dá)地區(qū),本身 gdp 也不高,直接按照城市粒度看聚合結(jié)果。
這樣,就按照不同的條件對(duì)數(shù)據(jù)進(jìn)行了分組聚合。由于返回行結(jié)果是混在一起的,像這個(gè)例子,可以根據(jù) isPower 字段是否為 1 判斷,是否按照城市、區(qū)域進(jìn)行了聚合,如果沒(méi)有其他更顯著的標(biāo)識(shí),可能導(dǎo)致無(wú)法區(qū)分不同行的聚合粒度,因此謹(jǐn)慎使用。
ORDER BY 中的 CASE
同樣,ORDER BY 使用 CASE 表達(dá)式,會(huì)將排序結(jié)果按照 CASE 分類進(jìn)行分組,每組按照自己的規(guī)則排序,比如:
SELECT * FROM test ORDER BY CASE WHEN isPower = 1 THEN gdp ELSE people END
上面的例子,對(duì)發(fā)達(dá)地區(qū)采用 gdp 排序,否則采用人口數(shù)量排序。
總結(jié)
CASE 表達(dá)式總結(jié)一下有如下特點(diǎn):
- 支持簡(jiǎn)單與搜索兩種寫法,推薦搜索寫法。
- 支持聚合與子查詢,需要注意不同情況的特點(diǎn)。
- 可以寫在 SQL 查詢的幾乎任何地方,只要是可以寫字段的地方,基本上就可以替換為 CASE 表達(dá)式。
- 除了 SELECT 外,CASE 表達(dá)式還廣泛應(yīng)用在 INSERT 與 UPDATE,其中 UPDATE 的妙用是不用將 SQL 拆分為多條,所以不用擔(dān)心數(shù)據(jù)變更后對(duì)判斷條件的二次影響。
到此這篇關(guān)于SQL CASE 表達(dá)式的具體使用的文章就介紹到這了,更多相關(guān)SQL CASE 表達(dá)式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
目前用到的兩個(gè)分頁(yè)存儲(chǔ)過(guò)程代碼
前用到的兩個(gè)分頁(yè)存儲(chǔ)過(guò)程,需要的朋友可以參考下。2009-09-09idea連接SQL?Server數(shù)據(jù)庫(kù)的詳細(xì)圖文教程
Idea的還有個(gè)強(qiáng)大之處就是連接數(shù)據(jù)庫(kù),就可以少開一個(gè)數(shù)據(jù)庫(kù)工具了,下面這篇文章主要給大家介紹了關(guān)于idea連接SQL?Server數(shù)據(jù)庫(kù)的詳細(xì)圖文教程,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-12-12查詢表中某字段有重復(fù)記錄個(gè)數(shù)的方法
本篇文章介紹了,查詢表中某字段有重復(fù)記錄個(gè)數(shù)的方法。需要的朋友參考下2013-04-04SQLServer 2000 升級(jí)到 SQLServer 2008 性能之需要注意的地方之一
今天在 相同環(huán)境測(cè)試 2000 和 2008 性能 讓我意外的是 2008 明顯比2000 慢很多,因?yàn)椴荒芎?jiǎn)單的升級(jí),sql語(yǔ)句也需要優(yōu)化2012-02-02使用SQL實(shí)現(xiàn)車流量的計(jì)算的示例代碼
本文主要介紹了使用SQL實(shí)現(xiàn)車流量的計(jì)算的示例代碼,文中通過(guò)示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-02-02向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)并返回當(dāng)前插入的行數(shù)及全局變量@@IDENTITY應(yīng)用
向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)并返回當(dāng)前插入的的行數(shù),這項(xiàng)功能很實(shí)用的可以判斷是否為批量插入主要是利用全局變量@@IDENTITY實(shí)現(xiàn),感興趣的朋友可以了解下啊,希望本文對(duì)你鞏固sql知識(shí)很有幫助的2013-01-01SQL語(yǔ)句實(shí)例說(shuō)明 方便學(xué)習(xí)mysql的朋友
我是在MySQL數(shù)據(jù)庫(kù)中做的測(cè)試,不同的數(shù)據(jù)庫(kù)有一定的差別方便學(xué)習(xí)mysql的朋友2012-09-09