版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認領(lǐng)
文檔簡介
1、<p><b> 信息科學(xué)與工程學(xué)院</b></p><p><b> 數(shù)據(jù)庫課程設(shè)計</b></p><p> 題 目: 庫存物資管理系統(tǒng) </p><p> 姓 名: </p><p> 學(xué) 號
2、: </p><p> 班 級: </p><p> 課 程:SQLserver2005數(shù)據(jù)庫技術(shù)</p><p> 任課教師: </p><p> 2011年6月20日<
3、/p><p> 課程設(shè)計任務(wù)書及成績評定</p><p> 進行需求分析,編寫數(shù)據(jù)字典。</p><p> 1、系統(tǒng)功能要求設(shè)計</p><p> ?、?有一個存放商品的倉庫,每天都有商品出庫和入庫。</p><p> ?、?每種商品都有商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格等。</p><p&
4、gt; ?、?入庫時必須填寫入庫單據(jù),單據(jù)包括商品名稱、生產(chǎn)廠家、型號、規(guī)格、入庫數(shù)量、入庫日期、入庫倉庫號、入庫倉庫名稱、送貨人姓名。</p><p> ?、?出庫時必須填寫出庫單據(jù),單據(jù)包括倉庫號、倉庫名稱、商品編號、商品名稱、型號、規(guī)格、出庫數(shù)量、出庫日期、提貨人姓名。</p><p> ⑤ 設(shè)置商品庫存臺帳,商品庫存臺帳是對倉庫中目前庫存的所有商品的明細記錄,商品庫存臺帳包括商品
5、編號、商品名稱、型號、規(guī)格、庫存數(shù)量、庫存日期。每當(dāng)有商品入庫或商品出庫時都應(yīng)該自動修改該臺帳,最后一次修改的是現(xiàn)在的庫存情況。</p><p> ?、?商品的入庫和出庫過程通過庫存臺帳更加清晰條理地顯示出倉庫中商品的庫存數(shù)量和庫存日期等信息,容易對庫存內(nèi)的商品信息進行查詢,增加,修改,刪除等操作。</p><p> ?、?該倉庫的商品型號統(tǒng)一為DA01-DA99格式,規(guī)格為CX100-C
6、X199格式</p><p><b> 主要功能:</b></p><p><b> 商品管理: </b></p><p> 增加商品:修改商品,刪除商品,瀏覽商品</p><p> 增加商品:實現(xiàn)增加商品的詳細資料的功能</p><p> 修改商品:實現(xiàn)修改商品資
7、料的功能</p><p> 刪除商品:實現(xiàn)刪除該商品的所有資料的功能</p><p> 瀏覽商品:實現(xiàn)瀏覽所有商品的功能</p><p><b> ② 庫存管理:</b></p><p> 實現(xiàn)商品的入庫,在商品入庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。</p><p> 實
8、現(xiàn)商品的出庫,在商品出庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。</p><p> 實現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。</p><p> 實現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。</p><p> 實現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。</p><p> 實現(xiàn)按出庫日期查詢商品的出庫情況及目前
9、的庫存量。</p><p> 按時間段查詢商品庫存情況。</p><p> 實現(xiàn)分別按年、季度和月對入庫商品數(shù)量的統(tǒng)計。</p><p> 實現(xiàn)分別按年、季度和月對出庫商品數(shù)量的統(tǒng)計。</p><p><b> 2、系統(tǒng)模塊設(shè)計</b></p><p> 庫存物資管理大體可以分為以下3大
10、模塊,如下圖所示:首先是商品入庫模塊,該模塊主要就是描述把采購回來的商品,分類別的放置到指定的倉庫中去,然后是商品出庫模塊,該模塊主要描述從指定的倉庫中拿出商品;最后時庫存模塊,這個模塊主要是記錄商品的庫存數(shù)量變化。</p><p><b> 3、數(shù)據(jù)字典</b></p><p> 設(shè)計完整的E-R圖。</p><p> 進行數(shù)據(jù)庫的邏輯
11、設(shè)計。</p><p><b> 關(guān)系模式如下:</b></p><p> 商品(商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格)</p><p><b> 商品編號為主鍵</b></p><p> 倉庫(倉庫號、倉庫名稱)</p><p><b> 倉庫號為主
12、鍵</b></p><p> 庫存(商品編號,倉庫號,庫存數(shù)量)</p><p> (商品編號,倉庫號)為主鍵,同時也分別為外鍵</p><p> 入庫(入庫號,商品編號,倉庫號,入庫數(shù)量,入庫日期,送貨人姓名)</p><p> (入庫號)為主鍵,(商品編號,倉庫號)為外鍵</p><p> 出
13、庫(出庫號,商品編號,倉庫號,出庫數(shù)量,出庫日期,提貨人姓名)</p><p> (出庫號)為主鍵,(商品編號,倉庫號)為外鍵</p><p> 完成物理數(shù)據(jù)庫的設(shè)計,包括數(shù)據(jù)庫、表、索引、視圖、完整性約束的物理設(shè)計。</p><p><b> 創(chuàng)建數(shù)據(jù)庫</b></p><p> create database
14、 Material_DB</p><p><b> 創(chuàng)建商品表</b></p><p> use Material_DB</p><p> create table Ware( /*定義商品表*/</p><p> WNo char(10) NOT NULL primary key,</p><
15、;p> WName varchar(50) NOT NULL,</p><p> WFactory varchar(50) NOT NULL,</p><p> WType varchar(20) NOT NULL,</p><p> Spec varchar(20) NOT NULL</p><p><b> )&
16、lt;/b></p><p> use Material_DB /*插入商品信息*/</p><p> insert into Ware</p><p> values ('100000','毛巾','新家園','DA11','CX100');</p><
17、;p> insert into Ware</p><p> values ('100001','臺燈','新家園','DA35','CX110');</p><p> insert into Ware</p><p> values ('100002',
18、39;電冰箱','海爾','DA61','CX900');</p><p> insert into Ware</p><p> values ('100003','電視','東芝','DA52','CX901');</p><p&
19、gt; insert into Ware</p><p> values ('100004','長城干紅','中糧華夏','DA25','CX108');</p><p> insert into Ware</p><p> values ('100005',
20、39;筆記本','東芝','DA24','CX781'); </p><p><b> 創(chuàng)建倉庫表</b></p><p> use Material_DB</p><p> create table Depot( /*定義倉庫表*/</p><p> DN
21、o char(10) NOT NULL primary key,</p><p> DName varchar(50) NOT NULL</p><p><b> )</b></p><p> use Material_DB /*插入倉庫信息*/</p><p> insert into Depot</p
22、><p> values ('2010001','勝昌');</p><p> insert into Depot</p><p> values ('2010002','德隆');</p><p> insert into Depot</p><p>
23、; values ('2010003','豪友');</p><p><b> 創(chuàng)建庫存表</b></p><p> use Material_DB</p><p> create table Stock ( /*定義庫存關(guān)系*/</p><p> WNo char(10) NO
24、T NULL,</p><p> DNo char(10) NOT NULL,</p><p> --WDate smalldatetime NOT NULL,</p><p> WAmount int NOT NULL,</p><p> primary key(WNo,Dno),</p><p> for
25、eign key (WNo) references Ware(WNo),</p><p> foreign key (DNo) references Depot(DNo)</p><p><b> )</b></p><p><b> 創(chuàng)建入庫表</b></p><p> use Mater
26、ial_DB</p><p> create table Stocking( /*定義入庫關(guān)系*/</p><p> SNo char(10) NOT NULL primary key,</p><p> WNo char(10) NOT NULL,</p><p> DNo char(10) NOT NULL,</p>
27、<p> foreign key (WNo) references Ware(WNo),</p><p> foreign key (DNo) references Depot(DNo),</p><p> SAmount Int NOT NULL,</p><p> SDate smalldatetime NOT NULL,</p>
28、;<p> Supplier varchar(50) NOT NULL</p><p><b> )</b></p><p><b> 創(chuàng)建出庫表</b></p><p> use Material_DB</p><p> create table Outbound( /*
29、定義出庫關(guān)系*/</p><p> ONo char(10)NOT NULL primary key,</p><p> WNo char(10)NOT NULL ,</p><p> foreign key (WNo)references Ware(WNo),</p><p> DNo char(10) NOT NULL,</
30、p><p> foreign key (DNo) references Depot(DNo),</p><p> OAmount int NOT NULl ,</p><p> ODate smalldatetime NOT NULL,</p><p> Buyers varchar(50) NOT NULL</p><
31、;p><b> )</b></p><p><b> 完整約束的說明:</b></p><p> 商品Ware的WNo商品編號要求在100000~999999之間,WName不能取空;</p><p> 倉庫Depot的DNo倉庫編號要求在2010001~2010999之間,DName不能取空;</p&
32、gt;<p> 該倉庫的商品型號統(tǒng)一為DA01-DA99格式,規(guī)格為CX100-CX199格式。</p><p> use Material_DB /*定義表Ware唯一性的約束條件*/</p><p> alter Table Ware add unique(WName,WFactory,WType,Spec);</p><p> alter
33、 Table Ware /*定義WType的完整性約束條件:商品型號統(tǒng)一為DA01-DA99格式*/</p><p> add constraint C2 check(WType like '[D][A][0-9][1-9]');</p><p> alter Table Ware /*定義Spec的完整性約束條件:規(guī)格為CX100-CX199格式*/</p&
34、gt;<p> add constraint C3 check(Spec like '[C][X][1-9][0-9][0-9]');</p><p> use Material_DB</p><p> alter table Ware /*添加對表Ware的WNo屬性的完整性約束,要求在~9999999999之間*/</p><p&
35、gt; add constraint C4 check(WNo like'[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]');</p><p> alter table Depot</p><p> add constraint C5 check(DNo like'[2][0][1][0][0-9][0-9
36、][0-9]');</p><p> use Material_DB</p><p> alter table Ware</p><p> drop constraint C4;</p><p> use Material_DB</p><p> alter table Ware /*添加對表Ware
37、的WNo屬性的完整性約束,要求在~999999之間*/</p><p> add constraint C4 check(WNo like'[1-9][0-9][0-9][0-9][0-9][0-9]');</p><p><b> 創(chuàng)建視圖</b></p><p> 通過WLST(提取“物料視圖”每個字的頭字母)視圖來瀏
38、覽庫存中相同編號商品的信息,其中包括:商品編號WNo、商品名WName、商品廠家WFactory、商品型號WType、商品規(guī)格Spec、商品數(shù)量SNum、</p><p> create view VWname(商品編號,商品名稱, 庫存數(shù)量)--視圖</p><p><b> AS</b></p><p> SELECT Stock.W
39、No,Ware.WName,sum(Stock.WAmount)</p><p> from Ware,Stock</p><p> where Stock.WNo=Ware.WNo </p><p> group by Stock.WNo,Ware.WName;</p><p> 實現(xiàn)商品的入庫,在商品入庫時通過觸發(fā)器或存儲過程同時
40、完成商品庫存臺帳的更新。</p><p><b> 入庫表的觸發(fā)器</b></p><p> create trigger stocking_into on stocking --這是入庫表的觸發(fā)器</p><p> after insert </p><p><b> as</b><
41、/p><p><b> begin </b></p><p> declare @a char(10),@b char(10),@d int;</p><p> select @a=i.DNo,@b=i.WNo,@d=i.SAmount</p><p> from inserted as i;</p>
42、<p> if(select Stock.WNo from Stock where Stock.WNo=@b and Stock.DNo=@a) is not null</p><p><b> begin</b></p><p> update Stock</p><p> set Stock.WAmount=Stock.W
43、Amount+@d</p><p> where Stock.WNo=@b and Stock.DNo=@a;</p><p><b> return;</b></p><p><b> end</b></p><p> insert into stock values(@b,@a,@d);
44、</p><p><b> end</b></p><p> insert into stocking values('100','100005','2010001',200,'2006-01-02','小明');</p><p> insert into s
45、tocking values('101','100005','2010003',200,'2006-01-02','小蘭');</p><p> insert into stocking values('102','100001','2010003',200,'2006-01-
46、02','小東');</p><p> insert into stocking values('103','100002','2010001',200,'2006-01-02','小紅');</p><p> insert into stocking values('104
47、','100003','2010001',200,'2007-01-14','小風(fēng)');</p><p> insert into stocking values('105','100005','2010002',200,'2006-01-25','小明');&
48、lt;/p><p> insert into stocking values('106','100000','2010002',200,'2006-02-02','小明');</p><p> insert into stocking values('107','100001',
49、'2010001',200,'2005-03-02','小東');</p><p> insert into stocking values('108','100002','2010002',200,'2007-04-02','小紅');</p><p> i
50、nsert into stocking values('109','100003','2010003',200,'2006-04-02','小風(fēng)');</p><p> insert into stocking values('110','100004','2010001',200,&
51、#39;2006-06-09','小敏');</p><p> insert into stocking values('111','100004','2010002',200,'2005-06-02','小風(fēng)');</p><p> insert into stocking val
52、ues('112','100005','2010001',200,'2005-06-02','小青');</p><p> insert into stocking values('113','100000','2010003',200,'2005-06-02',
53、9;小明');</p><p> insert into stocking values('114','100001','2010001',200,'2005-06-02','小東');</p><p> insert into stocking values('115','
54、100002','2010002',200,'2007-07-02','小紅');</p><p> insert into stocking values('116','100003','2010003',200,'2006-08-02','小風(fēng)');</p>
55、<p> insert into stocking values('117','100000','2010001',200,'2006-08-02','小明');</p><p> insert into stocking values('118','100001','201000
56、1',200,'2005-09-04','小東');</p><p> insert into stocking values('119','100002','2010002',200,'2005-06-02','小紅');</p><p> insert into
57、stocking values('120','100003','2010003',200,'2005-09-11','小風(fēng)');</p><p> insert into stocking values('121','100002','2010002',200,'2005-09
58、-01','小明');</p><p> insert into stocking values('122','100003','2010002',200,'2005-07-08','小敏');</p><p> insert into stocking values('12
59、3','100002','2010003',200,'2005-04-11','小敏');</p><p> insert into stocking values('124','100001','2010001',200,'2007-07-08','小紅');
60、</p><p> insert into stocking values('125','100001','2010003',200,'2005-08-21','小青');</p><p> insert into stocking values('126','100005'
61、,'2010001',200,'2005-07-08','小青');</p><p> insert into stocking values('127','100003','2010003',200,'2005-09-01','小蘭');</p><p>
62、insert into stocking values('128','100002','2010002',200,'2007-07-08','小蘭');</p><p> insert into stocking values('129','100004','2010001',200,
63、'2007-07-08','小蘭');</p><p> insert into stocking values('130','100005','2010002',200,'2007-07-08','小紅');</p><p> 實現(xiàn)商品的出庫,在商品出庫時通過觸發(fā)器或存儲過
64、程同時完成商品庫存臺帳的更新。</p><p><b> 出庫表觸發(fā)器</b></p><p> create trigger outbound_d on Outbound --這是出庫表的觸發(fā)器</p><p> after insert</p><p><b> as </b></
65、p><p><b> begin </b></p><p> declare @a char(10),@b char(10),@d int ;</p><p> select @a=i.DNo,@b=i.WNo,@d=i.OAmount</p><p> from inserted as i</p>&
66、lt;p> if(select s.WNo from Stock as s where s.DNo=@a and s.WNo=@b ) is not null</p><p><b> begin</b></p><p> if(select s.WAmount from Stock as s ,inserted where s.WAmount>=i
67、nserted.OAmount </p><p> and s.WNo=inserted.WNo and s.DNo=inserted.DNo) is not null</p><p><b> begin</b></p><p> update Stock </p><p> set Stock.WAmount
68、=Stock.WAmount-@d</p><p> where Stock.DNo=@a and Stock.WNo=@b</p><p><b> end</b></p><p><b> else</b></p><p><b> begin</b></p&
69、gt;<p> print '庫存量不夠'</p><p><b> rollback</b></p><p><b> end</b></p><p><b> end</b></p><p><b> else</b&
70、gt;</p><p><b> begin</b></p><p> print '庫存中沒有這種產(chǎn)品'</p><p><b> rollback</b></p><p><b> end</b></p><p><b&
71、gt; end</b></p><p> insert into Outbound values('1','100005','2010001',50,'2010-01-02','小紅');</p><p> insert into Outbound values('2',
72、9;100005','2010002',30,'2010-01-02','小紅');</p><p> insert into Outbound values('3','100001','2010003',50,'2010-01-02','小風(fēng)');</p>&l
73、t;p> insert into Outbound values('4','100005','2010001',30,'2010-01-02','小紅');</p><p> insert into Outbound values('5','100002','2010002',
74、20,'2010-02-08','小風(fēng)');</p><p> insert into Outbound values('6','100003','2010003',50,'2010-03-09','小紅');</p><p> insert into Outbound va
75、lues('7','100000','2010002',30,'2009-04-15','小風(fēng)');</p><p> insert into Outbound values('8','100004','2010002',50,'2009-04-15','小風(fēng)&
76、#39;);</p><p> insert into Outbound values('9','100005','2010001',30,'2010-06-02','小紅');</p><p> insert into Outbound values('10','100005
77、9;,'2010002',50,'2009-04-15','小紅');</p><p> insert into Outbound values('11','100001','2010003',50,'2010-06-03','小風(fēng)');</p><p> i
78、nsert into Outbound values('12','100005','2010001',30,'2009-04-15','小紅');</p><p> insert into Outbound values('13','100002','2010002',20,'
79、2010-07-05','小風(fēng)');</p><p> insert into Outbound values('14','100003','2010003',50,'2009-07-05','小紅');</p><p> insert into Outbound values(
80、39;15','100000','2010002',40,'2010-07-05','小風(fēng)');</p><p> insert into Outbound values('16','100004','2010002',50,'2009-07-05','小風(fēng)')
81、;</p><p> insert into Outbound values('17','100005','2010002',20,'2008-09-01','小明');</p><p> insert into Outbound values('18','100001',&
82、#39;2010003',30,'2010-09-01','小敏');</p><p> insert into Outbound values('19','100000','2010002',40,'2010-09-01','小敏');</p><p> inser
83、t into Outbound values('20','100002','2010002',50,'2008-09-01','小紅');</p><p> insert into Outbound values('21','100000','2010002',20,'2010
84、-08-21','小青');</p><p> insert into Outbound values('22','100003','2010001',30,'2008-09-01','小青');</p><p> insert into Outbound values('2
85、3','100002','2010002',40,'2008-09-01','小蘭');</p><p> insert into Outbound values('24','100003','2010003',50,'2008-12-30','小蘭');<
86、;/p><p> insert into Outbound values('25','100004','2010002',20,'2009-12-30','小蘭');</p><p> insert into Outbound values('26','100005','
87、2010001',40,'2008-12-08','小紅');</p><p> insert into Outbound values('27','100001','2010003',10,'2010-12-30','小敏');</p><p> insert in
88、to Outbound values('28','100002','2010002',30,'2008-12-08','小明');</p><p> insert into Outbound values('29','100003','2010003',30,'2009-12-
89、18','小紅');</p><p> insert into Outbound values('30','100004','2010001',40,'2010-09-01','小敏');</p><p> 實現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。</p>&l
90、t;p><b> /*方法一*/</b></p><p> select SNo,WName,s.WNo,s.DNo,SAmount,WAmount </p><p> from Stock as s,Ware as w,Stocking as si</p><p> where WName='電冰箱' and s
91、.WNo=w.WNo and w.WNo=si.WNo and s.DNo=si.DNo</p><p><b> /*方法二*/</b></p><p> declare @WName varchar(32)</p><p> SET @WName ='電冰箱'</p><p><b>
92、; SELECT * </b></p><p> FROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName = @WName);</p><p><b> /*方法三*/</b></p><p> CREATE VIEW LSName(SNo,WName,WN
93、o,DNo,SAmount,WAmount)</p><p><b> AS</b></p><p> select SNo,WName,s.WNo,s.DNo,SAmount,WAmount</p><p> from Stock as s,Ware as w,Stocking as si </p><p>
94、select distinct si.Sno,l.WName,si.SAmount,s.WAmount</p><p> FROM Stock as s,LSName as l,Ware as w,Stocking as si</p><p> WHERE l.WName='電冰箱' and si.Sno=l.Sno and w.WName=l.WName and &l
95、t;/p><p> s.WNo=si.WNo and si.WNo=w.WNo and w.WNo=l.WNo and si.DNo=l.DNo </p><p> and si.SAmount=l.SAmount and s.WAmount=l.WAmount </p><p> 實現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。</p><
96、p><b> /*方法一</b></p><p><b> 復(fù)合條件查詢*/</b></p><p> select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount </p><p> from Stock as s,Stocking</p><
97、;p> where SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo </p><p> and s.DNo=Stocking.DNo</p><p><b> /*方法二</b></p><p><b> 建立視圖查詢*/</b></
98、p><p> create view WA_Stocking(SNo,WNo,DNo,SAmount,SDate,Supplier,WAmount)</p><p> as select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount </p><p> from Stock as s JOIN Stocking on
99、 s.WNo=Stocking.WNo </p><p> and s.DNo=Stocking.DNo and SDate='2006-01-02 00:00:00'</p><p> with check option;</p><p><b> /*方法三</b></p><p><b&
100、gt; 相關(guān)子查詢*/</b></p><p> select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount </p><p> from Stock as s,Stocking</p><p> where exists (select * where SDate='2006-01-0
101、2 00:00:00' and s.WNo=Stocking.WNo </p><p> and s.DNo=Stocking.DNo);</p><p> 實現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。</p><p><b> /*方法一*/</b></p><p> select ONo,s.WN
102、o,s.DNo,OAmount,WName,ODate,Buyers,WAmount </p><p> from Stock as s,Outbound,Ware</p><p> where WName='長城干紅' and Ware.Wno=s.Wno and s.Wno=Outbound.Wno </p><p> and s.DNo=
103、Outbound.DNo</p><p><b> /*方法二*/</b></p><p> /* 創(chuàng)建視圖*/</p><p> CREATE VIEW W_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount,WName)</p><p><b> AS<
104、;/b></p><p> SELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WName</p><p> FROM Ware,Stock,Outbound</p><p> WHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outboun
105、d.DNo AND Ware.WNo=Stock.Wno</p><p> select ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount </p><p> from W_S_O</p><p> where WName='長城干紅' </p><p><b>
106、/*方法三*/</b></p><p> declare @WName varchar(32)</p><p> set @WName='長城干紅'</p><p><b> select *</b></p><p> from Outbound </p><p&g
107、t; where WNo=(select WNo </p><p> from Ware </p><p> where WName=@WName);</p><p><b> /*方法四*/</b></p><p> select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buy
108、ers,WAmount </p><p> from Stock as s,Outbound,Ware</p><p> where exists </p><p><b> (select *</b></p><p> where WName='長城干紅' and s.WNo=Outbound.
109、WNo </p><p> and s.DNo=Outbound.DNo and Ware.Wno=s.Wno);</p><p> 實現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。</p><p><b> /*方法一*/</b></p><p> select ONo,s.WNo,s.DNo,OAmount,
110、ODate,Buyers,WAmount </p><p> from Stock as s,Outbound</p><p> where ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo </p><p> and s.DNo=Outbound.DNo</p><p>
111、;<b> /*方法二*/</b></p><p> /* 創(chuàng)建視圖*/</p><p> CREATE VIEW SO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)</p><p><b> AS</b></p><p> SELECT ONo,
112、Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount</p><p> FROM Stock,Outbound</p><p> WHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo </p><p> select ONo,WNo,DNo,OAmount,O
113、Date,Buyers,WAmount</p><p><b> from SO</b></p><p> where ODate='2010-01-02 0:00:00'</p><p><b> /*方法三*/</b></p><p> select ONo,s.WNo,
114、s.DNo,OAmount,ODate,Buyers,WAmount </p><p> from Stock as s,Outbound</p><p> where exists </p><p><b> (select *</b></p><p> where ODate='2010-01-02
115、0:00:00' and s.WNo=Outbound.WNo </p><p> and s.DNo=Outbound.DNo);</p><p> 按時間段查詢商品庫存情況。</p><p> /*時間在-01-02和-01-02之間的入庫出庫情況*/</p><p><b> /*方法一*/</b>
116、</p><p> select w.WName as 商品,sum(SAmount) as 入庫數(shù)量</p><p> from Stocking as s ,Ware as w </p><p> where w.WNo=s.WNo and SDate between '2006-01-02' and '2010-01-02'
117、;</p><p> group by w.WName</p><p> select w.WName as 商品,sum(OAmount) as 出庫數(shù)量</p><p> from Outbound as o ,Ware as w </p><p> where w.WNo=o.WNo and ODate between
118、9;2006-01-02' and '2010-01-02'</p><p> group by w.WName</p><p><b> /*方法二*/</b></p><p> select w.WName as 商品,sum(SAmount) as 入庫數(shù)量</p><p> fro
119、m Stocking as s ,Ware as w </p><p> where w.WNo=s.WNo and SDate in</p><p><b> (</b></p><p> select SDate</p><p> from Stocking as s</p><p>
120、 where SDate between '2006-01-02' and '2010-01-02'</p><p><b> )</b></p><p> group by w.WName</p><p> select w.WName as 商品,sum(OAmount) as 出庫數(shù)量</p&
121、gt;<p> from Outbound as o ,Ware as w </p><p> where w.WNo=o.WNo and ODate in</p><p><b> (</b></p><p> select ODate</p><p> from Outbound as o&
122、lt;/p><p> where ODate between '2006-01-02' and '2010-01-02'</p><p><b> )</b></p><p> group by w.WName</p><p><b> /*方法三*/</b>&l
123、t;/p><p> select WName,sum(SAmount) Ssum</p><p> from Stocking,Ware</p><p> where exists</p><p><b> (select *</b></p><p> where SDate between
124、 '2006-01-02' and '2010-01-02' and Stocking.WNo=Ware.WNo</p><p><b> )</b></p><p> group by WName</p><p> select WName,sum(OAmount) Osum</p><
125、;p> from Outbound,Ware</p><p> where exists</p><p><b> (select *</b></p><p> where ODate between '2006-01-02' and '2010-01-02' and Outbound.WNo=Wa
126、re.WNo</p><p><b> )</b></p><p> group by WName</p><p> 實現(xiàn)分別按年、季度和月對入庫商品數(shù)量的統(tǒng)計。</p><p><b> /*方法一:</b></p><p><b> 年:*/</
127、b></p><p> select WName,sum(SAmount) Ssum</p><p> from Stocking,Ware</p><p> where year (SDate)=2005 and Stocking.WNo=Ware.WNo</p><p> group by WName</p>
128、<p><b> /*季度:*/</b></p><p> select WName,sum(SAmount) Ssum </p><p> from Stocking,Ware</p><p> where SDate between '2005-3-1 0:00:00' and '2005-5-3
129、1 0:00:00' </p><p> and Stocking.WNo=Ware.WNo</p><p> group by WName</p><p><b> /*月:*/</b></p><p> select WName,sum(SAmount) Ssum</p><p&g
130、t; from Stocking,Ware</p><p> where month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005</p><p> group by WName</p><p><b> /*方法二:</b></p><p>
131、<b> 年:*/</b></p><p> create view Stocking_SumN</p><p><b> as</b></p><p> select WName,sum(SAmount) Ssum</p><p> from Stocking JOIN Ware on
132、Stocking.WNo=Ware.WNo</p><p> where year (SDate)=2005</p><p> group by WName</p><p><b> /*月:*/</b></p><p> create view Stocking_SumM</p><p>
133、;<b> as</b></p><p> select WName,sum(SAmount) Ssum</p><p> from Stocking JOIN Ware on Stocking.WNo=Ware.WNo</p><p> where month (SDate)=6 and year (SDate)=2005</p
134、><p> group by WName</p><p><b> /*季度:*/</b></p><p> create view Stocking_SumJ</p><p><b> as</b></p><p> select WName,sum(SAmount)
135、 Ssum</p><p> from Stocking JOIN Ware on Stocking.WNo=Ware.WNo</p><p> where SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00'</p><p> group by WName</
136、p><p><b> /*方法三:</b></p><p><b> 年:*/</b></p><p> select WName,sum(SAmount) Ssum</p><p> from Stocking,Ware</p><p> where exists&l
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 眾賞文庫僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 數(shù)據(jù)庫課程設(shè)計—物資管理系統(tǒng)
- 數(shù)據(jù)庫課程設(shè)計—物資管理系統(tǒng)
- 數(shù)據(jù)庫課程設(shè)計-商場庫存管理系統(tǒng)
- 倉儲物資管理系統(tǒng)-數(shù)據(jù)庫課程設(shè)計
- 數(shù)據(jù)庫原理課程設(shè)計-- 庫存管理系統(tǒng)
- 庫存管理系統(tǒng)數(shù)據(jù)庫課程設(shè)計---超市收銀管理系統(tǒng)
- 數(shù)據(jù)庫超市商品庫存管理系統(tǒng)課程設(shè)計報告
- 數(shù)據(jù)庫課程設(shè)計--數(shù)據(jù)庫設(shè)計—公司管理系統(tǒng)
- 數(shù)據(jù)庫課程設(shè)計---機房管理系統(tǒng)數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫課程設(shè)計---圖書管理系統(tǒng)數(shù)據(jù)庫管理系統(tǒng)
- 數(shù)據(jù)庫課程設(shè)計--工資管理系統(tǒng)數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫課程設(shè)計-酒店管理系統(tǒng)課程設(shè)計
- 數(shù)據(jù)庫原理課程設(shè)計---教室管理系統(tǒng)數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫課程設(shè)計--bbs系統(tǒng)數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫課程設(shè)計論文---汽車銷售管理系統(tǒng)數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫課程設(shè)計--超市會員管理系統(tǒng)的數(shù)據(jù)庫設(shè)計
- 數(shù)據(jù)庫原理課程設(shè)計---圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計
- 物資庫存管理系統(tǒng)課程設(shè)計
- 數(shù)據(jù)庫課程設(shè)計-學(xué)生管理系統(tǒng)
- 數(shù)據(jù)庫課程設(shè)計-薪資管理系統(tǒng)
評論
0/150
提交評論