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

来源：用户分享时间：2025/7/25 22:15:26 本文由

loading 分享下载这篇文档手机版

说明：文章内容仅供预览，部分内容可能不全，需要完整文档或者需要复制内容，请下载word后使用。下载word有问题请添加微信号:xxxxxxx或QQ：xxxxxx 处理（尽可能给您提供完整文档），感谢您的支持与谅解。

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

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 页

库存物资管理（参考案例）.doc 将本文的Word文档下载到电脑，方便复制、编辑、收藏和打印