统计12个月份的数据

oracle用cennect by

 

select AA.mon,
IFNULL(BB.num,0) 'num'
from
(select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -11 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -10 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -9 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -8 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -7 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -6 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -5 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -4 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -3 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -2 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT( (DATE_ADD(now(),INTERVAL -1 MONTH)),'%Y-%m') as mon
union all
select DATE_FORMAT(now(),'%Y-%m') as mon) AA

left join
(
select DATE_FORMAT(t.create_date,'%Y-%m') as mon,count(*) as 'num'
from fb_fabu t
where DATE_FORMAT(t.create_date, '%Y-%m') > DATE_FORMAT(
date_sub(curdate(), INTERVAL 12 MONTH),
'%Y-%m')
GROUP BY mon
) BB

on AA.mon=BB.mon