CASE WHEN

工作中常用sql语句：https://mp.weixin.qq.com/s/G71VhGLoM6rwUGah5PaZhQ
sql常见面试题：
https://www.bilibili.com/read/cv8481239/

case when用法
select case 字段 when 1 then ‘有’ else ‘无’ end as 字段 from 表
select (case when password is null then ‘1’ else password ) end ) as 字段 from 表

select
t3.region_id as ‘regionId’,
t3.region_name as ‘regionName’,
count(case when t1.tricolor = 1 then 1 else null end ) as ‘greenTotal’,
count(case when t1.tricolor = 2 then 1 else null end ) as ‘yellowTotal’,
count(case when t1.tricolor = 3 then 1 else null end )as ‘redTotal’
from pa_household_member t1
left join pa_household t2 on t2.household_id = t1.household_id
left join system_region t3 on t3.region_id = t2.area
group by t2.area

select d.tools_name, null as sum,null as num,dtp.specs,dtp.type,count( case when state =0 then ‘state’ end ) as 在库 ,
count(case when state =1 then ‘state’ end) 借出 ,
count(case when state =2 then ‘state’ end) 丢失,
count(case when state =3 then ‘state’ end) 报废
from dm_tools_KC d left join dm_tools_project dtp on d.tools_name = dtp.tools_name where d.TOOLS_NAME =‘BH’ group by d.tools_name,dtp.specs,dtp.type;

上面这个sql就是统计出不同工具名称的各个状态的数量，按工具名称进行分组统计

sum(case when t.ride_num=1 then 1 else 0 end ) as ride_1_times

count(case when t.ride_num=1 then 1 else null end ) as ride_1_times

count(case when t.ride_num=1 then 1 end ) as ride_1_times then=1，

两者的查询结果相同，但sum 中case when de else 需要为0，而count case when 的else需要设定为null或者不写。count默认不统计null ，不能为0.

select c.MRL,c.OPERATION_ID,c.PRODUCT_ID,
sum(if(TYPE=‘ONSHOW’,1,0) as 上架数,
sum(if(TYPE=‘WITHDRAW’,1,0) as 下架数,
sum(if(TYPE=‘MOVE’,1,0) as 迁移数
from chanage_apply c,chanage_cabinets d
where c. – ??? 看不出你这两个表如何联接。
group by c.MRL,c.OPERATION_ID,c.PRODUCT_ID
写sql用sum能得出结果，但不建议用，应该用case when.