第一范文网 - 专业文章范例文档资料分享平台

库存物资管理(参考案例)

来源:用户分享 时间:2025/7/24 9:04:26 本文由loading 分享 下载这篇文档手机版
说明:文章内容仅供预览,部分内容可能不全,需要完整文档或者需要复制内容,请下载word后使用。下载word有问题请添加微信号:xxxxxxx或QQ:xxxxxx 处理(尽可能给您提供完整文档),感谢您的支持与谅解。

/*方法三*/

CREATE VIEW LSName(SNo,WName,WNo,DNo,SAmount,WAmount) AS

select SNo,WName,s.WNo,s.DNo,SAmount,WAmount from Stock as s,Ware as w,Stocking as si

select distinct si.Sno,l.WName,si.SAmount,s.WAmount FROM Stock as s,LSName as l,Ware as w,Stocking as si

WHERE l.WName='电冰箱' and si.Sno=l.Sno and w.WName=l.WName and s.WNo=si.WNo and si.WNo=w.WNo and w.WNo=l.WNo and si.DNo=l.DNo and si.SAmount=l.SAmount and s.WAmount=l.WAmount

八、 实现按入库日期查询商品的入库情况及目前的库存量。(田XX负责部

分)

/*方法一 复合条件查询*/

select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stocking

where SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo and s.DNo=Stocking.DNo

/*方法二 建立视图查询*/

create view WA_Stocking(SNo,WNo,DNo,SAmount,SDate,Supplier,WAmount) as select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s JOIN Stocking on s.WNo=Stocking.WNo

第 17 页 共 27 页

and s.DNo=Stocking.DNo and SDate='2006-01-02 00:00:00' with check option;

/*方法三 相关子查询*/

select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stocking

where exists (select * where SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo and s.DNo=Stocking.DNo);

九、 实现按商品名称查询商品的出库情况及目前的库存量。(陈XX负责部

分)

/*方法一*/

select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Ware

where WName='长城干红' and Ware.Wno=s.Wno and s.Wno=Outbound.Wno and s.DNo=Outbound.DNo

/*方法二*/

AS

SELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WName FROM Ware,Stock,Outbound

WHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo AND Ware.WNo=Stock.Wno

select ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount from W_S_O

where WName='长城干红'

第 18 页 共 27 页

/* 创建视图*/

CREATE VIEW W_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount,WName)

/*方法三*/

declare @WName varchar(32) set @WName='长城干红' select * from Outbound

where WNo=(select WNo from Ware

where WName=@WName);

/*方法四*/

select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount

from Stock as s,Outbound,Ware where exists

(select *

where WName='长城干红' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo and Ware.Wno=s.Wno);

十、 实现按出库日期查询商品的出库情况及目前的库存量。(关XX负责部

分)

/*方法一*/

select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount

from Stock as s,Outbound

where ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo

/*方法二*/

/* 创建视图*/

CREATE VIEW SO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)

第 19 页 共 27 页

AS

SELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount FROM Stock,Outbound

WHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo select ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount from SO

where ODate='2010-01-02 0:00:00'

/*方法三*/

select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount

from Stock as s,Outbound where exists

(select *

where ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo

and s.DNo=Outbound.DNo);

十一、 按时间段查询商品库存情况。(殷XX负责部分)

/*时间在-01-02和-01-02之间的入库出库情况*/ /*方法一*/

/*方法二*/

select w.WName as 商品,sum(SAmount) as 入库数量 from Stocking as s ,Ware as w

where w.WNo=s.WNo and SDate in

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

where w.WNo=o.WNo and ODate between '2006-01-02' and '2010-01-02' group by w.WName

select w.WName as 商品,sum(SAmount) as 入库数量 from Stocking as s ,Ware as w

where w.WNo=s.WNo and SDate between '2006-01-02' and '2010-01-02' group by w.WName

第 20 页 共 27 页

搜索更多关于: 库存物资管理(参考案例) 的文档
库存物资管理(参考案例).doc 将本文的Word文档下载到电脑,方便复制、编辑、收藏和打印
本文链接:https://www.diyifanwen.net/c4nkg115afi38gut0yjnu_5.html(转载请注明文章来源)
热门推荐
Copyright © 2012-2023 第一范文网 版权所有 免责声明 | 联系我们
声明 :本网站尊重并保护知识产权,根据《信息网络传播权保护条例》,如果我们转载的作品侵犯了您的权利,请在一个月内通知我们,我们会及时删除。
客服QQ:xxxxxx 邮箱:xxxxxx@qq.com
渝ICP备2023013149号
Top