这个语句也可以!
select a.编号,a.名称,a.入库数量,a.入库金额/a.入库数量 as 进价,a.入库金额,b.出库数量,b.出库金额/b.出库数量 as 售价,b.出库金额 from (select 编号,名称,sum(入库数量) as 入库数量,sum(入库金额) as 入库金额 from [表1$] group by 编号,名称) a left join (select 编号,名称,sum(出库数量) as 出库数量,sum(出库金额) as 出库金额 from [表2$] group by 编号,名称) b on a.编号 = b.编号 and a.名称 = b.名称
select D.编号,D.名称,D.入库数量,D.进价,D.入库金额,C.出库数量,C.售价,C.出库金额 from (select A.编号,A.名称,sum(A.入库数量) as 入库数量,avg(A.进价) as 进价,sum(A.入库金额) as 入库金额 from [表1$] A group by A.编号,A.名称) D left outer join (select B.编号,B.名称,sum(B.出库数量) as 出库数量,avg(B.售价) as 售价,sum(B.出库金额) as 出库金额 from [表2$] B group by B.编号,B.名称) C on D.编号=C.编号 and D.名称=C.名称
謝謝無心版主的回覆,我这里也给一个吧!!
select 编号,名称,sum(入库数量) as 入库数量,max(进价) as 进价,sum(入库金额) as 入库金额,sum(出库数量) as 出库数量,max(售价) as 售价,sum(出库金额) as 出库金额 from (select 编号,名称,入库数量,进价,入库金额,0 as 出库数量,0 as 售价,0 as 出库金额 from [表1$]
union all
select 编号,名称,0,0,0,出库数量,售价,出库金额 from [表2$]) group by 编号,名称