MySQL,case when你真的会用吗？附避坑指南
case when 的语法有如下两种：
1 CASE WHEN [expr] THEN [result1]…ELSE [default] END2 CASE [col_name] WHEN [value1/expr1] THEN [result1]…ELSE [default] END
注意：
第一种语法最常用，可比较等于、范围的条件。遇到第一个满足条件的即返回，不再往下比较，如果没有满足的条件则返回else里的结果，如果没有else则返回NULL。
第二种语法不常用，只能比较等于的条件。when后面如果是值，则是表示等于的意思，"="可以不用写，遇到满足条件的才返回，如果没有满足的条件则返回else里的结果，如果没有else则返回NULL。when后面如果是表达式，不管when的条件是否满足，都是返回else里的结果，如果没有else则返回NULL。
when后面要么是表达式，要么是值，不然报错。如果是“=‘101’“或者“>=1000” 这样既不是表达式，也不是值，则执行时报错。
表结构：
CREATE TABLE `staff` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(50) NOT NULL COMMENT '姓名',`dept_id` varchar(10) DEFAULT NULL COMMENT '部门id',`salary` double DEFAULT NULL COMMENT '工资',`sex` int(11) DEFAULT NULL COMMENT '性别，1是男生，0是女生',`created_time` datetime DEFAULT NULL COMMENT '创建时间',`updated_time` datetime DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='职员表'
现有记录：
mysql> select * from staff ;+----+-------+---------+--------+------+---------------------+---------------------+| id | name | dept_id | salary | sex | created_time | updated_time |+----+-------+---------+--------+------+---------------------+---------------------+| 1 | Tom | 101 | 2500 | 1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 2 | Tonny | 101 | 3500 | 1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 3 | Bob | 101 | 3500 | 0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 4 | Rob | 101 | 4500 | 1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 5 | Sudey | 101 | 5500 | 0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 6 | Sunny | 102 | 5500 | 1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 7 | Sedey | 102 | 3500 | 0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 8 | Bobby | 102 | 4500 | 0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 9 | Weedy | 103 | 4500 | 1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 || 10 | Jimme | 104 | 5500 | 0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |+----+-------+---------+--------+------+---------------------+---------------------+10 rows in set (0.00 sec)
我们想根据salary区分类别：
1. 使用
CASE WHEN [expr] THEN [result1]…ELSE [default] END
如下语句的结果符合期望：
when后面是表达式
selectname,salary,casewhen salary >= 5000 then 'T1'when salary >= 4000 then 'T2'when salary >= 3000 then 'T3'when salary >= 2000 then 'T4'else 'T5'end as salary_levelfrom staff ;+-------+--------+--------------+| name | salary | salary_level |+-------+--------+--------------+| Tom | 2500 | T4 || Tonny | 3500 | T3 || Bob | 3500 | T3 || Rob | 4500 | T2 || Sudey | 5500 | T1 || Sunny | 5500 | T1 || Sedey | 3500 | T3 || Bobby | 4500 | T2 || Weedy | 4500 | T2 || Jimme | 5500 | T1 |+-------+--------+--------------+10 rows in set (0.00 sec)
如下语句的结果则不符合期望：
顺序弄反了，是返回第一个符合条件的结果
selectname,salary,casewhen salary >= 2000 then 'T1'when salary >= 3000 then 'T2'when salary >= 4000 then 'T3'when salary >= 5000 then 'T4'else 'T5'end as salary_levelfrom staff ;+-------+--------+--------------+| name | salary | salary_level |+-------+--------+--------------+| Tom | 2500 | T1 || Tonny | 3500 | T1 || Bob | 3500 | T1 || Rob | 4500 | T1 || Sudey | 5500 | T1 || Sunny | 5500 | T1 || Sedey | 3500 | T1 || Bobby | 4500 | T1 || Weedy | 4500 | T1 || Jimme | 5500 | T1 |+-------+--------+--------------+10 rows in set (0.00 sec)
2. 使用
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
如下语句的结果符合期望：
when后面是值
selectname,salary,case salarywhen 2500 then 'T1'when 3500 then 'T2'when 4500 then 'T3'when 5500 then 'T4'else 'T5'end as salary_levelfrom staff ;+-------+--------+--------------+| name | salary | salary_level |+-------+--------+--------------+| Tom | 2500 | T1 || Tonny | 3500 | T2 || Bob | 3500 | T2 || Rob | 4500 | T3 || Sudey | 5500 | T4 || Sunny | 5500 | T4 || Sedey | 3500 | T2 || Bobby | 4500 | T3 || Weedy | 4500 | T3 || Jimme | 5500 | T4 |+-------+--------+--------------+10 rows in set (0.00 sec)
如下语句则报错：
when后面既不是表达式，也不是值
selectname,salary,case salarywhen >= 2000 then 'T1'when >= 3000 then 'T2'when >= 4000 then 'T3'when >= 5000 then 'T4'else 'T5'end as salary_levelfrom staff ;-- ERROR 1064 (42000): You have an error in your SQL syntax;
3. 使用
CASE [col_name] WHEN [expr1] THEN [result1]…ELSE [default] END
如下语句不报错，但是结果不符合期望：
when后面是表达式，不管when的条件是否满足，都是返回else里的结果
selectname,salary,case salarywhen salary=2500 then 'T1'when salary=3500 then 'T2'when salary=4500 then 'T3'when salary=5500 then 'T4'else 'T5'end as salary_levelfrom staff ;+-------+--------+--------------+| name | salary | salary_level |+-------+--------+--------------+| Tom | 2500 | T5 || Tonny | 3500 | T5 || Bob | 3500 | T5 || Rob | 4500 | T5 || Sudey | 5500 | T5 || Sunny | 5500 | T5 || Sedey | 3500 | T5 || Bobby | 4500 | T5 || Weedy | 4500 | T5 || Jimme | 5500 | T5 |+-------+--------+--------------+10 rows in set (0.00 sec)
如下语句不报错，但是结果不符合期望：
when后面是表达式，已经把else语句注释掉了，不管when的条件是否满足，都是返回NULL
selectname,salary,case salarywhen salary=2500 then 'T1'when salary=3500 then 'T2'when salary=4500 then 'T3'when salary=5500 then 'T4'-- else 'T5'end as salary_levelfrom staff ;+-------+--------+--------------+| name | salary | salary_level |+-------+--------+--------------+| Tom | 2500 | NULL || Tonny | 3500 | NULL || Bob | 3500 | NULL || Rob | 4500 | NULL || Sudey | 5500 | NULL || Sunny | 5500 | NULL || Sedey | 3500 | NULL || Bobby | 4500 | NULL || Weedy | 4500 | NULL || Jimme | 5500 | NULL |+-------+--------+--------------+10 rows in set (0.00 sec)
以上是本人亲测，之前使用时有些迷惑第二种语法，如今终于明白了。下期分享case when在分组统计中的实际用法。
当前版本：
mysql> select version();+-----------+| version() |+-----------+| 5.7.28 |+-----------+1 row in set (0.01 sec)mysql>
历史文章：
「欢迎关注，一起学习，一起进步」