MySQL中case的用法

“入目诗成天又雪，与梅并作十分春”

序

最近看书得MySQL中case的几种用法，分享给大家。以下内容基于MySQL5.7.18。

用法

1. case行转列用法

现有表数据如下图所示（性别列中，1表示男，2表示女）：

需统计各年级男女人数。虽然表中数据已有各年级的男女人数，但是还是不够一目了然。接下来使用case语句查询如下：

SELECT
	class,
	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
	`school_test` 
GROUP BY
	class;

结果如下：

这是最经典的case行转列用法。
2. 将已有编号方式转换为新的方式并统计
将上面的表数据修改为如下格式：

需要统计各年级的总人数。SQL语句如下：

SELECT
CASE class 
	WHEN '一（1）班' THEN '一年级' 
	WHEN '二（1）班' THEN '二年级' 
	WHEN '三（1）班' THEN '三年级' 
	WHEN '四（1）班' THEN '四年级' 
	WHEN '五（1）班' THEN '五年级' 
	WHEN '六（1）班' THEN '六年级' 
	else '其他' end as new_class,
	sum(population)
FROM
	`school_test` 
GROUP BY
	CASE class 
	WHEN '一（1）班' THEN '一年级' 
	WHEN '二（1）班' THEN '二年级' 
	WHEN '三（1）班' THEN '三年级' 
	WHEN '四（1）班' THEN '四年级' 
	WHEN '五（1）班' THEN '五年级' 
	WHEN '六（1）班' THEN '六年级' 
	else '其他' end ;

查询结果如下：

为了SQL语句的美观，group by 字句可以引用上面case的别名，如下：

SELECT
CASE class 
	WHEN '一（1）班' THEN '一年级' 
	WHEN '二（1）班' THEN '二年级' 
	WHEN '三（1）班' THEN '三年级' 
	WHEN '四（1）班' THEN '四年级' 
	WHEN '五（1）班' THEN '五年级' 
	WHEN '六（1）班' THEN '六年级' 
	else '其他' end as new_class,
	sum(population)
FROM
	`school_test` 
GROUP BY
	new_class;  -- 这里使用了别名

3. 在 UPDATE 语句里进行条件分支
现有表如下，各人员对应薪水表：

公司有如下安排：对工资高于8000的人员降薪20%，对工资低于8000的人员加薪20%。
乍一看，分别执行下面两个 UPDATE 操作好像就可以做到，但这样的结果却是不正确的。

-- 条件 1
UPDATE salary_test
SET salary = salary * 0.8
WHERE salary > 8000;
-- 条件 2
UPDATE salary_test
SET salary = salary * 1.2
WHERE salary < 8000;

我们来分析一下不正确的原因。例如张三当前工资是9000元，按“条件 1”执行 UPDATE 操作后，张三的工资会被更新为 7200元，但继续按“条件 2”执行 UPDATE 操作后，张三的工资又会被更新为8640元。这样，张三的工资被更新了两次，并且最终的结果也不对，张三结果应该为7200元。
这时候就可以用case来更新这个表，如下：

UPDATE salary_test 
SET salary =
CASE
	WHEN salary > 8000 THEN
	salary * 0.8 
	WHEN  salary < 8000 THEN
salary * 1.2 ELSE salary END;

总结

这就是case的三种常用格式，其实最常用的还是第一种行转列的形式。希望对大家的学习有帮助。