查询商品销售统计年报

[lai178]

就是作一张年报表,按那时间作十二个月份查询统计.
请看我附件中的图片,有数据格式与要求

[ACMAIN_CHM]

select MM,
sum(售价价*销售数量) as 售价收入,
sum(成本价*销售数量) as 售价成本价,
sum((售价价-成本价)*销售数量) as 利润
from (
select 售价价,成本价,销售数量,month(销售时间) as MM
from table1
union all
select 0,0,0,1 union all
select 0,0,0,2 union all
select 0,0,0,3 union all
select 0,0,0,4 union all
select 0,0,0,5 union all
select 0,0,0,6 union all
select 0,0,0,7 union all
select 0,0,0,8 union all
select 0,0,0,9 union all
select 0,0,0,10 union all
select 0,0,0,11 union all
select 0,0,0,12
) t
group by MM

[lai178]

谢谢回复,我的这个月份是要通过另一张表来区分的,而不是安正常的月份来区分的

如:

月份 起始时间 结束时间
1月分 2012-01-20 2012-02-30
2月分 2012-03-01 2012-03-15
.....
.....
12月份 2012-12-01 2012-12-30

[Rotel-刘志东]

select ta.MM,
sum(tb.售价价*tb.销售数量) as 售价收入,
sum(tb.成本价*tb销售数量) as 售价成本价,
sum((tb.售价价-tb.成本价)*tb.销售数量) as 利润
from tb 
left join ta on ta.id=tb.id
group by mm