SELECT DATE_FORMAT(consumeDate,'%Y-%m'),SUM(amount) FROM expense
WHERE YEAR(consumeDate)=2011
GROUP BY DATE_FORMAT(consumeDate,'%Y-%m')
我想统计1到12月的支出情况
没有数据的月份显示0
请问要如何修改这条sql
月份 总数
1 0
2 0
3 0
4 0
5 0
6 100
. 0
. 0
. 0
12 0
WHERE YEAR(consumeDate)=2011
GROUP BY DATE_FORMAT(consumeDate,'%Y-%m')
我想统计1到12月的支出情况
没有数据的月份显示0
请问要如何修改这条sql
月份 总数
1 0
2 0
3 0
4 0
5 0
6 100
. 0
. 0
. 0
12 0
from
(
select 1 as m
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) m left join (SELECT month(consumeDate) as m,SUM(amount) FROM expense
WHERE YEAR(consumeDate)=2011
GROUP BY month(consumeDate) ) a on m.m=a.m注意,你的 DATE_FORMAT(consumeDate,'%Y-%m'),是出不来1,2,3这种效果的,是 2011-01这种,并不符合你例子中的要求。
不过你的例子为空的月份都是null啊。。
并不是1,2,3,4,5,6,7这些月份啊?
怎么处理啊??
能帮我写个完整的sql吗?
谢谢了
from
(
select 1 as m
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) m left join (SELECT month(consumeDate) as m,SUM(amount) as exp FROM expense
WHERE YEAR(consumeDate)=2011
GROUP BY month(consumeDate) ) a on m.m=a.m