库存物资管理（参考案例）

(

select SDate from Stocking as s

where SDate between '2006-01-02' and '2010-01-02' )

group by w.WName

select w.WName as 商品,sum(OAmount) as 出库数量 from Outbound as o ,Ware as w (

select ODate from Outbound as o

where ODate between '2006-01-02' and '2010-01-02' )

group by w.WName

where w.WNo=o.WNo and ODate in

/*方法三*/

select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select *

where SDate between '2006-01-02' and '2010-01-02' and Stocking.WNo=Ware.WNo )

group by WName

select WName,sum(OAmount) Osum from Outbound,Ware where exists (select *

where ODate between '2006-01-02' and '2010-01-02' and Outbound.WNo=Ware.WNo )

group by WName

第 21 页 共 27 页

十二、 实现分别按年、季度和月对入库商品数量的统计。（金XX负责部

分）

/*方法一： 年：*/

select WName,sum(SAmount) Ssum from Stocking,Ware

where year (SDate)=2005 and Stocking.WNo=Ware.WNo group by WName

/*季度：*/

select WName,sum(SAmount) Ssum from Stocking,Ware

where SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00' and Stocking.WNo=Ware.WNo group by WName

/*月：*/

select WName,sum(SAmount) Ssum from Stocking,Ware

where month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005 group by WName

第 22 页 共 27 页

/*方法二： 年：*/

create view Stocking_SumN as

select WName,sum(SAmount) Ssum

from Stocking JOIN Ware on Stocking.WNo=Ware.WNo where year (SDate)=2005 group by WName

/*月：*/

create view Stocking_SumM as

select WName,sum(SAmount) Ssum

from Stocking JOIN Ware on Stocking.WNo=Ware.WNo where month (SDate)=6 and year (SDate)=2005 group by WName

/*季度：*/

create view Stocking_SumJ as

select WName,sum(SAmount) Ssum

from Stocking JOIN Ware on Stocking.WNo=Ware.WNo

where SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00' group by WName

/*方法三： 年：*/

select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select *

where year (SDate)=2005 and Stocking.WNo=Ware.WNo )

group by WName

第 23 页 共 27 页

/*季度：*/

select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select *

where SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00' and Stocking.WNo=Ware.WNo )

group by WName

/*月：*/

select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select *

where month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005 )

group by WName

十三、 实现分别按年、季度和月对出库商品数量的统计。（谭XX负责部

分）

第 24 页 共 27 页