查询商品销售统计年报
就是作一张年报表,按那时间作十二个月份查询统计.
请看我附件中的图片,有数据格式与要求
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
-
SQL code
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
