背景

中小项目（非大数据项目）中总是有统计报表模块，其中每次都有对12个月数据统计的需求，特别留作记录

实战

这个实例是统计每个月用户注册总量

如果数据表中create_time为datetime格式

select 
sum(case month(create_time) when '1' then 1 else 0 end) as 一月,
sum(case month(create_time) when '2' then 1 else 0 end) as 二月,
sum(case month(create_time) when '3' then 1 else 0 end) as 三月,
sum(case month(create_time) when '4' then 1 else 0 end) as 四月,
sum(case month(create_time) when '5' then 1 else 0 end) as 五月,
sum(case month(create_time) when '6' then 1 else 0 end) as 六月,
sum(case month(create_time) when '7' then 1 else 0 end) as 七月,
sum(case month(create_time) when '8' then 1 else 0 end) as 八月,
sum(case month(create_time) when '9' then 1 else 0 end) as 九月,
sum(case month(create_time) when '10' then 1 else 0 end) as 十月,
sum(case month(create_time) when '11' then 1 else 0 end) as 十一月,
sum(case month(create_time) when '12' then 1 else 0 end) as 十二月
from tbl_user
where year(create_time) in ('2018');

如果数据表中create_time为时间戳，需要进行时间戳转换

select 
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '1' then 1 else 0 end) as 一月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '2' then 1 else 0 end) as 二月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '3' then 1 else 0 end) as 三月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '4' then 1 else 0 end) as 四月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '5' then 1 else 0 end) as 五月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '6' then 1 else 0 end) as 六月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '7' then 1 else 0 end) as 七月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '8' then 1 else 0 end) as 八月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '9' then 1 else 0 end) as 九月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '10' then 1 else 0 end) as 十月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '11' then 1 else 0 end) as 十一月,
sum(case month(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) when '12' then 1 else 0 end) as 十二月
from tbl_user
where year(FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%S')) in ('2018');