看起来类似于程序语言中Switch Case多条件匹配语法，可以实现很多高级功能
Case语法
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
预制数据
mysql> select * from person;
+----+---------+--------+------+--------+
| id | country | name | age | gender |
+----+---------+--------+------+--------+
| 3 | china | tim | 10 | 1 |
| 7 | usa | kobe | 10 | 1 |
| 8 | usa | james | 20 | 1 |
| 9 | usa | grace | 30 | 0 |
| 10 | japan | monica | 40 | 2 |
+----+---------+--------+------+--------+
Case的场景示例
根据字段查询分组结果
mysql> SELECT id, `name`, CASE gender WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '其他' END AS gender FROM person;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 3 | tim | 男 |
| 7 | kobe | 男 |
| 8 | james | 男 |
| 9 | grace | 女 |
| 10 | monica | 其他 |
+----+--------+--------+
分组统计
- 根据不同国家统计用户的性别
mysql> select country,
-> sum(case when gender=1 then 1 else 0 end) as male,
-> sum( case when gender=0 then 1 else 0 end) as female,
-> sum(case when gender>1 then 1 else 0 end) as unknown
-> from person
-> group by country;
+---------+------+--------+---------+
| country | male | female | unknown |
+---------+------+--------+---------+
| china | 1 | 0 | 0 |
| japan | 0 | 0 | 1 |
| usa | 2 | 1 | 0 |
+---------+------+--------+---------+
批量更新
- 简单Case函数
UPDATE person
SET `status` = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN 1
END
WHERE id IN (1, 2, 3, 4)
注意事项：一定要有WHERE id IN的限制，否则不在WHEN中的记录就会被置为NULL
- Case搜索函数
UPDATE person
SET `status` = CASE
WHEN id%2=1 THEN 1
WHEN id%2=0 THEN 0
END
区别：简单Case函数只能实现相等条件判断，Case搜索函数适合复杂条件判断，比如大于、小于等
