SQL進(jìn)階一整個(gè)是根據(jù)我看了pdf版本的整理以及自己的見解整理。后期也方便我自己查看和復(fù)習(xí)。

CASE 表達(dá)式

CASE 表達(dá)式是從 SQL-92 標(biāo)準(zhǔn)開始被引入的。可能因?yàn)樗窍鄬?duì)較新的技術(shù)，所以盡管使用起來非常便利，但其真正的價(jià)值卻并不怎么為人所知。很多人不用它，或者用它的簡(jiǎn)略版函數(shù)，例如 DECODE（Oracle）、IF （MySQL）等。然而，正如 Joe Celko 所說，CASE表達(dá)式也許是 SQL-92 標(biāo)準(zhǔn)里加入的最有用的特性。如果能用好它，那么 SQL 能解決的問題就會(huì)更廣泛，寫法也會(huì)更加漂亮。而且，因?yàn)?CASE 表達(dá)式是不依賴于具體數(shù)據(jù)庫的技術(shù)，所以可以提高 SQL 代碼的可移植性。這里強(qiáng)烈推薦大家改用 CASE 表達(dá)式，特別是使用DECODE 函數(shù)的 Oracle 用戶 。

正對(duì)decode，我們和case進(jìn)行下對(duì)比，然后引出我們的主角case。

DECODE 是 Oracle 用戶很熟悉的函數(shù)，它有以下四個(gè)不如 CASE 表達(dá)式的地方。
? 它是 Oracle 獨(dú)有的函數(shù)，所以不具有可移植性。
? 分支數(shù)最大支持 127 個(gè)（參數(shù)上限 255 個(gè)，一個(gè)分支需要 2 個(gè)參數(shù)）。
? 如果分支數(shù)增加，代碼會(huì)變得非常難讀。
? 表達(dá)能力較弱。具體來說，參數(shù)里不能使用謂詞，也不能嵌套子查詢。

ok，現(xiàn)在用一些案例去了解學(xué)習(xí)下優(yōu)點(diǎn)眾多的case用法：

#CASE 表達(dá)式概述

首先我們來學(xué)習(xí)一下基本的寫法，CASE 表達(dá)式有簡(jiǎn)單 CASE 表達(dá)式（simple case expression）和搜索 CASE 表達(dá)式（searched caseexpression）兩種寫法，它們分別如下所示。

■CASE 表達(dá)式的寫法

-- 簡(jiǎn)單CASE 表達(dá)式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- 搜索CASE 表達(dá)式
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

這兩種寫法的執(zhí)行結(jié)果是相同的，“sex”列（字段）如果是 '1' ，那么結(jié)果為男；如果是 '2' ，那么結(jié)果為女。簡(jiǎn)單 CASE 表達(dá)式正如其名，寫法簡(jiǎn)單，但能實(shí)現(xiàn)的事情比較有限。簡(jiǎn)單 CASE 表達(dá)式能寫的條件，搜索 CASE 表達(dá)式也能寫，所以后面采用搜索 CASE 表達(dá)式的寫法。

我們?cè)诰帉?SQL 語句的時(shí)候需要注意，在發(fā)現(xiàn)為真的 WHEN 子句時(shí)，CASE 表達(dá)式的真假值判斷就會(huì)中止，而剩余的 WHEN 子句會(huì)被忽略。為了避免引起不必要的混亂，使用 WHEN 子句時(shí)要注意條件的排他性。

■剩余的　WHEN 子句被忽略的寫法示例

-- 例如，這樣寫的話，結(jié)果里不會(huì)出現(xiàn)“第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

注意事項(xiàng) 1：統(tǒng)一各分支返回的數(shù)據(jù)類型
雖然這一點(diǎn)無需多言，但這里還是要強(qiáng)調(diào)一下：一定要注意 CASE 表達(dá)式里各個(gè)分支返回的數(shù)據(jù)類型是否一致。某個(gè)分支返回字符型，而其他分支返回?cái)?shù)值型的寫法是不正確的。
注意事項(xiàng) 2：不要忘了寫 END
使用 CASE 表達(dá)式的時(shí)候，最容易出現(xiàn)的語法錯(cuò)誤是忘記寫 END 。雖然忘記寫時(shí)程序會(huì)返回比較容易理解的錯(cuò)誤消息，不算多么致命的錯(cuò)誤。但是，感覺自己寫得沒問題，而執(zhí)行時(shí)卻出錯(cuò)的情況大多是由這個(gè)原因引起的，所以請(qǐng)一定注意一下。

注意事項(xiàng) 3：養(yǎng)成寫 ELSE 子句的習(xí)慣
與 END 不同，ELSE 子句是可選的，不寫也不會(huì)出錯(cuò)。不寫 ELSE 子句時(shí)，CASE 表達(dá)式的執(zhí)行結(jié)果是 NULL 。但是不寫可能會(huì)造成“語法沒有錯(cuò)誤，結(jié)果卻不對(duì)”這種不易追查原因的麻煩，所以最好明確地寫上 ELSE 子句（即便是在結(jié)果可以為 NULL 的情況下）。養(yǎng)成這樣的習(xí)慣后，我們從代碼上就可以清楚地看到這種條件下會(huì)生成 NULL，而且將來代碼有修改時(shí)也能減少失誤。

#將已有編號(hào)方式轉(zhuǎn)換為新的方式并統(tǒng)計(jì)

在進(jìn)行非定制化統(tǒng)計(jì)時(shí)，我們經(jīng)常會(huì)遇到將已有編號(hào)方式轉(zhuǎn)換為另外一種便于分析的方式并進(jìn)行統(tǒng)計(jì)的需求。例如，現(xiàn)在有一張按照“‘1：北海道’、‘2：青森’、……、‘47：沖繩’”這種編號(hào)方式來統(tǒng)計(jì)都道府縣 人口的表，我們需要以東北、關(guān)東、九州等地區(qū)為單位來分組，并統(tǒng)計(jì)人口數(shù)量。具體來說，就是統(tǒng)計(jì)下表 PopTbl 中的內(nèi)容，得出如右表“統(tǒng)計(jì)結(jié)果”所示的結(jié)果。

在“統(tǒng)計(jì)結(jié)果”這張表中，“四國”對(duì)應(yīng)的是表 PopTbl 中的“德島、香川、愛媛、高知 ”，“九 州”對(duì)應(yīng)的是表 PopTbl 中的“福岡、佐賀、長崎”。

大家會(huì)怎么實(shí)現(xiàn)呢？定義一個(gè)包含“地區(qū)編號(hào)”列的視圖是一種做法，但是這樣一來，需要添加的列的數(shù)量將等同于統(tǒng)計(jì)對(duì)象的編號(hào)個(gè)數(shù)，而且很難動(dòng)態(tài)地修改。

而如果使用 CASE 表達(dá)式，則用如下所示的一條 SQL 語句就可以完成。為了便于理解，這里用縣名（pref_name ）代替編號(hào)作為GROUP BY 的列。

-- 把縣編號(hào)轉(zhuǎn)換成地區(qū)編號(hào)(1)
SELECT CASE pref_name
WHEN '德島' THEN '四國'
WHEN '香川' THEN '四國'
WHEN '愛媛' THEN '四國'
WHEN '高知' THEN '四國'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '德島' THEN '四國'
WHEN '香川' THEN '四國'
WHEN '愛媛' THEN '四國'
WHEN '高知' THEN '四國'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE '其他' END;

這里的關(guān)鍵在于將 SELECT 子句里的 CASE 表達(dá)式復(fù)制到 GROUP BY子句里。需要注意的是，如果對(duì)轉(zhuǎn)換前的列“pref_name ”進(jìn)行 GROUP BY ，就得不到正確的結(jié)果（因?yàn)檫@并不會(huì)引起語法錯(cuò)誤，所以容易被忽視）。

同樣地，也可以將數(shù)值按照適當(dāng)?shù)募?jí)別進(jìn)行分類統(tǒng)計(jì)。例如，要按人口數(shù)量等級(jí)（pop_class ）查詢都道府縣個(gè)數(shù)的時(shí)候，就可以像下面這樣寫 SQL 語句。

-- 按人口數(shù)量等級(jí)劃分都道府縣
SELECT CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END;

pop_class cnt
--------- ----
01     1
02     3
03     3
04     2

這個(gè)技巧非常好用。不過，必須在 SELECT 子句和 GROUP BY 子句這兩處寫一樣的 CASE 表達(dá)式，這有點(diǎn)兒麻煩。后期需要修改的時(shí)候，很容易發(fā)生只改了這一處而忘掉改另一處的失誤。

所以，如果我們可以像下面這樣寫，那就方便多了。

-- 把縣編號(hào)轉(zhuǎn)換成地區(qū)編號(hào)(2) ：將CASE 表達(dá)式歸納到一處
SELECT CASE pref_name
WHEN '德島' THEN '四國'
WHEN '香川' THEN '四國'
WHEN '愛媛' THEN '四國'
WHEN '高知' THEN '四國'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district; ←-------GROUP BY 子句里引用了SELECT 子句中定義的別名

但是沒錯(cuò)，這里的 GROUP BY 子句使用的正是 SELECT 子句里定義的列的別稱——district 。但是嚴(yán)格來說，這種寫法是違反標(biāo)準(zhǔn) SQL 的規(guī)則的。因?yàn)?GROUP BY 子句比 SELECT 語句先執(zhí)行，所以在 GROUP BY 子句中引用在 SELECT 子句里定義的別稱是不被允許的。事實(shí)上，在 Oracle、DB2、SQL Server 等數(shù)據(jù)庫里采用這種寫法時(shí)就會(huì)出錯(cuò)。

不過也有支持這種 SQL 語句的數(shù)據(jù)庫，例如在 PostgreSQL 和MySQL 中，這個(gè)查詢語句就可以順利執(zhí)行。這是因?yàn)椋@些數(shù)據(jù)庫在執(zhí)行查詢語句時(shí)，會(huì)先對(duì) SELECT 子句里的列表進(jìn)行掃描，并對(duì)列進(jìn)行計(jì)算。不過因?yàn)檫@是違反標(biāo)準(zhǔn)的寫法，所以這里不強(qiáng)烈推薦大家使用。但是，這樣寫出來的 SQL 語句確實(shí)非常簡(jiǎn)潔，而且可讀性也很好。

■用一條 SQL 語句進(jìn)行不同條件的統(tǒng)計(jì)

進(jìn)行不同條件的統(tǒng)計(jì)是 CASE 表達(dá)式的著名用法之一。例如，我們需要往存儲(chǔ)各縣人口數(shù)量的表 PopTbl 里添加上“性別”列，然后求按性別、縣名匯總的人數(shù)。具體來說，就是統(tǒng)計(jì)表 PopTbl2 中的數(shù)據(jù)，然后求出如表“統(tǒng)計(jì)結(jié)果”所示的結(jié)果。圖片沒有截取完全。

通常的做法是像下面這樣，通過在 WHERE 子句里分別寫上不同的條件，然后執(zhí)行兩條 SQL 語句來查詢。

■示例代碼 3

-- 男性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name;

最后需要通過宿主語言或者應(yīng)用程序?qū)⒉樵兘Y(jié)果按列展開。如果使用UNION ，只用一條 SQL 語句就可以實(shí)現(xiàn)，但使用這種做法時(shí)，工作量并沒有減少，SQL 語句也會(huì)變得很長。而如果使用 CASE 表達(dá)式，下面這一條簡(jiǎn)單的 SQL 語句就可以搞定。

SELECT pref_name,
-- 男性人口
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
-- 女性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;

上面這段代碼所做的是，分別統(tǒng)計(jì)每個(gè)縣的“男性”（即 '1' ）人數(shù)和“女性”（即 '2' ）人數(shù)。也就是說，這里是將“行結(jié)構(gòu)”的數(shù)據(jù)轉(zhuǎn)換成了“列結(jié)構(gòu)”的數(shù)據(jù)。除了 SUM ，COUNT 、AVG 等聚合函數(shù)也都可以用于將行結(jié)構(gòu)的數(shù)據(jù)轉(zhuǎn)換成列結(jié)構(gòu)的數(shù)據(jù)。

這個(gè)技巧可貴的地方在于，它能將 SQL 的查詢結(jié)果轉(zhuǎn)換為二維表的格式。如果只是簡(jiǎn)單地用 GROUP BY 進(jìn)行聚合，那么查詢后必須通過宿主語言或者 Excel 等應(yīng)用程序?qū)⒔Y(jié)果的格式轉(zhuǎn)換一下，才能使之成為交叉表。看上面的執(zhí)行結(jié)果會(huì)發(fā)現(xiàn)，此時(shí)輸出的已經(jīng)是側(cè)欄為縣名、表頭為性別的交叉表了。在制作統(tǒng)計(jì)表時(shí)，這個(gè)功能非常方便。如果用一句話來形容這個(gè)技巧，可以這樣說：

新手用 WHERE 子句進(jìn)行條件分支，高手用 SELECT 子句進(jìn)行條件分支。

總結(jié)

以上是生活随笔為你收集整理的SQL进阶随笔--case用法（一）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。