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