/*方法三*/
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 页
相关推荐：
loading