MySQL中case when else end用法

MySQL中case when else end用法

三种不同的写法

1、case when 条件1 then 取值1 else 不满足条件的取值 end

2、case 字段 when 条件值 then 满足条件后的结果 else 不满足条件的结果 end

3、case when 条件表达式1 then 满足条件后的结果1 else 不满足条件的结果1

when 条件表达式2 then 满足条件后的结果2 else 不满足条件的结果2 end

实例

	update base_customer_info_source b 
	set b.top=case when b.customer_type_1 is not null then '1'
				   else '0' end,
		  b.freezer_num=case when b.customer_type_2 is not null then 1
					         else 0 end
	where 1=1
	and b.city='111111'

if的写法

update insight_poi ip
INNER JOIN base_customer_info_source b on ip.customer_id=b.id
set ip.customer_top=IF(b.customer_type_1 is not null,'1','0'),
	  ip.customer_freezer_num=IF(b.customer_type_2 is not null,1,0)
where 1=1
and b.city='111111'