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

布满荆棘的人生 2023-10-05 22:10 136阅读 0赞

#### 文章目录 ####

*   *  前言
     *  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有如下字段，需要根据性别与年龄更新用户是否到达法定结婚年龄。若符合条件则更新到达法定结婚年龄，否则更新为未到达法定结婚年龄

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>姓名</td> 
  </tr> 
  <tr> 
   <td>age</td> 
   <td>年龄</td> 
  </tr> 
  <tr> 
   <td>sex</td> 
   <td>性别</td> 
  </tr> 
  <tr> 
   <td>remark</td> 
   <td>备注</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][f79f5e8277cd4e16ba8b0fcc68993655.png]

#### 普通写法： ####

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;

![在这里插入图片描述][de9da30932d84e8b9b4e4de1a4b7e54c.png]

#### 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;

![在这里插入图片描述][78401e8b349d4abc8f5e42c50d121cee.png]  
`注意：`

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

原数据：  
![在这里插入图片描述][95ff5eaa58d047499419ebdfaab32521.png]  
更新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;

![在这里插入图片描述][2b237b2b5d2747959e4b580124260d1c.png]

### 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表原数据如下：  
![在这里插入图片描述][11cd1f5499ba417591441c0f1c70655f.png]  
现在要更新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

![在这里插入图片描述][2b237b2b5d2747959e4b580124260d1c.png]

##### 解决方法一：添加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表原数据如下：  
![在这里插入图片描述][11cd1f5499ba417591441c0f1c70655f.png]  
更新id=1的数据remark字段，当年龄小于22更新为“年龄”，当性别为男更新为“性别”

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

可以看到当同一条数据同时满足多个case when时，只会更新为第一个命中的条件。  
![在这里插入图片描述][668a7f0e73fc4659a450745a5e57da9c.png]

#### 3、case when判null问题 ####

user表原数据：  
![在这里插入图片描述][0cfba1ac35d942829d51a14428307241.png]  
在查询时，当remark字段为null时，我们想让其返回为“空”,但是用case when后发现没生效。

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

结果如下：  
![在这里插入图片描述][6c27c0bbfa0c4c9c8347f567855964ee.png]  
原理可以看上面的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 ;

![在这里插入图片描述][ca9a7470369e493c9697bb9a92bf0f6b.png]

[f79f5e8277cd4e16ba8b0fcc68993655.png]: https://img-blog.csdnimg.cn/f79f5e8277cd4e16ba8b0fcc68993655.png
[de9da30932d84e8b9b4e4de1a4b7e54c.png]: https://img-blog.csdnimg.cn/de9da30932d84e8b9b4e4de1a4b7e54c.png
[78401e8b349d4abc8f5e42c50d121cee.png]: https://img-blog.csdnimg.cn/78401e8b349d4abc8f5e42c50d121cee.png
[95ff5eaa58d047499419ebdfaab32521.png]: https://img-blog.csdnimg.cn/95ff5eaa58d047499419ebdfaab32521.png
[2b237b2b5d2747959e4b580124260d1c.png]: https://img-blog.csdnimg.cn/2b237b2b5d2747959e4b580124260d1c.png
[11cd1f5499ba417591441c0f1c70655f.png]: https://img-blog.csdnimg.cn/11cd1f5499ba417591441c0f1c70655f.png
[668a7f0e73fc4659a450745a5e57da9c.png]: https://img-blog.csdnimg.cn/668a7f0e73fc4659a450745a5e57da9c.png
[0cfba1ac35d942829d51a14428307241.png]: https://img-blog.csdnimg.cn/0cfba1ac35d942829d51a14428307241.png
[6c27c0bbfa0c4c9c8347f567855964ee.png]: https://img-blog.csdnimg.cn/6c27c0bbfa0c4c9c8347f567855964ee.png
[ca9a7470369e493c9697bb9a92bf0f6b.png]: https://img-blog.csdnimg.cn/ca9a7470369e493c9697bb9a92bf0f6b.png