update case when 多字段，多条件， mysql中case when用法

前言

本篇内容主要讲解如何根据多个条件更新某字段。例如男生年龄大于22岁即更新为到达法定结婚年龄，女生年龄大于20岁即更新为到达法定结婚年龄。在这种需求中，需要根据两个条件更新某个字段；

sql示例

假设用户表user有如下字段，需要根据性别与年龄更新用户是否到达法定结婚年龄。若符合条件则更新到达法定结婚年龄，否则更新为未到达法定结婚年龄

字段名	备注
name	姓名
age	年龄
sex	性别
remark	备注

普通写法：

update  user set remark ='到达法定结婚年龄' where sex = '男' and age >=22;
update  user set remark ='到达法定结婚年龄' where sex = '女' and age >=20;
update  user set remark ='未到达法定结婚年龄' where sex = '男' and age <22;
update  user set remark ='未到达法定结婚年龄' where sex = '女' and age <20;

说明：以上的写法需要四句才能完成此需求，即使做优化，也需要两个sql语句才能完成此需求，是否有一个sql即可满足需求呢，sql中有个case when语法，可以完成此功能。

update case when写法

update user set remark = case 
when sex = '男' and age >=22 then '到达法定结婚年龄'
when sex = '女' and age >=20 then '到达法定结婚年龄'
when sex = '男' and age <22 then '未到达法定结婚年龄'
when sex = '女' and age <20 then '未到达法定结婚年龄'
end
where id <5;

update case when 多字段写法

需求：修改remark字段，要求男生22岁及以上为到达法定结婚年龄否则为未到达法定结婚年龄，女生20岁及以上为到达法定结婚年龄否则为未到达法定结婚年龄，同时修改id=1的name为zhang san ,id=4的name为li si，id=2与3的name维持原样。

UPDATE USER SET remark = CASE 
WHEN sex = '男' AND age >=22 THEN '到达法定结婚年龄'
WHEN sex = '女' AND age >=20 THEN '到达法定结婚年龄'
WHEN sex = '男' AND age <22 THEN '未到达法定结婚年龄'
WHEN sex = '女' AND age <20 THEN '未到达法定结婚年龄'
END ,
NAME = CASE 
WHEN id =1 THEN 'zhang san'
WHEN id=4 THEN 'li si'
ELSE NAME
END 
WHERE id <5;

注意：

1. case when else 原样输出，则在else后直接写上列名即可
2. case when用法中，符合where条件的所有数据，都需要用case when枚举列举完，要不就是走到else逻辑中，否则该字段会被更新为null。案例如下：

原数据：

更新id小于5的数据name字段，当id=1更新name为zhang san,当id=4更新name为li si，id=2与3的数据没有用case when,也没有走到else逻辑中，导致name字段更新为null了，结果如下：

UPDATE USER SET 
NAME = CASE 
WHEN id =1 THEN 'zhang san'
WHEN id=4 THEN 'li si'
END 
WHERE id <5;

case when语法

Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数

CASE [value] 
WHEN [compare_value1] THEN [result1]
WHEN [compare_value2] THEN [result2] …
ELSE [default] 
END

语义：
　　将case后面的值value分别和每个when子句后面的值compare_value进行相等比较：
　　如果一旦和某个when子句后面的值相等则返回相应的then子句后面的值result；
　　如果和所有when子句后面的值都不相等，则返回else子句后面的值；
　　如果没有else部分则返回null。
注意：
　　①value可以是表达式或者列名
　　②CASE表达式的数据类型取决于跟在then或else后面的表达式的类型
类型必须相同（可尝试做隐式类型转换），否则出错。

示例：

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

Case搜索函数

CASE 
WHEN [expr1] THEN [result1]
WHEN [expr2] THEN [result2]
WHEN [exprN] THEN [resultN]
ELSE [default] 
END

语义：
　　如果某个when子句后面的条件expr为true，则返回相应的when子句后面的值result；
　　如果所有的when子句后面的条件expr都不为true，则返回else子句后面的值；
　　如果没有else部分则返回null。

示例

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

注意：
1：case只返回第一个符合条件的值，剩下的case部分会被忽略
2：虽然CASE表达式中的ELSE子句可以省略，但还是希望大家不要省略。在一些低版本的mysql中若省略了else分支且所有的when条件都不符合可能会报错。

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。

case简单函数示例：

-- 统计年龄,姓名条件的
select 
count(case age when 10 then age else null end) as age_num,
count(case name when '张三' then name end) as name_num 
from test_list;

case搜索函数示例：

-- 统计年龄,姓名条件的
select 
count(case when age between 11 and 22 then age else null end) as age_num,
count(case when name='张三' then name end) as name_num 
from test_list;

case when 排序示例

select * 
from test_list 
order by case when age>10 then sex  end desc;

case when与聚合函数混合使用

SELECT 
 SUM(CASE WHEN sex='男' THEN 1 ELSE 0 END) AS 女生人数,
 SUM(CASE WHEN sex='女' THEN 1 ELSE 0 END) AS 男生人数
FROM user;

case when 的坑

1、不符合case when条件但是字段被更新为null了

user表原数据如下：

现在要更新id=1与4的name字段，但是用case when后id=2与3的name被更新为null了。符合条件的数据被更新了，不符合条件的数据字段被更新为null了

UPDATE USER SET 
NAME = CASE 
WHEN id =1 THEN 'zhang san'
WHEN id=4 THEN 'li si'
END 

解决方法一：添加where条件

UPDATE USER SET 
NAME = CASE 
WHEN id =1 THEN 'zhang san'
WHEN id=4 THEN 'li si'
END 
where id in (1,4);

解决方法二：添加else 原样输出

不符合case when的数据，走else逻辑，这里else 后面跟上列名，指的是按原来的值更新

UPDATE USER SET 
NAME = CASE 
WHEN id =1 THEN 'zhang san'
WHEN id=4 THEN 'li si'
ELSE name
END 

2、同一条数据符合第一个case when条件后即返回

user表原数据如下：

更新id=1的数据remark字段，当年龄小于22更新为“年龄”，当性别为男更新为“性别”

UPDATE USER SET remark = CASE 
WHEN age <22 THEN '年龄'
WHEN sex = '男' THEN '性别'
END 
WHERE id =1;

可以看到当同一条数据同时满足多个case when时，只会更新为第一个命中的条件。

3、case when判null问题

user表原数据：

在查询时，当remark字段为null时，我们想让其返回为“空”,但是用case when后发现没生效。

SELECT id,NAME,age,sex,
CASE remark 
WHEN '' THEN '空字符串'
WHEN NULL THEN '空'
END
FROM USER 

结果如下：

原理可以看上面的case when语法：

解决方法一：

SELECT id,NAME,age,sex,
CASE  
WHEN remark ='' THEN '空字符串'
WHEN remark IS NULL THEN '空'
END
FROM USER ;

解决方法二：

SELECT id,NAME,age,sex,
CASE  remark IS NULL 
WHEN FALSE THEN '空字符串'
WHEN TRUE THEN '空'
END
FROM USER ;