select D.仓库号,D.账本号,isnull(sum(B.入库单数),0) 入库单数,isnull(sum(B.入库金额),0) 入库金额 from D
left join (select 物资编码,count(入库单数) 入库单数,sum(入库金额) 入库金额 from B group by 物资编码) B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
left join (select 物资编码,count(入库单数) 入库单数,sum(入库金额) 入库金额 from B group by 物资编码) B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
物资编码 入库数量 入库金额
01-01-001 1000 1000
01-02-002 1 1
01-01-001 1000 1000
select D.仓库号,D.账本号,isnull(count(B.入库单数),0) 入库单数,isnull(sum(B.入库金额),0) 入库金额 from D
left join (select 物资编码,count(物资编码) 入库单数,
sum(入库金额) 入库金额 from B group by 物资编码) B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
物资编码 入库数量 入库金额
01-01-001 1000 1000
01-02-002 1 1
01-01-001 1000 1000
select D.仓库号,D.账本号,isnull(count(B.入库单数),0) 入库单数,isnull(sum(B.入库金额),0) 入库金额 from D
left join (select 物资编码,count(物资编码) 入库单数,
sum(入库金额) 入库金额 from B group by 物资编码) B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
left join B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
left join B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号
原来应该是这样:
select D.仓库号,D.账本号,isnull(sum(B.入库单数),0) 入库单数,isnull(sum(B.入库金额),0) 入库金额 from D
left join (select 物资编码,count(入库单数) 入库单数,sum(入库金额) 入库金额 from B group by 物资编码) B on D.物资编码=B.物资编码 group by D.仓库号,D.账本号不过分都也给大家了