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

/*方法三*/

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 页