江苏嘉瑞通建设有限公司网站,公司网站建设需要提供什么材料,网站开发费分摊多少年,如何自己制作h5页面文章目录 前言 sql示例 普通写法#xff1a; update case when写法 update case when 多字段写法 case when语法 case when 的坑 1、不符合case when条件但是字段被更新为null了 解决方法一#xff1a;添加where条件 解决方法二#xff1a;添加else 原样输出 2、同一条数据符…文章目录 前言 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有如下字段需要根据性别与年龄更新用户是否到达法定结婚年龄。若符合条件则更新到达法定结婚年龄否则更新为未到达法定结婚年龄
字段名 备注 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岁及以上为到达法定结婚年龄否则为未到达法定结婚年龄同时修改id1的name为zhang san ,id4的name为li siid2与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 id4 THEN li si
ELSE NAME
END
WHERE id 5;注意
case when else 原样输出则在else后直接写上列名即可 case when用法中符合where条件的所有数据都需要用case when枚举列举完要不就是走到else逻辑中否则该字段会被更新为null。案例如下 原数据
更新id小于5的数据name字段当id1更新name为zhang san,当id4更新name为li siid2与3的数据没有用case when,也没有走到else逻辑中导致name字段更新为null了结果如下
UPDATE USER SET
NAME CASE
WHEN id 1 THEN zhang san
WHEN id4 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 sexWHEN 1 THEN 男WHEN 2 THEN 女ELSE 其他
ENDCase搜索函数
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注意 1case只返回第一个符合条件的值剩下的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 age10 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表原数据如下
现在要更新id1与4的name字段但是用case when后id2与3的name被更新为null了。符合条件的数据被更新了不符合条件的数据字段被更新为null了
UPDATE USER SET
NAME CASE
WHEN id 1 THEN zhang san
WHEN id4 THEN li si
END 解决方法一添加where条件
UPDATE USER SET
NAME CASE
WHEN id 1 THEN zhang san
WHEN id4 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 id4 THEN li si
ELSE name
END 2、同一条数据符合第一个case when条件后即返回 user表原数据如下
更新id1的数据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 ;
