SQL case when 的用法

case when用于按条件筛选：

select case 
		when logo='123' then cardName
		else 'xyk'
	end as card_name
from table
	where dt='20220202'

case when用于统计
count用于，当值赋予NULL时，count不会累计，其他值会累计，例如有一个SQL：select count(1) from t where name='A’的结果为5，表总数为10。使用case when count效果如下：

select count(case 
		when name='A' then 1
		else NULL
	end) as numA
from t

以上结果为5.

select count(case 
		when name='A' then 1
		else 0
	end) as numA
from t

以上结果为10.

sum的效果：

select sum(case 
		when name='A' then 1
		else 0
	end) as numA
from t

以上结果为5

添加group by 的场景：

select name,count(case 
		when name='A' then 1
		else 0
	end) as num
from t
group by name

以上sql的name='A’不起作用，等价select name,count(1) from t group by name。

select name,count(case 
		when name='A' then 1
		else NULL
	end) as num
from t
group by name

以上SQL除了name='A’的为5，其它的num都为0.

case when结合count distinct使用，假设有如下表：

prod_id	act_type	div_id
p1	click	divice1
p1	view	divice2
p1	view	divice1
p1	click	divice1
p1	click	divice2
p1	click	divice3

select prod_id,count(distinct case 
		when act_type='click' then div_id 
	end) as clickUV, 
	count(distinct case 
		when act_type='view' then div_id 
	end) as viewUV
	from t group by prod_id

结果：

prod_id	clickUV	viewUV
p1	3	2

备注：count(NULL)=0,sum(NULL)=NULL