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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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