SQL进阶（一） CASE 表达式

CASE 表达式

一、简述

CASE 表达式是 SQL 里非常重要而且使用起来非常便利的技术，可以用来描述条件分支。通过它可以方便的实现行列转换、数据重分组。

基本写法

• 简单 CASE 表达式

  CASE sex
  	WHEN '1' THEN 男
  	WHEN '2' THEN 女
  ELSE '其他' END	
  

• 搜索 CASE 表达式

  CASE WHEN sex = '1' THEN '男'
  	 WHEN sex = '2' THEN '女'
  ELSE '其他' END
  

注意点

在编写 SQL 语句的时候需要注意，在发现为真的 WHEN 子句时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。

ELSE子句是可选的，不写也不会出错。不写 ELSE 时，当 CASE 表达式中的条件均不满足时，它的执行结果便是 NULL。当不写时，就可能出现"语法正确，结果不正确"的问题，所以最好明确地写上 ELSE子句（即使是在结果可以为 NULL 的情况下）。

在使用 CASE 表达式的时候，最容易出现的语法错误时忘记写 END。有时候感觉写的没问题，而执行出错的情况下，大多数是由这个原因引起的。

二、使用场景示例

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

问题描述：

现在需要按省份为单位来分组，并统计人口数量。

数据表格：city_info

city_name(城市)	population(人口)
南京	100
苏州	120
常州	80
杭州	90
宁波	80
温州	70
香港	150
澳门	120

统计结果：

地区名	人口
江苏	300
浙江	240
其他	270

分析：

因为需要根据地区来分组，其中南京、苏州、常州属于江苏；杭州、宁波、温州属于浙江，香港和澳门划入其他。

使用 CASE 表达式，则用如下所示的一条 SQL 语句就可以完成。

-- 把城市编号转换为地区编号
SELECT CASE city_name
		WHEN '南京' THEN '江苏'
		WHEN '苏州' THEN '江苏'
		WHEN '常州' THEN '江苏'
		WHEN '杭州' THEN '浙江'
		WHEN '宁波' THEN '浙江'
		WHEN '温州' THEN '浙江'
	   ELSE '其他' END AS district,
	   SUM(population)
FROM city_info
GROUP BY CASE city_name
		WHEN '南京' THEN '江苏'
		WHEN '苏州' THEN '江苏'
		WHEN '常州' THEN '江苏'
		WHEN '杭州' THEN '浙江'
		WHEN '宁波' THEN '浙江'
		WHEN '温州' THEN '浙江'
	   ELSE '其他' END; 

如果是在 MySQL 中，则可以简写为如下的方式。因为 MySQL 在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。但是这是违反标准的写法，如果需要兼容不同的数据，那么就不推荐使用。

-- 把城市编号转换为地区编号
SELECT CASE city_name
		WHEN '南京' THEN '江苏'
		WHEN '苏州' THEN '江苏'
		WHEN '常州' THEN '江苏'
		WHEN '杭州' THEN '浙江'
		WHEN '宁波' THEN '浙江'
		WHEN '温州' THEN '浙江'
	   ELSE '其他' END AS district,
	   SUM(population)
FROM city_info
GROUP BY district;

2.用一条 SQL 语句进行不同条件的统计

问题描述：

统计各个地区不同性别的人数

数据表格：city_info

city_name(城市)	sex(性别 1：男 2：女)	population(人口)
南京	1	50
南京	2	50
常州	1	50
常州	2	30
杭州	1	60
杭州	2	40
温州	1	40
温州	2	30

统计结果：

城市	男	女
南京	50	50
常州	50	30
杭州	60	40
温州	40	30

分析：

上面是一种常见的行转列的情况。将”行结构“的数据转换成了"列结构"。使用 CASE 表达式可以方便的将查询结果转换为二维表的格式。

SELECT city_name,
	-- 男性人口
	SUM(CASE SEX WHEN '1' THEN population ELSE 0 END) AS '男',
	-- 女性人口
	SUM(CASE SEX WHEN '2' THEN population ELSE 0 END) AS '女'
FROM city_info
GROUP BY city_name;

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

问题描述：

对当前雇员的薪水进行更新：
1.对当前工资为3万以上的员工，降薪10%
2.对当前工资为2.5万以上且不满2.8万的员工，加薪20%

数据表格：salary

name(雇员)	salary(薪水)
张三	30000
李四	28000
王五	25000

统计结果：

name	salary
张三	27000
李四	25200
王五	22500

分析：

咋看上面的两个条件分别通过下面的两个 UPDATE 操作好像就可以做到，但这样的结果却是不正确的。

-- 条件 1
UPDATE salary
	SET salary = salary * 0.9
WHERE salary >= 30000;	

-- 条件 2
UPDATE salary
	SET salary = salary * 1.2
WHERE salary >= 25000 AND salary < 28000;	

张三的当前工资是30万元，按"条件1"执行 UPDATE 操作后，工资会被更新为2.7万元，但继续按"条件2"执行 UPDATE 操作后，工资又会被更新为32.4万元。这样，本来应该被降薪的员工却被加薪了0.24万元。

问题在于，第一次的 UPDATE 操作执行后，"当前工资"发生了变化，如果还拿它当作第二次 UPDATE 的判定条件，结果就会不准确。

使用 CASE 表达式便可以执行正确的更新操作。这种操作是一气呵成的，可以避免之前两次 UPDATE 出现的错误。

UPDATE salary
	SET salary = CASE WHEN salary >= 3000
   					THEN salary * 0.9
   					WHEN salary >= 25000 AND salary < 28000
   					THEN salary * 1.2
   					ELSE salary END;

需要注意的是，如果以上的 CASE 表达式里没有明确指定 ELSE 子句，执行结果会被默认地处理成 ELSE NULL。