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

文章目录

• • 前言
  • sql示例
  • • 普通写法：
    • update case when写法
    • update case when 多字段写法
  • case when语法
  • case when 的坑
  • • 1、不符合case when条件但是字段被更新为null了
    • • 解决方法一：添加where条件
      • 解决方法二：添加else 原样输出
    • 2、同一条数据符合第一个case when条件后即返回
    • 3、case when判null问题
    • • 解决方法一：
      • 解决方法二：

前言

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

sql示例

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

普通写法：

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 '未到达法定结婚年龄'endwhere 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 NAMEEND 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 nameEND 

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 '空'ENDFROM USER 

结果如下：

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

解决方法一：

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

解决方法二：

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

来源地址：https://blog.csdn.net/weixin_49114503/article/details/122942377