sql-按月统计

 

Table:'createDate','pigCount';

 

SQL:

select a.mm,b.pigCount from(select 01 mm union all select 02 union all select 03 union all select 04 union all select 05 union all select 06 union all select 07 union all select 08 union all select 09 union all select 10 union all select 11 union all select 12 )a left join (select DATE_FORMAT(createDate,'%m') as new_month,sum(pigCount) as pigCount from incomings where 1=1 and DATE_FORMAT(createDate,'%Y')='2009' group by new_month) b on a.mm=b.new_month