mysql group by case when 分月_MySQL 关于case when结合group by用时的写法举例

原表是个员工档案，共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