數(shù)據(jù)庫課程設(shè)計商品信息管理系統(tǒng)樣本

課程設(shè)計課程名稱：數(shù)據(jù)庫系統(tǒng)概論設(shè)計題目：商品信息管理系統(tǒng)設(shè)計與實現(xiàn)院系名稱信息科學(xué)與工程學(xué)院班級計算機(jī)科學(xué)與技術(shù)本科一班姓名與學(xué)號：李**于**見**陳**指引教師李*1課題簡介隨著當(dāng)代科技高速發(fā)展，設(shè)備和管理當(dāng)代化，在實際工作中如何提高工作效率成為一種很重要問題。而建立管理信息系統(tǒng)是一種較好解決辦法。通過半年學(xué)習(xí)，咱們對計算機(jī)方面知識有了很大提高，本著理論聯(lián)系實際宗旨，通過學(xué)校提供這次軟件技術(shù)實踐機(jī)會，在指引教師協(xié)助下，歷經(jīng)兩周時間，咱們自行設(shè)計一套商品信息管理系統(tǒng)，在下面各章中，我將以這套商品管理系統(tǒng)為例,談?wù)勂溟_發(fā)過程和所涉及到問題。2設(shè)計目應(yīng)用對數(shù)據(jù)庫系統(tǒng)原理理論學(xué)習(xí),通過上機(jī)實踐方式將理論知識與實踐更好結(jié)合起來,鞏固所學(xué)知識。數(shù)據(jù)庫應(yīng)用課程實踐：實踐和鞏固在課堂教學(xué)中學(xué)習(xí)關(guān)于知識，純熟掌握對于給定構(gòu)造數(shù)據(jù)庫創(chuàng)立、基本操作、程序系統(tǒng)建立和調(diào)試以及系統(tǒng)評價。數(shù)據(jù)庫原理軟件設(shè)計實踐：實踐和鞏固在課堂教學(xué)中學(xué)習(xí)關(guān)于關(guān)系數(shù)據(jù)庫原理關(guān)于知識和數(shù)據(jù)庫系統(tǒng)建立辦法，純熟掌握對于給定實際問題，為了建立一種關(guān)系數(shù)據(jù)庫商品信息管理系統(tǒng)，必要得通過系統(tǒng)調(diào)研、需求分析、概念設(shè)計、邏輯設(shè)計、物理設(shè)計、系統(tǒng)調(diào)試、維護(hù)以及系統(tǒng)評價普通過程，為畢業(yè)設(shè)計打下基本。3設(shè)計內(nèi)容運用基于E-R模型數(shù)據(jù)庫設(shè)計辦法和關(guān)系規(guī)范化理論做指引完畢從系統(tǒng)分析到設(shè)計直至系統(tǒng)最后實現(xiàn),開發(fā)商品信息系統(tǒng),完畢商品信息管理系統(tǒng)所有功能,商品入庫子系統(tǒng)，商品出庫子系統(tǒng)，商品庫存子系統(tǒng)。一方面做好需求分析，并完畢數(shù)據(jù)流圖和數(shù)據(jù)字典。另一方面做概念分析，運用實體聯(lián)系辦法將需求分析顧客需求抽象為信息構(gòu)造，得到E-R圖。然后就是邏輯構(gòu)造設(shè)計，將E-R圖轉(zhuǎn)換為計算機(jī)系統(tǒng)所支持邏輯模型4設(shè)計詳細(xì)實行進(jìn)行需求分析，編寫數(shù)據(jù)字典。1、系統(tǒng)功能規(guī)定設(shè)計①有一種存儲商品倉庫，每天均有商品出庫和入庫。②每種商品均有商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格等。③入庫時必要填寫入庫單據(jù)，單據(jù)涉及商品名稱、生產(chǎn)廠家、型號、規(guī)格、入庫數(shù)量、入庫日期、入庫倉庫號、入庫倉庫名稱、送貨人姓名。④出庫時必要填寫出庫單據(jù)，單據(jù)涉及倉庫號、倉庫名稱、商品編號、商品名稱、型號、規(guī)格、出庫數(shù)量、出庫日期、提貨人姓名。⑤設(shè)立商品庫存臺帳，商品庫存臺帳是對倉庫中當(dāng)前庫存所有商品明細(xì)記錄，商品庫存臺帳涉及商品編號、商品名稱、型號、規(guī)格、庫存數(shù)量、庫存日期。每當(dāng)有商品入庫或商品出庫時都應(yīng)當(dāng)自動修改該臺帳，最后一次修改是當(dāng)前庫存狀況。⑥商品入庫和出庫過程通過庫存臺帳更加清晰條理地顯示出倉庫中商品庫存數(shù)量和庫存日期等信息，容易對庫存內(nèi)商品信息進(jìn)行查詢，增長，修改，刪除等操作。⑦該倉庫商品型號統(tǒng)一為DA01-DA99格式，規(guī)格為CX100-CX199格式重要功能：商品管理：增長商品：修改商品，刪除商品，瀏覽商品增長商品：實現(xiàn)增長商品詳細(xì)資料功能修改商品：實現(xiàn)修改商品資料功能刪除商品：實現(xiàn)刪除該商品所有資料功能瀏覽商品：實現(xiàn)瀏覽所有商品功能②庫存管理：實現(xiàn)商品入庫，在商品入庫時通過觸發(fā)器或存儲過程同步完畢商品庫存臺帳更新。實現(xiàn)商品出庫，在商品出庫時通過觸發(fā)器或存儲過程同步完畢商品庫存臺帳更新。實現(xiàn)按商品名稱查詢商品入庫狀況及當(dāng)前庫存量。實現(xiàn)按入庫日期查詢商品入庫狀況及當(dāng)前庫存量。實現(xiàn)按商品名稱查詢商品出庫狀況及當(dāng)前庫存量。實現(xiàn)按出庫日期查詢商品出庫狀況及當(dāng)前庫存量。準(zhǔn)時間段查詢商品庫存狀況。實現(xiàn)分別按年、季度和月對入庫商品數(shù)量記錄。實現(xiàn)分別按年、季度和月對出庫商品數(shù)量記錄。2、系統(tǒng)模塊設(shè)計庫存物資管理大體可以分為如下3大模塊，如下圖所示:一方面是商品入庫模塊，該模塊重要就是描述把采購回來商品，分類別放置到指定倉庫中去，然后是商品出庫模塊，該模塊重要描述從指定倉庫中拿出商品；最后時庫存模塊，這個模塊重要是記錄商品庫存數(shù)量變化。商品信息商品信息管理商品入庫模塊商品出庫模塊商品庫存模塊3、數(shù)據(jù)字典表表名中文名Ware商品Depot倉庫Stock

庫存Stocking入庫Outbound出庫商品Ware字段名數(shù)據(jù)類型與否可覺得空IsPKIsFK中文名WNochar(10)NOTNULLYesNo商品編號WNamevarchar(50)NOTNULLNoNo商品名稱WFactoryvarchar(50)NOTNULLNoNo生產(chǎn)廠家WType

varchar(20)NOTNULLNoNo型號Specvarchar(20)NOTNULLNoNo規(guī)格倉庫Depot字段名數(shù)據(jù)類型與否可覺得空IsPKIsFK中文名DNochar(10)NOTNULLYesNo倉庫號DNamevarchar(50)NOTNULLNoNo倉庫名稱庫存Stock

字段名數(shù)據(jù)類型與否可覺得空IsPKIsFK中文名WNochar(10)NOTNULLYesYes商品編號Dnochar(10)NOTNULLYesYes倉庫號WAmount

IntNOTNULLNoNo庫存數(shù)量入庫Stocking字段名數(shù)據(jù)類型與否可覺得空IsPKIsFK中文名SNochar(10)NOTNULLYesNo入庫號WNochar(10)NOTNULLNoYes商品編號Dnochar(10)NOTNULLNoYes倉庫號SAmountIntNOTNULLNoNo入庫數(shù)量SDatesmalldatetimeNOTNULLNoNo入庫日期Suppliervarchar(50)NOTNULLNoNo送貨人姓名出庫Outbound字段名數(shù)據(jù)類型與否可覺得空IsPKIsFK中文名ONochar(10)NOTNULLYesNo出庫號WNochar(10)NOTNULLNoYes商品編號Dnochar(10)NOTNULLNoYes倉庫號OAmountintNOTNULLNoNo出庫數(shù)量ODatesmalldatetimeNOTNULLNoNo出庫日期Buyersvarchar(50)NOTNULLNoNo提貨人姓名設(shè)計完整E-R圖。商品編號商品編號型號生產(chǎn)廠家規(guī)格商品名稱商品倉庫號倉庫倉庫名稱商品倉庫庫存nm商品倉庫入庫nm商品倉庫出庫nm庫存關(guān)系：入庫關(guān)系：出庫關(guān)系：分E-R圖：總E-R圖：總E-R圖：商品編號型號生產(chǎn)廠家規(guī)格商品名稱商品倉庫號倉庫倉庫名稱入庫日期送貨人姓名入庫數(shù)量入庫入庫號nnnmmm庫存庫存數(shù)量提貨人姓名出庫日期出庫數(shù)量出庫出庫號進(jìn)行數(shù)據(jù)庫邏輯設(shè)計。關(guān)系模式如下：商品（商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格）商品編號為主鍵倉庫（倉庫號、倉庫名稱）倉庫號為主鍵庫存（商品編號，倉庫號，庫存數(shù)量）（商品編號，倉庫號）為主鍵，同步也分別為外鍵入庫（入庫號，商品編號，倉庫號，入庫數(shù)量，入庫日期，送貨人姓名）（入庫號）為主鍵，（商品編號，倉庫號）為外鍵出庫（出庫號，商品編號，倉庫號，出庫數(shù)量，出庫日期，提貨人姓名）（出庫號）為主鍵，（商品編號，倉庫號）為外鍵完畢物理數(shù)據(jù)庫設(shè)計，（涉及數(shù)據(jù)庫、表、索引、視圖、完整性約束物理設(shè)計。）創(chuàng)立數(shù)據(jù)庫createdatabaseMaterial_DB創(chuàng)立商品表useMaterial_DBcreatetableWare(/*定義商品表*/WNochar(10)NOTNULLprimarykey,WNamevarchar(50)NOTNULL,WFactoryvarchar(50)NOTNULL,WTypevarchar(20)NOTNULL,Specvarchar(20)NOTNULL)useMaterial_DB/*插入商品信息*/insertintoWarevalues('100000','毛巾','新家園','DA11','CX100');insertintoWarevalues('100001','臺燈','新家園','DA35','CX110');insertintoWarevalues('100002','電冰箱','海爾','DA61','CX900');insertintoWarevalues('100003','電視','東芝','DA52','CX901');insertintoWarevalues('100004','長城干紅','中糧華夏','DA25','CX108');insertintoWarevalues('100005','筆記本','東芝','DA24','CX781')；創(chuàng)立倉庫表useMaterial_DBcreatetableDepot(/*定義倉庫表*/DNochar(10)NOTNULLprimarykey,DNamevarchar(50)NOTNULL)useMaterial_DB/*插入倉庫信息*/insertintoDepotvalues('001','勝昌');insertintoDepotvalues('002','德隆');insertintoDepotvalues('003','豪友');創(chuàng)立庫存表useMaterial_DBcreatetableStock(/*定義庫存關(guān)系*/WNochar(10)NOTNULL,DNochar(10)NOTNULL,--WDatesmalldatetimeNOTNULL,WAmountintNOTNULL,primarykey(WNo,Dno),foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo))創(chuàng)立入庫表useMaterial_DBcreatetableStocking(/*定義入庫關(guān)系*/SNochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,DNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo),SAmountIntNOTNULL,SDatesmalldatetimeNOTNULL,Suppliervarchar(50)NOTNULL)創(chuàng)立出庫表useMaterial_DBcreatetableOutbound(/*定義出庫關(guān)系*/ONochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),DNochar(10)NOTNULL,foreignkey(DNo)referencesDepot(DNo),OAmountintNOTNULl,ODatesmalldatetimeNOTNULL,Buyersvarchar(50)NOTNULL)完整約束闡明：商品WareWNo商品編號規(guī)定在100000~999999之間，WName不能取空；倉庫DepotDNo倉庫編號規(guī)定在001~999之間，DName不能取空；該倉庫商品型號統(tǒng)一為DA01-DA99格式，規(guī)格為CX100-CX199格式。useMaterial_DB/*定義表Ware唯一性約束條件*/alterTableWareaddunique(WName,WFactory,WType,Spec);alterTableWare/*定義WType完整性約束條件：商品型號統(tǒng)一為DA01-DA99格式*/addconstraintC2check(WTypelike'[D][A][0-9][1-9]');alterTableWare/*定義Spec完整性約束條件：規(guī)格為CX100-CX199格式*/addconstraintC3check(Speclike'[C][X][1-9][0-9][0-9]');useMaterial_DBaltertableWare/*添加對表WareWNo屬性完整性約束，規(guī)定在~之間*/addconstraintC4check(WNolike'[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]');altertableDepotaddconstraintC5check(DNolike'[2][0][1][0][0-9][0-9][0-9]');useMaterial_DBaltertableWaredropconstraintC4;useMaterial_DBaltertableWare/*添加對表WareWNo屬性完整性約束，規(guī)定在~999999之間*/addconstraintC4check(WNolike'[1-9][0-9][0-9][0-9][0-9][0-9]');創(chuàng)立視圖通過WLST（提取“物料視圖”每個字頭字母）視圖來瀏覽庫存中相似編號商品信息，其中涉及：商品編號WNo、商品名WName、商品廠家WFactory、商品型號WType、商品規(guī)格Spec、商品數(shù)量SNum、createviewVWname(商品編號,商品名稱，庫存數(shù)量)--視圖ASSELECTStock.WNo,Ware.WName,sum(Stock.WAmount)fromWare,StockwhereStock.WNo=Ware.WNogroupbyStock.WNo,Ware.WName;實現(xiàn)商品入庫，在商品入庫時通過觸發(fā)器或存儲過程同步完畢商品庫存臺帳更新。入庫表觸發(fā)器createtriggerstocking_intoonstocking--這是入庫表觸發(fā)器afterinsertasbegindeclare@achar(10),@bchar(10),@dint;select@a=i.DNo,@b=i.WNo,@d=i.SAmountfrominsertedasi;if(selectStock.WNofromStockwhereStock.WNo=@bandStock.DNo=@a)isnotnullbegin updateStock setStock.WAmount=Stock.WAmount+@d whereStock.WNo=@bandStock.DNo=@a; return; endinsertintostockvalues(@b,@a,@d);endinsertintostockingvalues('100','100005','001',200,'-01-02','小明');insertintostockingvalues('101','100005','003',200,'-01-02','小蘭');insertintostockingvalues('102','100001','003',200,'-01-02','小東');insertintostockingvalues('103','100002','001',200,'-01-02','小紅');insertintostockingvalues('104','100003','001',200,'-01-14','小風(fēng)');insertintostockingvalues('105','100005','002',200,'-01-25','小明');insertintostockingvalues('106','100000','002',200,'-02-02','小明');insertintostockingvalues('107','100001','001',200,'-03-02','小東');insertintostockingvalues('108','100002','002',200,'-04-02','小紅');insertintostockingvalues('109','100003','003',200,'-04-02','小風(fēng)');insertintostockingvalues('110','100004','001',200,'-06-09','小敏');insertintostockingvalues('111','100004','002',200,'-06-02','小風(fēng)');insertintostockingvalues('112','100005','001',200,'-06-02','小青');insertintostockingvalues('113','100000','003',200,'-06-02','小明');insertintostockingvalues('114','100001','001',200,'-06-02','小東');insertintostockingvalues('115','100002','002',200,'-07-02','小紅');insertintostockingvalues('116','100003','003',200,'-08-02','小風(fēng)');insertintostockingvalues('117','100000','001',200,'-08-02','小明');insertintostockingvalues('118','100001','001',200,'-09-04','小東');insertintostockingvalues('119','100002','002',200,'-06-02','小紅');insertintostockingvalues('120','100003','003',200,'-09-11','小風(fēng)');insertintostockingvalues('121','100002','002',200,'-09-01','小明');insertintostockingvalues('122','100003','002',200,'-07-08','小敏');insertintostockingvalues('123','100002','003',200,'-04-11','小敏');insertintostockingvalues('124','100001','001',200,'-07-08','小紅');insertintostockingvalues('125','100001','003',200,'-08-21','小青');insertintostockingvalues('126','100005','001',200,'-07-08','小青');insertintostockingvalues('127','100003','003',200,'-09-01','小蘭');insertintostockingvalues('128','100002','002',200,'-07-08','小蘭');insertintostockingvalues('129','100004','001',200,'-07-08','小蘭');insertintostockingvalues('130','100005','002',200,'-07-08','小紅');實現(xiàn)商品出庫，在商品出庫時通過觸發(fā)器或存儲過程同步完畢商品庫存臺帳更新。出庫表觸發(fā)器createtriggeroutbound_donOutbound--這是出庫表觸發(fā)器afterinsertasbegindeclare@achar(10),@bchar(10),@dint;select@a=i.DNo,@b=i.WNo,@d=i.OAmountfrominsertedasiif(selects.WNofromStockasswheres.DNo=@aands.WNo=@b)isnotnullbeginif(selects.WAmountfromStockass,insertedwheres.WAmount>=inserted.OAmountands.WNo=inserted.WNoands.DNo=inserted.DNo)isnotnullbeginupdateStocksetStock.WAmount=Stock.WAmount-@dwhereStock.DNo=@aandStock.WNo=@bendelsebeginprint'庫存量不夠'rollbackendendelsebeginprint'庫存中沒有這種產(chǎn)品'rollbackendendinsertintoOutboundvalues('1','100005','001',50,'-01-02','小紅');insertintoOutboundvalues('2','100005','002',30,'-01-02','小紅');insertintoOutboundvalues('3','100001','003',50,'-01-02','小風(fēng)');insertintoOutboundvalues('4','100005','001',30,'-01-02','小紅');insertintoOutboundvalues('5','100002','002',20,'-02-08','小風(fēng)');insertintoOutboundvalues('6','100003','003',50,'-03-09','小紅');insertintoOutboundvalues('7','100000','002',30,'-04-15','小風(fēng)');insertintoOutboundvalues('8','100004','002',50,'-04-15','小風(fēng)');insertintoOutboundvalues('9','100005','001',30,'-06-02','小紅');insertintoOutboundvalues('10','100005','002',50,'-04-15','小紅');insertintoOutboundvalues('11','100001','003',50,'-06-03','小風(fēng)');insertintoOutboundvalues('12','100005','001',30,'-04-15','小紅');insertintoOutboundvalues('13','100002','002',20,'-07-05','小風(fēng)');insertintoOutboundvalues('14','100003','003',50,'-07-05','小紅');insertintoOutboundvalues('15','100000','002',40,'-07-05','小風(fēng)');insertintoOutboundvalues('16','100004','002',50,'-07-05','小風(fēng)');insertintoOutboundvalues('17','100005','002',20,'-09-01','小明');insertintoOutboundvalues('18','100001','003',30,'-09-01','小敏');insertintoOutboundvalues('19','100000','002',40,'-09-01','小敏');insertintoOutboundvalues('20','100002','002',50,'-09-01','小紅');insertintoOutboundvalues('21','100000','002',20,'-08-21','小青');insertintoOutboundvalues('22','100003','001',30,'-09-01','小青');insertintoOutboundvalues('23','100002','002',40,'-09-01','小蘭');insertintoOutboundvalues('24','100003','003',50,'-12-30','小蘭');insertintoOutboundvalues('25','100004','002',20,'-12-30','小蘭');insertintoOutboundvalues('26','100005','001',40,'-12-08','小紅');insertintoOutboundvalues('27','100001','003',10,'-12-30','小敏');insertintoOutboundvalues('28','100002','002',30,'-12-08','小明');insertintoOutboundvalues('29','100003','003',30,'-12-18','小紅');insertintoOutboundvalues('30','100004','001',40,'-09-01','小敏');實現(xiàn)按商品名稱查詢商品入庫狀況及當(dāng)前庫存量。/*辦法一*/ selectSNo,WName,s.WNo,s.DNo,SAmount,WAmount fromStockass,Wareasw,Stockingassi whereWName='電冰箱'ands.WNo=w.WNoandw.WNo=si.WNoands.DNo=si.DNo/*辦法二*/ declare@WNamevarchar(32) SET@WName='電冰箱' SELECT* FROMStockingWHEREWNoIN(SELECTWNoFROMWareWHEREWName=@WName);/*辦法三*/ CREATEVIEWLSName(SNo,WName,WNo,DNo,SAmount,WAmount)ASselectSNo,WName,s.WNo,s.DNo,SAmount,WAmountfromStockass,Wareasw,Stockingassiselectdistinctsi.Sno,l.WName,si.SAmount,s.WAmountFROMStockass,LSNameasl,Wareasw,StockingassiWHEREl.WName='電冰箱'andsi.Sno=l.Snoandw.WName=l.WNameands.WNo=si.WNoandsi.WNo=w.WNoandw.WNo=l.WNoandsi.DNo=l.DNoandsi.SAmount=l.SAmountands.WAmount=l.WAmount實現(xiàn)按入庫日期查詢商品入庫狀況及當(dāng)前庫存量。/*辦法一復(fù)合條件查詢*/ selectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount fromStockass,Stocking whereSDate='-01-0200:00:00'ands.WNo=Stocking.WNo ands.DNo=Stocking.DNo/*辦法二建立視圖查詢*/ createviewWA_Stocking(SNo,WNo,DNo,SAmount,SDate,Supplier,WAmount) asselectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount fromStockassJOINStockingons.WNo=Stocking.WNo ands.DNo=Stocking.DNoandSDate='-01-0200:00:00' withcheckoption;/*辦法三有關(guān)子查詢*/selectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmountfromStockass,Stockingwhereexists(select*whereSDate='-01-0200:00:00'ands.WNo=Stocking.WNoands.DNo=Stocking.DNo);實現(xiàn)按商品名稱查詢商品出庫狀況及當(dāng)前庫存量。/*辦法一*/ selectONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount fromStockass,Outbound,Ware whereWName='長城干紅'andWare.Wno=s.Wnoands.Wno=Outbound.Wno ands.DNo=Outbound.DNo/*辦法二*/ /*創(chuàng)立視圖*/CREATEVIEWW_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount,WName)ASSELECTONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WNameFROMWare,Stock,OutboundWHEREStock.WNo=Outbound.WNoANDStock.DNo=Outbound.DNoANDWare.WNo=Stock.WnoselectONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmountfromW_S_OwhereWName='長城干紅'/*辦法三*/declare@WNamevarchar(32)set@WName='長城干紅'select*fromOutboundwhereWNo=(selectWNofromWarewhereWName=@WName);/*辦法四*/selectONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount fromStockass,Outbound,Ware whereexists(select*whereWName='長城干紅'ands.WNo=Outbound.WNo ands.DNo=Outbound.DNoandWare.Wno=s.Wno);實現(xiàn)按出庫日期查詢商品出庫狀況及當(dāng)前庫存量。/*辦法一*/selectONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount fromStockass,Outbound whereODate='-01-020:00:00'ands.WNo=Outbound.WNo ands.DNo=Outbound.DNo/*辦法二*//*創(chuàng)立視圖*/ CREATEVIEWSO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount) AS SELECTONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount FROMStock,Outbound WHEREStock.WNo=Outbound.WNoANDStock.DNo=Outbound.DNo selectONo,WNo,DNo,OAmount,ODate,Buyers,WAmount fromSO whereODate='-01-020:00:00'/*辦法三*/selectONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount fromStockass,Outbound whereexists(select*whereODate='-01-020:00:00'ands.WNo=Outbound.WNo ands.DNo=Outbound.DNo);準(zhǔn)時間段查詢商品庫存狀況。/*時間在-01-02和-01-02之間入庫出庫狀況*//*辦法一*/ selectw.WNameas商品,sum(SAmount)as入庫數(shù)量 fromStockingass,Wareasw wherew.WNo=s.WNoandSDatebetween'-01-02'and'-01-02' groupbyw.WName selectw.WNameas商品,sum(OAmount)as出庫數(shù)量 fromOutboundaso,Wareasw wherew.WNo=o.WNoandODatebetween'-01-02'and'-01-02' groupbyw.WName/*辦法二*/ selectw.WNameas商品,sum(SAmount)as入庫數(shù)量 fromStockingass,Wareasw wherew.WNo=s.WNoandSDatein ( selectSDate fromStockingass whereSDatebetween'-01-02'and'-01-02' ) groupbyw.WName selectw.WNameas商品,sum(OAmount)as出庫數(shù)量 fromOutboundaso,Wareasw wherew.WNo=o.WNoandODatein ( selectODate fromOutboundaso whereODatebetween'-01-02'and'-01-02' ) groupbyw.WName/*辦法三*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereSDatebetween'-01-02'and'-01-02'andStocking.WNo=Ware.WNo)groupbyWNameselectWName,sum(OAmount)OsumfromOutbound,Warewhereexists(select*whereODatebetween'-01-02'and'-01-02'andOutbound.WNo=Ware.WNo)groupbyWName實現(xiàn)分別按年、季度和月對入庫商品數(shù)量記錄。/*辦法一：年：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereyear(SDate)=andStocking.WNo=Ware.WNogroupbyWName/*季度：*/selectWName,sum(SAmount)SsumfromStocking,WarewhereSDatebetween'-3-10:00:00'and'-5-310:00:00'andStocking.WNo=Ware.WNogroupbyWName/*月：*/selectWName,sum(SAmount)SsumfromStocking,Warewheremonth(SDate)=6andStocking.WNo=Ware.WNoandyear(SDate)=groupbyWName/*辦法二：年：*/createviewStocking_SumNasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowhereyear(SDate)=groupbyWName/*月：*/createviewStocking_SumMasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowheremonth(SDate)=6andyear(SDate)=groupbyWName/*季度：*/createviewStocking_SumJasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowhereSDatebetween'-3-10:00:00'and'-5-310:00:00'groupbyWName/*辦法三：年：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereyear(SDate)=andStocking.WNo=Ware.WNo)groupbyWName/*季度：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereSDatebetween'-3-10:00:00'and'-5-310:00:00'andStocking.WNo=Ware.WNo)groupbyWName/*月：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*wheremonth(SDate)=6andStocking.WNo=Ware.WNoandyear(SDate)=)groupbyWName實現(xiàn)分別按年、季度和月對出庫商品數(shù)量記錄。/*第一種辦法：復(fù)合條件查詢按年：*/selectWName,sum(OAmount)OsumfromOutbound,Warewhereyear(ODate)=andOutbound.WNo=Ware.WNogroupbyWName/*按季度：*/selectWName,sum(OAmount)OsumfromOutbound,WarewhereODatebetween'-09-010:00:00'and'-11-300:00:00'andOutbound.WNo=Ware.WNogroupbyWName/*按月：*/selectWName,sum(OAmount)OsumfromOutbound,Warewheremonth(ODate)=9andOutbound.WNo=Ware.WNoandyear(ODate)=groupbyWName/*第二種辦法：視圖查詢按年：*/createviewyear_outb(商品,出庫數(shù)量)asselectWName,sum(OAmount)OsumfromOutboundasoJOINWareaswono.WNo=w.WNowhereyear(ODate)=groupbyWNamewithcheckoption;/*按季度：*/createviewjidu_outb(商品,出庫數(shù)量)asselectWName,sum(OAmount)OsumfromOutboundasoJOINWareaswon