现有三张业务表:第一张为商品档案表(包括商品编号、商品名称等字段),第二张为销售明细表(包括商品编号、销售日期、数量、单价等字段),第三张为采购明细表(包括商品编号、采购日期、数量、单价等字段)。其数据库为Access97,后台开发工具为Delphi4。现要求求出某起始日期A至终止日期B之间的销售额达前20位的品种,同时还要按这20个品种分别求出日期A~B之间的购进数量和金额(如果没有购时,数量金额全为0),而且前面求出的销售和采购数据要在同一个Dbgrid中显示(格式:药品编号、名称、销售数量、销售金额、采购数量、采购金额)。请高手赐教。
select 商品编号,sum(数量) as 销售数量,sum(数量*单价) as 销售金额,'0' as 采购数量,‘0’ as 采购金额
from 销售明细表
where 销售日期 between A and B
group by 商品编号
union
select 商品编号,'0' as 销售数量,'0' as 销售金额,sum(数量) as 采购数量,sum(数量*单价) as 采购金额
from 采购明细表
where 采购日期 between A and B
group by 商品编号) A,商品档案表 B
where A.商品编号=B.商品编号
group by A.商品编号,B.商品名称
select 商品编号,sum(数量) as 销售数量,sum(数量*单价) as 销售金额,'0' as 采购数量,‘0’ as 采购金额
from 销售明细表
where 销售日期 between A and B
group by 商品编号
union
select 商品编号,'0' as 销售数量,'0' as 销售金额,sum(数量) as 采购数量,sum(数量*单价) as 采购金额
from 采购明细表
where 采购日期 between A and B
group by 商品编号) A,商品档案表 B
where A.商品编号=B.商品编号
group by A.商品编号,B.商品名称
oder by 销售金额