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

/*第一种方法：复合条件查询 按年：*/

select WName,sum(OAmount) Osum from Outbound,Ware

where year (ODate)=2010 and Outbound.WNo=Ware.WNo group by WName

/*按季度：*/

select WName,sum(OAmount) Osum from Outbound,Ware

where ODate between '2010-09-01 0:00:00' and '2010-11-30 0:00:00' and Outbound.WNo=Ware.WNo group by WName

/*按月：*/

select WName,sum(OAmount) Osum from Outbound,Ware

where month (ODate)=9 and Outbound.WNo=Ware.WNo and year (ODate)=2010 group by WName

/*第二种方法：视图查询 按年：*/

create view year_outb(商品,出库数量) as select WName,sum(OAmount) Osum

from Outbound as o JOIN Ware as w on o.WNo = w.WNo where year (ODate)=2010 group by WName with check option;

/*按季度：*/

create view jidu_outb(商品,出库数量) as select WName,sum(OAmount) Osum

from Outbound as o JOIN Ware as w on o.WNo = w.WNo

where ODate between '2010-09-01 0:00:00' and '2010-11-30 0:00:00' group by WName

第 25 页 共 27 页

with check option;

/*按月：*/

create view month_outb(商品,出库数量) as select WName,sum(OAmount) Osum

from Outbound as o JOIN Ware as w on o.WNo = w.WNo where month (ODate)=9 and year (ODate)=2010 group by WName with check option;

/*方法三：相关子查询*/ /*按年：*/

select WName as 商品,sum(OAmount) as 出库数量 from Outbound ,Ware where exists (select *

where year (ODate)=2010 and Outbound.WNo=Ware.WNo )

group by WName

/*按季度：*/

select WName as 商品,sum(OAmount) as 出库数量 from Outbound ,Ware where exists (select *

where ODate between '2009-03-1 0:00:00' and '2009-05-30 0:00:00' and Outbound.WNo=Ware.WNo )

group by WName

/*按月：*/

select WName as 商品,sum(OAmount) as 出库数量 from Outbound ,Ware where exists (select *

where month (ODate)=9 and Outbound.WNo=Ware.WNo and year (ODate)=2010 )

第 26 页 共 27 页

group by WName

心得体会：（组员总结，公共部分，由成员分别填写）

第 27 页 共 27 页