mysql 分组group by结合case when 二次分组用法

第一部分:分组之后每种状态,再分组归为四类,每类再分组

不建议用法是,,或者说错误用法是

语法通过但是得不到正确的结果

sum 。。。group by case when 

比如如下写法:
 

select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action,
	case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包'   else action end action_type,case when action = '通知被拒绝' then 0 else 1 end status	from bi_product_action_list 
	group by customer_id,product_key,product_name,product_version,case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包'   else action end

正确写法

分组后再分组

select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action_type from 
	(
	select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action,
	case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包'   else action end action_type,
	case when action = '通知被拒绝' then 0 else 1 end status	from bi_product_action_list 
	group by customer_id,product_key,product_name,product_version,action
	) m
	group by customer_id,product_key,product_name,product_version,action_type

 

第二部分:常规用法

group by case when

原表是个员工档案,共583人,但case when结合group by用时,写法不同,其出来的结果也不同

例1:

select distinct a.Branch,
case when kultur = '硕士' then sum(num) else '0' end as 研究生学历,
case when kultur = '本科' then sum(num) else '0' end as 大本学历,
case when kultur = '大专' then sum(num) else '0' end as 大专学历,
case when kultur = '中专' then sum(num) else '0' end as 中专学历,
case when kultur = '高中' or kultur ='职高' or kultur ='中职'  then sum(num) else '0' end as 高中学历,
case when kultur = '初中' then sum(num) else '0' end as 初中学历,
case when kultur = '小学' then sum(num) else '0' end as 小学学历,
case when kultur = '' or kultur is null  then sum(num) else '0' end as 未填写学历
from( select Branch,kultur,count(*) as num 
        from tb_Stuffbusic 
     group by branch,kultur) a
Group by a.Branch,a.kultur

结果如下图:

 

例2:

select Branch as 部门名称,count(branch) as 总人数,
sum(case when kultur = '硕士' then 1 else '0' end) as 研究生学历,
sum(case when kultur = '本科' then 1 else '0' end) as 大本学历,
sum(case when kultur = '大专' then 1 else '0' end) as 大专学历,
sum(case when kultur = '中专' then 1 else '0' end) as 中专学历,
sum(case when kultur = '高中' or kultur ='职高' or kultur ='中职'  then 1 else '0' end) as 高中学历,
sum(case when kultur = '初中' then 1 else '0' end) as 初中学历,
sum(case when kultur = '小学' then 1 else '0' end) as 小学学历,
sum(case when kultur = '' or kultur is null  then 1 else '0' end) as 未填写学历
from tb_Stuffbusic         
group by branch

结果如下图:

 

例3:表中ID字段有时字段值长不够4位,但不能超过4位,故通过下面语句一次性加上。
UPDATE tb_Stuffbusic
SET ID = CASE WHEN LEN(ID) = 1 THEN '000'+ID
              WHEN LEN(ID) = 2 THEN '00'+ID
              WHEN LEN(ID) = 3 THEN '0'+ID
              ELSE ID
              END

 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值