MySQL case when ，if 函数用法

case when… then… else… end：

CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END
score 表：

mysql> select * from score;
+----+----------+------+------+-------+
| id | name     | sex  | age  | score |
+----+----------+------+------+-------+
|  1 | zhangsan |    1 |   19 |    99 |
|  2 | lisi     |    0 |   25 |    89 |
|  3 | wangwu   |    1 |   21 |    79 |
|  4 | zhaoliu  |    0 |   18 |    69 |
|  5 | sunqi    |    1 |   23 |    59 |
|  6 | zhouba   |    1 |   15 |    49 |
|  7 | wujiu    |    0 |   20 |    39 |
+----+----------+------+------+-------+

示例： 查找男女，以汉字显示

1、使用 case when ，符合要求的返回 1，case when 外部用 sum 进行求和

select 
	name, 
	(case sex when '1' then '男' else '女' end) sex 
from score;

if 函数

select name, if(sex = 1, '男', '女') as sex from score;


+----------+-----+
| name     | sex |
+----------+-----+
| zhangsan | 男  |
| lisi     | 女  |
| wangwu   | 男  |
| zhaoliu  | 女  |
| sunqi    | 男  |
| zhouba   | 男  |
| wujiu    | 女  |
+----------+-----+

条件表达式：

CASE
WHEN condition THEN result1 ELSE result2
END
如果没有 end，返回 NULL
注意： 多个 when 之间，不必加逗号

select 
	name, 
	score, 
	(case 
		when score > 90 then 'nice' 
		when score > 80 then 'good' 
		when score >= 60 then'not bad' 
		else 'bad' end) as level 
from score;

示例 2： 统计总人数，男女人数和男女成年的人数

1、case when 外部用 sum 进行求和

select 
	count(id) as '总人数', 
	sum(case when sex = '1' then 1 else 0 end) as '男生人数', 
	sum(case when sex = '0' then 1 else 0 end) as '女生人数', 
	sum(case when age >= 18 and sex = '1' then 1 else 0 end) as '成年男生人数', 
	sum(case when age >= 18 and sex = '0' then 1 else 0 end) as '成年女生人数' 
from score;

if 函数计数

select 
	count(if(age >= 18 and sex = 1, 1, null)) as '成年男生人数', 
	count(if(age >= 18 and sex = 0, 1, null)) as '成年女生人数' 
from score;


+-----------+--------------+--------------+--------------------+--------------------+
| 总人数    | 男生人数     | 女生人数     | 成年男生人数       | 成年女生人数       |
+-----------+--------------+--------------+--------------------+--------------------+
|         7 |            4 |            3 |                  3 |                  3 |
+-----------+--------------+--------------+--------------------+--------------------+