Case表達式

在發現結果為真的WHEN子句時,CASE表達式的真假值判斷會終止,剩餘的WHEN子句會被忽略:

CASE WHEN col_1 IN ('a', 'b') THEN '第一'
 WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

註意:

1. 統一各分支返回的數據類型.
2. 記得寫end.
3. 寫else子句的習慣,否則執行結果預設處理為null.

案例1

將已有編號方式轉換為新的方式並統計

下麵給出的group by引用select中定義的別名,case寫在group by違反SQL規則(GROUP BY 子句比 SELECT 語句先執行):

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;

案例2

用一條 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;

總結:聚合函數可以實現行轉列,實現select的條件分支(代替where)

案例3

用 CHECK 約束定義多個列的條件關係

題目:女性員工工資必須在20w以上(蘊含式（conditional）的邏輯表達式，記作 P → Q)

CONSTRAINT check_salary CHECK
 ( CASE WHEN sex = '2'
 THEN CASE WHEN salary > 200000
 THEN 1 ELSE 0 END
 ELSE 1 END = 1 )

案例4

在 UPDATE 語句里進行條件分支

要求

1. 對當前工資為 30 萬以上的員工，降薪 10%。
2. 對當前工資為 25 萬以上且不滿 28 萬的員工，加薪 20%。

UPDATE Salaries
 SET salary = CASE WHEN salary >= 300000
 THEN salary * 0.9
 WHEN salary >= 250000 AND salary < 280000
 THEN salary * 1.2
 ELSE salary END;

案例5

表之間的數據匹配

用以上表生成下麵表:

註:此sql實現可以使用in或exists謂詞

SELECT course_name,
	 CASE WHEN course_id IN 
	 (SELECT course_id FROM OpenCourses 
	 WHERE month = 200706) THEN '○'
	 ELSE '×' END AS "6 月",
	 CASE WHEN course_id IN 
	 (SELECT course_id FROM OpenCourses
	 WHERE month = 200707) THEN '○'
	 ELSE '×' END AS "7 月",
	 CASE WHEN course_id IN 
	 (SELECT course_id FROM OpenCourses
	 WHERE month = 200708) THEN '○'
	 ELSE '×' END AS "8 月"
FROM CourseMaster;

案例6

在 CASE 表達式中使用聚合函數

1. 獲取只加入了一個社團的學生的社團 ID。
2. 獲取加入了多個社團的學生的主社團 ID。

註:此處使用MAX,確保每個學生只被計算一次,確保在每個分組（每個學生）中只選擇一個社團.

SELECT std_id,
 CASE WHEN COUNT(*) = 1 -- 只加入了一個社團的學生
 THEN MAX(club_id)
 ELSE MAX(CASE WHEN main_club_flg = 'Y'
		 THEN club_id
		 ELSE NULL END)
 END AS main_club
FROM StudentClub
 GROUP BY std_id;

case在執行時會被判斷為一個固定值,因此可以用在聚合函數中.

習題實踐 求兩列中最大值

select key,
       case when x < y then y
	        else x end as greatest
from Greatests;

拓展為三列,需要在分支中嵌套(case表達式在執行時會解析為標量值):

select key,
       case when case when x < y then y else x end < z
	   		then z
			else case when x < y then y else x end
		end as greatest
from Greatests;

如果為四列或者五列,先行轉列之後使用max函數:

select key,MAX(col) as greatest
from (
		select key,x as col from Greatests
		union all
		select key,y as col from Greatests
		unoin all
		select key,z as col from Greate
		)TMP
group by key;

其中上述使用union all產生重覆數據(建立臨時表進行行轉列),之後使用group by進行排序,最後max進行去重.

註:Oracle 或 MySQL 可以使用下麵函數進行求解:

SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
 FROM Greatests;