MySQL统计近12个月的数据，如果某个月份没有数据则用0填充

直接上SQL

select dict.ym, ifnull(ret.ac, 0) num from (
select date_format(curdate(),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 1 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 2 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 3 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 4 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 5 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 6 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 7 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 8 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 9 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 10 month),'%Y-%m') AS `ym` union
select date_format((curdate() - interval 11 month),'%Y-%m') AS `ym`
) dict left join (
	select count(e.id) ac, left(e.data_time, 7) ym
	from yourtable e 
	where left(e.data_time, 7) > left(date_sub(curdate(), interval 12 month), 7)
	and left(e.data_time, 7) <= left(date_add(curdate(), interval -1 day), 7)
	group by left(e.data_time, 7)
) ret on dict.ym = ret.ym
order by dict.ym

方法网上有很多，这种个人觉得比较好理解