问一个select 语句的写法

[cheyo车油]

表purchInfo
字段bID purQty
表sale
字段bID saleQty

bID为图片编号
purQty为进货数量
saleQty为销售数量
在表两个表的bID字段的值都不是唯一的,
即同一本书可以进货数次,也可以被销售数次.

现在要统计仓库的库存,存入表stock中
表stock有如下两个字段:
bID,stockQty

请问这个select语句要如何写???

[zarge]

Insert Into stock(bID, stockQty)
select x.bID, 进货数量 - 销售数量
from (select bID, sum(purQty) as 进货数量 from purchInfo group by bID) x inner join
(select bID, sum(saleQty) as 销售数量 from sale group by bID) y on x.bID = y.bID

[pengdali]

insert stock (bID,stockQty)
select bid,sum(purqyt) from (
select bid,purqyt from purchinfo
union all
select bid,-saleQty from purchinfo) tem group by bid

[pengdali]

insert stock (bID,stockQty)
select bid,sum(purqyt) from (
select bid,purqyt from purchinfo
union all
select bid,-saleQty from purchinfo) tem group by bid

[wzh1215]

insert into stock(bID,stockQty)
select a.bID,sum(a.purQty)-isnull((select sum(b.saleQty) from sale b where a.bID=b.bID),0) from purchInfo group by bID

[pp_hotel]

在insert之前 是否应delete一下

[xiaohui1748]

insert into stock(bID,stockQty)
values(stock.bID=(select distinct purchInfo.bID from purchInfo),
stock.stockQty=(select purchInfo.purQty from purchInfo group by purchInfo.bID)-(select sale.saleQty from sale group by sale.bID))