入库汇总

create table cgb
(
ddh varchar(20), --采购单号
dm varchar(50), --商品编码
dgs float, --订购表

)
INSERT cgb SELECT 'aa','dm1',1
UNION ALL SELECT 'aa','dm2',4
UNION ALL SELECT 'aa','dm3',20
UNION ALL SELECT 'bb','dm3',20
GO

--入库表
create table jhmxb
(
ddh varchar(100),--采购单号
dm varchar(50),
sl float, ---入库数
rq date --日期

)

INSERT jhmxb SELECT 'aa','dm1',8,'2011-05-12'
UNION ALL SELECT 'aa','dm1',4,'2011-05-19'
UNION ALL SELECT 'aa','dm3',20,'2011-05-12'
GO

 

 

select a.ddh as 订单号,a.dm as 编码,SUM(a.dgs) as 采购数,isnull(sum(b.sl),0) as 入库数 ,max(b.rq) as入库日期 from cgb a left join jhmxb b on a.ddh=b.ddh and a.dm=b.dm group by a.ddh,a.dm ;

 

select a.ddh,a.dm,isnull(SUM(a.dgs),0) as 采购数, isnull(sum(b.sl),0) as 入库数,max(rq) as 入库日期 from cgb a left join jhmxb b on a.ddh=b.ddh and a.dm=b.dm group by a.ddh,a.dm