SQL之CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常
称为（条件）分支

CASE表达式的语法

CASE WHEN <求值表达式> THEN <表达式>
 WHEN <求值表达式> THEN <表达式>
 WHEN <求值表达式> THEN <表达式>
 . . .
 ELSE <表达式>
END

WHEN 子句中的“< 求值表达式 >”就是类似“列 = 值”这样，返回
值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作
使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。
CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行
求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果
为真（TRUE），那么就返回 THEN 子句中的表达式，CASE 表达式的执行
到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。
如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE
中的表达式，执行终止。
从 CASE 表达式名称中的“表达式”我们也能看出来，上述这些整
体构成了一个表达式。并且由于表达式最终会返回一个值，因此 CASE 表
达式在 SQL 语句执行时，也会转化为一个值。虽然使用分支众多的 CASE
表达式编写几十行代码的情况也并不少见，但是无论多么庞大的 CASE 表
达式，最后也只会返回一个简单的值。

SELECT product_name,
	 CASE WHEN product_type = '衣服'
				 THEN 'A ：' | | product_type
				 WHEN product_type = '办公用品'
				 THEN 'B ：' | | product_type
				 WHEN product_type = '厨房用具'
				 THEN 'C ：' | | product_type
				 ELSE NULL
	 END AS abc_product_type
 FROM Product;

使用CASE表达式进行行列转换

通常使用GROUP BY

SELECT product_type,
 SUM(sale_price) AS sum_price
 FROM Product
 GROUP BY product_type;

使用CASE表达式进行行列转换
– 对按照商品种类计算出的销售单价合计值进行行列转换

SELECT SUM(CASE WHEN product_type = '衣服'
 THEN sale_price ELSE 0 END) AS sum_price_clothes,
 SUM(CASE WHEN product_type = '厨房用具'
 THEN sale_price ELSE 0 END) AS sum_price_kitchen,
 SUM(CASE WHEN product_type = '办公用品'
 THEN sale_price ELSE 0 END) AS sum_price_office
 FROM Product;

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

 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;

这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY子句里。需要注意的是，如果对转换前的列“pref_name ”进行 GROUPBY ，就得不到正确的结果（因为这并不会引起语法错误，所以容易被忽视）。
同样地，也可以将数值按照适当的级别进行分类统计。例如，要按人口数量等级（pop_class ）查询都道府县个数的时候，就可以像下面这样写 SQL 语句。

-- 按人口数量等级划分都道府县
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;

用一条 SQL 语句进行不同条件的统计
进行不同条件的统计是 CASE 表达式的著名用法之一。例如，我们需要往存储各县人口数量的表 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;

上面这段代码所做的是，分别统计每个县的“男性”（即 ‘1’ ）人数和“女性”（即 ‘2’ ）人数。也就是说，这里是将“行结构”的数据转换成了“列结构”的数据。除了 SUM ，COUNT 、AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的格式。如果只是简单地用 GROUP BY 进行聚合，那么查询后必须通过宿主语言或者 Excel 等应用程序将结果的格式转换一下，才能使之成为交叉表。看上面的执行结果会发现，此时输出的已经是侧栏为县名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。如果用一句话来形容这个技巧，可以这样说：新手用 WHERE 子句进行条件分支，高手用 SELECT 子句进行条件分支。
在 UPDATE 语句里进行条件分支

-- 用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更换主键

-- 用CASE 表达式调换主键值
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
			THEN 'b'
			WHEN p_key = 'b'
			THEN 'a'
			ELSE p_key END
WHERE p_key IN ('a', 'b');

表之间的数据匹配

-- 表的匹配：使用IN 谓词
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;

GREATEST 和 LEAST 的功能。
greatest 几个数的最大值
lest 几个数的最小值

SELECT username,GREATEST(x,y,z) as gre  from xyz_min