SQL进阶教程——CASE表达式（第一章）

1. 前言

CASE表达式是从SQL-92标准开始被引入的，可能因为他是相对比较新的技术，所以尽管使用起来非常便利，但其真正的价值却并不怎么为人所知。很多人不用它，或者用他的简版函数，例如DECODE(Oracle),IF(MySQL)等。如果能用好它，那么SQL能解决的问题就会更广泛，写法也会更加漂亮。而且，因为CASE表达式是不依赖于具体数据库的技术，所以可以提高SQL代码的可移植性。这里强烈推荐大家改用CASE表达式，特别是使用DECODE函数的Oracle用户。

2. CASE表达式概述

--简单CASE表达式
CASE sex
  WHEN '1' THEN '男'
  WHEN '2' THEN '女'
ELSE '其他' END

--搜索CASE表达式
CASE WHEN sex='1' THEN '男'
  	 WHEN sex='2' THEN '女'
ELSE '其他' END
/**简单CASE表达式能写的条件，搜索CASE表达式也能写，所以一般采用搜索CASE表达式。
在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止,而剩余的WHEN子句会被忽略.
为了避免引起不必要的混乱，使用WHEN子句时要注意条件的排他性**/

注意：

• 统一各分支返回的数据类型（如果返回数据类型不同就会报错）
• 不要忘了写END
• 养成写ELSE子句的习惯

3. 将已有编号方式转换为新的编号方式并统计

• 统计数据源表PopTbl

pred_name(县名)	population(人口)
德岛	100
香川	200
爱暖	150
高知	200
福冈	300
佐贺	100
长崎	200
东京	400
群马	50

• 期望结果

地区名	人口
四国	650
九州	600
其他	450

• 代码

--把县编号转换成地区编号
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;
--

4. 用一条SQL进行不同条件的统计

• 执行两条SQL语句来查询（不推荐）

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

--女性人口
SELECT pred_name,
	SUM(population)
FROM PopTbl2
WHERE sex='2'
GROUP BY pred_name;
/**最后需要通过宿主言语或者应用程序将查询结果按列展开。
如果使用UNION，只用一条SQL就可以实现，但使用这种做法时，工作量并没有减少，
SQL语句也会变得很长**/

• 使用CASE表达式

--新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支
SELECT pred_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 pred_name;

5. 在UPDATE语句里进行条件分支

• Saleries

name	salary
相田	300000
神崎	270000
木村	220000
齐藤	290000

• 代码

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;
/**为什么不适用两次UPDATE操作了，因为第一次的UPDATE操作执行后，当前工资发生了变化，
如果还拿它当做第二次UPDATE的判定条件，结果就会不准确。
SQL最后一行的“ELSE salary”非常关键，如果没有它，不满足条件的
(如果CASE表达式没有明确的指定ELSE子句，执行结果会被默认的处理成ELSE NULL)**/

6. 表之间的数据匹配

于DECODE函数等相比，CASE表达式的一大优势在于能够判断表达式。也就是说，在CASE表达式里，我们可以使用BETWEEN，LIKE和<,>等便利的谓词组合，以及能够嵌套子查询的IN和EXISTS谓词。

• 课程一览(CourseMaster)

course_id	course_name
1	会计入门
2	财务知识
3	薄记考试
4	税务师

• 开设的课程(OpenCourses)

month	course_id
200706	1
200706	3
200706	4
200708	4
200708	2
200708	4

• 需要生成的交叉表（期望结果）

course_name	6月	7月	8月
会计入门	〇	X	X
财务知识	X	X	〇
薄记考试	〇	X	X
税务师	〇	〇	〇

• 代码

--表的匹配：使用IN谓词
SELECT course_name,
		CASE WHEN course_id IN
					(SELECT course_id FROM OpenCourses
					WHERE month = 200706) THEN '〇'
			ELSE 'x' END AS "6月",
		CASE WHEN course_id IN
					(SELECT course_id FROM OpenCourses
					WHERE month = 200707) THEN '〇'
			ELSE 'x' END AS "7月",
		CASE WHEN course_id IN
					(SELECT course_id FROM OpenCourses
					WHERE month = 200708) THEN '〇'
			ELSE 'x' END AS "8月"
FROM CourseMaster;



--表的匹配：使用EXISTS谓词(含有EXISTS的关联子查询)
SELECT course_name,
		CASE WHEN EXISTS
					(SELECT course_id FROM OpenCourses OC
					WHERE month = 200706
					AND OC.course_id = CM.course_id) THEN '〇'
			ELSE 'x' END AS "6月",
		CASE WHEN EXISTS
					(SELECT course_id FROM OpenCourses OC
					WHERE month = 200707
					AND OC.course_id = CM.course_id) THEN '〇'
			ELSE 'x' END AS "7月",
		CASE WHEN EXISTS
					(SELECT course_id FROM OpenCourses OC
					WHERE month = 200708
					AND OC.course_id = CM.course_id) THEN '〇'
			ELSE 'x' END AS "8月",
FROM CourseMaster CM;
--无论是用IN还是EXISTS，得到的结果都是一样的，但从性能方面说，EXISTS更好

7. 在CASE表达式中使用聚合函数

• StudentClub

std_id(学号)	club_id(社团ID)	club_name(社团名)	main_club_flg(主社团标志)
100	1	棒球	Y
100	2	管弦乐	N
200	2	管弦乐	N
200	3	羽毛球	Y
200	4	足球	N
300	4	足球	N
400	5	游泳	N
500	6	围棋	N

• 期望

1. 获取只加入了一个社团的学生的社团ID
2. 获取加入了多个社团的学生的主社团ID

• 代码

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;
/**这条SQL语句在CASE表达式里使用了聚合函数，又在聚合函数里使用了CASE表达式，让人眼花缭乱
主要是用“CASE WHEN COUNT(*) = 1...ELSE ..”这样的CASE表达式来表示
“只加入了一个社团还是加入了多个社团”这样的条件分支。如果用一句话形容这个技巧，
可以这么说：新手用HAVING子句进行条件分支，高手用SELECT子句进行条件分支**/

• 结果

std_id	main_club
100	1
200	3
300	4
400	5
500	6

本节要点：

1. 在GROUP BY子句使用CASE表达式，可以灵活的选择作为聚合的单位的编号和等级。
2. 在聚合函数中使用CASE表达式，可以轻松的将行结构的数据转换成列结构的数据。
3. 相反，聚合函数也可以嵌套进CASE表达式里使用
4. 相比依赖于具体数据库的函数，CASE表达式有更强大的表达能力和更好的可移植性。
5. 正因为CASE表达式是一种表达式而不是语言，才有了这诸多优点。