《sql进阶教程》之CASE 表达式

本文是《sql进阶教程》阅读笔记，感兴趣可以阅读该书对应章节，这本适合有一定sql基础的同学阅读。另外作者《sql基础教程》也值得一看。

一、 CASE 表达式

在 SQL 里表达条件分支；CASE 表达式是 SQL 里非常重要而且使用起来非常便利的技术，使用它能够过进行行列转换、已有数据重分组（分类）、与约束的结合使用、针对聚合结果的条件分支等

注：因为 CASE 表达式是不依赖于具体数据库的技术，所以可以提高 SQL 代码的可移植性

1.1 表达式概要

CASE 表达式有简单 CASE 表达式（simple case expression）和搜索 CASE 表达式（searched caseexpression）两种写法

在发现为真的 WHEN 子句时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。为了避免引起不必要的混乱，使用 WHEN 子句时要注意条件的排他性

---

1.2 使用case的注意事项

• 统一各分支返回的数据类型
• 不要忘了写 END
• 养成写 ELSE 子句的习惯

---

1.3 案例应用

案例一：将已有编号方式转换为新的方式并统计
例如，现在有一张按照“‘1：北海道’、‘2：青森’、……、‘47：冲绳’”这种编号方式来统计都道府县 人口的表，我们需要以东北、关东、九州等地区为单位来分组，并统计人口数量。具体来说，就是统计下表 PopTbl 中的内容，得出如右表“统计结果”所示的结果

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

注：不必了解是如何进行分类的。只需要理解其语法格式，在相似的场合能够灵活应用。

---

案例二：用一条 SQL 语句进行不同条件的统计

例如，我们需要往存储各县人口数量的表 PopTbl 里添加上“性别”列，然后求按性别、县名汇总的人数。具体来说，就是统计表 PopTbl2 中的数据，然后求出如表“统计结果”所示的结果。

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;

这里是将“行结构”的数据转换成了“列结构”的数据。除了 SUM ，COUNT 、AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。

这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的格式。在制作统计表时，这个功能非常方便

---

案例三：用 CHECK 约束定义多个列的条件关系

假设某公司规定“女性员工的工资必须在 20 万日元以下”，而在这个公司的人事表中，这条无理的规定是使用 CHECK 约束来描述的。

-- 素材准备
-- 创建表： 1:male 2:female
-- 女性员工的工资必须在 20 万日元以下
CREATE TABLE employee (
		id serial PRIMARY KEY,
		sex int,
		salary NUMERIC CHECK (CASE WHEN sex = 2 THEN CASE WHEN  salary <= 200000 THEN 1  ELSE 0 END  ELSE 1 END = 1)
);

INSERT INTO employee(sex,salary) VALUES(1,300000); -- 可以
INSERT INTO employee(sex,salary) VALUES(1,100000); -- 可以
INSERT INTO employee(sex,salary) VALUES(2,100000); -- 可以
INSERT INTO employee(sex,salary) VALUES(2,300000); -- 约束不行(new row for relation "employee" violates check constraint "employee_check")

SELECT * FROM employee;

CASE 表达式被嵌入到 CHECK 约束里，描述了"如果是女性员工，则工资是 20 万日元以下”这个命题。

在命题逻辑中，该命题是叫作蕴含式（conditional）的逻辑表达式，记作 P → Q。

补充一下关于蕴含式与逻辑与的区别：
逻辑与也是一个逻辑表达式，意思是“P 且 Q”，记作 P ∧ Q。用逻辑与改写的 CHECK 约束如下所示
CONSTRAINT check_salary CHECK(sex = 2 AND salary <= 200000 )

如果在 CHECK 约束里使用逻辑与，该公司将不能雇佣男性员工。而如果使用蕴含式，男性也可以在这里工作要想让逻辑与 P ∧ Q 为真，需要命题 P 和命题 Q 均为真，或者一个为真且另一个无法判定真假。也就是说，能在这家公司工作的是“性别为女且工资在 20 万日元以下”的员工，以及性别或者工资无法确定的员工（如果一个条件为假，那么即使另一个条件无法确定真假，也不能在这里工作）。

而要想让蕴含式 P → Q 为真，需要命题 P 和命题 Q 均为真，或者 P 为假，或者 P 无法判定真假。也就是说如果不满足“是女性”这个前提条件，则无需考虑工资约束

---

案例四：在 UPDATE 语句里进行条件分支

以某数值型的列的当前值为判断对象，将其更新成别的值 。UPDATE 操作的条件会有多个分支

要求1：对当前工资为 30 万日元以上的员工，降薪 10%。
要求2：对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪20%

-- 素材准备
CREATE TABLE salaries (
	name character VARYING(30),
	salary NUMERIC
);

-- 插入数据
INSERT INTO salaries(name,salary) VALUES('A',270000),('B',324000),('C',220000),('D',290000);

-- 使用case 进行不同条件的更新
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;

---

案例五：表之间的数据匹配

在 CASE 表达式里，我们可以使用BETWEEN 、LIKE和 < 、> 等便利的谓词组合，以及能嵌套子查询的 IN和 EXISTS 谓词。因此，CASE 表达式具有非常强大的表达能力

(一)、课程一览 CourseMaster

course_id	coursena
1	会计入门
2	财务知识
3	簿记考试
4	税

(二)、开设的课程 OpenCourses

month	course
200706	1
200706	3
200706	4
200707	4
200708	2
200708	4

用这两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程。

course_name	6 月	7 月	8 月
会计入门	○	×	×
财务知识	×	×	○
簿记考试	○	×	×
税务师	○	○	○

-- 使用exists 关键字，也可以使用子查询 IN
-- 如果需要增加月份，则修改SELECT 子句就可以了。
SELECT CM.course_name,
CASE WHEN EXISTS
	(SELECT course_id FROM OpenCourses OC
		WHERE month = 200706
		AND OC.course_id = CM.course_id) THEN '○'
	ELSE '×' END AS "6 月",
CASE WHEN EXISTS
	(SELECT course_id FROM OpenCourses OC
		WHERE month = 200707
		AND OC.course_id = CM.course_id) THEN '○'
	ELSE '×' END AS "7 月",
CASE WHEN EXISTS
(SELECT course_id FROM OpenCourses OC
		WHERE month = 200708
		AND OC.course_id = CM.course_id) THEN '○'
	ELSE '×' END AS "8 月"
FROM CourseMaster CM;

---

案例六、在 CASE 表达式中使用聚合函数

有的学生同时加入了多个社团（如学号为 100、200 的学生），有的学生只加入了某一个社团（如学号为 300、400、500 的学生）。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N

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

要求一： 获取只加入了一个社团的学生的社团 ID。
要求二： 获取加入了多个社团的学生的主社团 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;

-- 主要模式如下：CASE WHEN COUNT(*) = 1 …… ELSE …….

CASE 表达式用在 SELECT 子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部

CASE 表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正因为它是表达式，所以还可以写在SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。简单点说，在能写列名和常量的地方，通常都可以写 CASE 表达式。从这个意义上来说，与 CASE 表达式最接近的不是面向过程语言里的CASE 语句

---

1.4 小结

• 在 GROUP BY 子句里使用 CASE 表达式，可以灵活地选择作为聚合的单位的编号或等级加粗样式。这一点在进行非定制化统计时能发挥巨大的威力

• 在聚合函数中使用 CASE 表达式，可以轻松地将行结构的数据转换成列结构的数据

• 聚合函数可以嵌套进 CASE 表达式里使用

• CASE 表达式有更强大的表达能力和更好的可移植性

• CASE是一种表达式而不是语句

1.5 参考

• 《sql权威指南》15.3.5 节 “在 UPDATE 中使用 CASE 表达式”和 18.1节“CASE 表达式”等
• 《SQL 解惑（第 2 版）》谜题13、36（例子不错）、43、44 、 45 都有关于case的使用

---

1.6 练习题

习题一： 多列数据的最大值

用 SQL 从多行数据里选出最大值或最小值很容易——通过 GROUP BY子句对合适的列进行聚合操作，并使用 MAX 或 MIN 聚合函数就可以求出。那么，从多列数据里选出最大值该怎么做呢

表名：greatests

key	x	y	z
A	1	2	3
B	5	5	2
C	4	7	1
D	3	3	8

要求如下

key	greatest
A	3
B	5
C	7
D	8

sql语句如下：

--方式一：给字段key命一个别名 (order by 的执行顺序在select之后)
SELECT
	KEY,
	CASE
WHEN x > y THEN
	(CASE WHEN x > z THEN x ELSE z END)
ELSE
	(CASE WHEN y > z THEN y ELSE z END)
END AS GREATEST
FROM
	Greatests;
	
-- 方式二：转换成行格式后使用MAX 函数
SELECT key, MAX(greatest)
FROM (
	SELECT key, x AS greatest FROM Greatests
	UNION ALL
	SELECT key, y AS greatest FROM Greatests
	UNION ALL
	SELECT key, Z AS greatest FROM Greatests
) temp 
GROUP BY key 
ORDER BY key ASC;

-- 方式三：Oracle 和 MySQL中的语法 greatest.least 。 标准sql不支持。
SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
FROM Greatests;

---

习题二：转换行列——在表头里加入汇总

注意：四国 即德岛、香川、爱媛、告知

性别	全国	德岛	香川	爱媛	高知	四国（再揭）
男	855	60	100	100	100	360
女	845	40	100	50	100	290

--特殊处理一下四国即可。在聚合函数中使用case语句。
SELECT sex,
	SUM(population) AS total,
	SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
	SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
	SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
	SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
	SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
	THEN population ELSE 0 END) AS zaijie
FROM PopTbl2
GROUP BY sex;

---

习题三：用 ORDER BY 生成“排序”列

使用习题一的表，用一个查询语句，使得结果按照 B-A-D-C 这样的指定顺序进行排列（并没有什么特别的实际意义）

-- 给字段key命一个别名 (order by 的执行顺序在select之后)
SELECT 	CASE KEY 
				WHEN 'B' THEN 1
				WHEN 'A' THEN 2
				WHEN 'D' THEN 3
				WHEN 'C' THEN 4
				ELSE 5 END AS rownumber,key,x,y,z
FROM Greatests
ORDER BY rownumber ASC;

-- 也可以在order by 中使用
SELECT key
	FROM Greatests
ORDER BY CASE key
	WHEN 'B' THEN 1
	WHEN 'A' THEN 2
	WHEN 'D' THEN 3
	WHEN 'C' THEN 4
	ELSE NULL END;