【正文】 第 28 頁 共 28 頁。 設(shè)計(jì)表的屬性，和其他同學(xué)一起討論了一些問題，其中包括商品屬性要用到多少個(gè)字符，那些需要用到時(shí)間屬性，是要用DateTime還是smalldatetime因?yàn)樾枰?xì)到分鐘，所以決定用smalldatetime，還有后來插入數(shù)據(jù)時(shí)smalldatetime插入的格式寫錯(cuò)了，也是通過上面一步一步搜索出來的。 and = ) group by WName/*按月：*/select WName as 商品,sum(OAmount) as 出庫數(shù)量 from Outbound ,Ware where exists (select * where month (ODate)=9 and = and year (ODate)=2010 ) group by WName心得體會(huì)： 通過這個(gè)庫存物料管理的數(shù)據(jù)庫，我學(xué)到了很多東西，首先是我必須學(xué)會(huì)去查詢有關(guān)物料管理這個(gè)系統(tǒng)有關(guān)的組成部分，和相關(guān)的需求，只有弄懂了這些才能同組員一起討論得出一個(gè)比較合理的結(jié)果。 and 39。/*方法三：相關(guān)子查詢*//*按年：*/select WName as 商品,sum(OAmount) as 出庫數(shù)量 from Outbound ,Ware where exists (select * where year (ODate)=2010 and = ) group by WName/*按季度：*/select WName as 商品,sum(OAmount) as 出庫數(shù)量 from Outbound ,Ware where exists (select * where ODate between 39。group by WNamewith check option。 and 39。/*按季度：*/create view jidu_outb(商品,出庫數(shù)量)as select WName,sum(OAmount) Osumfrom Outbound as o JOIN Ware as w on = where ODate between 39。20101130 0:00:0039。20100901 0:00:0039。 and = ) group by WName/*月：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where month (SDate)=6 and = and year (SDate)=2005 ) group by WName十三、 實(shí)現(xiàn)分別按年、季度和月對(duì)出庫商品數(shù)量的統(tǒng)計(jì)。 and 39。group by WName/*方法三：年：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where year (SDate)=2005 and = ) group by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where SDate between 39。 and 39。 and =group by WName/*月：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere month (SDate)=6 and = and year (SDate)=2005group by WName/*方法二：年：*/create view Stocking_SumNasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on =where year (SDate)=2005group by WName/*月：*/create view Stocking_SumMasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on =where month (SDate)=6 and year (SDate)=2005group by WName/*季度：*/create view Stocking_SumJasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on =where SDate between 39。 and 39。/*方法一：年：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere year (SDate)=2005 and =group by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Warewhere SDate between 39。2010010239。2006010239。2010010239。2006010239。2010010239。2006010239。2010010239。2006010239。2010010239。2006010239。2010010239。2006010239。十一、 按時(shí)間段查詢商品庫存情況。20100102 0:00:0039。20100102 0:00:0039。20100102 0:00:0039。十、 實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。長城干紅39。select *from Outbound where WNo=(select WNo from Ware where WName=WName)。 /*方法三*/declare WName varchar(32)set WName=39。 and = and = and =/*方法二*/ /* 創(chuàng)建視圖*/CREATE VIEW W_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount,WName)ASSELECT ONo,OAmount,ODate,Buyers,WAmount,FROM Ware,Stock,OutboundWHERE = AND = AND =select ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount from W_S_Owhere WName=39。/*方法一*/ select ONo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Ware where WName=39。 and = and =)。/*方法三相關(guān)子查詢*/select SNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere exists (select * where SDate=39。20060102 00:00:0039。20060102 00:00:0039。 and = and = and = and = and = and = and = and = 八、 實(shí)現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。/*方法三*/ CREATE VIEW LSName(SNo,WName,WNo,DNo,SAmount,WAmount)ASselect SNo,WName,SAmount,WAmountfrom Stock as s,Ware as w,Stocking as si select distinct ,FROM Stock as s,LSName as l,Ware as w,Stocking as siWHERE =39。電冰箱39。電冰箱39。七、 實(shí)現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。小敏39。2010090139。201000139。10000439。3039。)。,39。,30,39。,39。,39。insert into Outbound values(39。小明39。2008120839。201000239。10000239。2839。)。,39。,10,39。,39。,39。insert into Outbound values(39。小紅39。2008120839。201000139。10000539。2639。)。,39。,20,39。,39。,39。insert into Outbound values(39。小蘭39。2008123039。201000339。10000339。2439。)。,39。,40,39。,39。