直接上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
方法网上有很多，这种个人觉得比较好理解
