数据库课程设计商品信息管理系统样本

资料内容仅供您学习参考，如有不当之处，请联系改正或者删除。课程设计课程名称:数据库系统概论设计题目:商品信息管理系统设计与实现院系名称信息科学与工程学院班级交通运输1202班姓名与学号:指导教师1课题简介随着现代科技的高速发展,设备和管理的现代化,在实际工作中如何提高工作效率成为一个很重要的问题。而建立管理信息系统是一个很好的解决办法。经过半年的学习,我们对计算机方面的知识有了很大的提升,本着理论联系实际的宗旨,经过学校提供的这次软件技术实践的机会,在指导教师的帮助下,历经两周时间,我们自行设计一套商品信息管理系统,在下面的各章中,我将以这套商品管理系统为例,谈谈其开发过程和所涉及到的问题。2设计目的应用对数据库系统原理的理论学习,经过上机实践的方式将理论知识与实践更好的结合起来,巩固所学知识。数据库应用课程实践:实践和巩固在课堂教学中学习有关知识,熟练掌握对于给定结构的数据库的创立、基本操作、程序系统的建立和调试以及系统评价。数据库原理软件设计实践:实践和巩固在课堂教学中学习的关于关系数据库原理的有关知识和数据库系统的建立方法,熟练掌握对于给定实际问题,为了建立一个关系数据库商品信息管理系统,必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价的一般过程,为毕业设计打下基础。3设计内容运用基于E-R模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设计直至系统的最终实现,开发商品信息系统,完成商品信息管理系统的全部功能,商品入库子系统,商品出库子系统,商品库存子系统。首先做好需求分析,并完成数据流图和数据字典。其次做概念分析,利用实体联系的方法将需求分析的用户需求抽象为信息结构,得到E-R图。然后就是逻辑结构设计,将E-R图转换为计算机系统所支持的逻辑模型4设计具体实施进行需求分析,编写数据字典。1、系统功能要求设计①有一个存放商品的仓库,每天都有商品出库和入库。②每种商品都有商品编号、商品名称、生产厂家、型号、规格等。③入库时必须填写入库单据,单据包括商品名称、生产厂家、型号、规格、入库数量、入库日期、入库仓库号、入库仓库名称、送货人姓名。④出库时必须填写出库单据,单据包括仓库号、仓库名称、商品编号、商品名称、型号、规格、出库数量、出库日期、提货人姓名。⑤设置商品库存台帐,商品库存台帐是对仓库中当前库存的所有商品的明细记录,商品库存台帐包括商品编号、商品名称、型号、规格、库存数量、库存日期。每当有商品入库或商品出库时都应该自动修改该台帐,最后一次修改的是现在的库存情况。⑥商品的入库和出库过程经过库存台帐更加清晰条理地显示出仓库中商品的库存数量和库存日期等信息,容易对库存内的商品信息进行查询,增加,修改,删除等操作。⑦该仓库的商品型号统一为DA01-DA99格式,规格为CX100-CX199格式主要功能:商品管理:增加商品:修改商品,删除商品,浏览商品增加商品:实现增加商品的详细资料的功能修改商品:实现修改商品资料的功能删除商品:实现删除该商品的所有资料的功能浏览商品:实现浏览所有商品的功能②库存管理:实现商品的入库,在商品入库时经过触发器或存储过程同时完成商品库存台帐的更新。实现商品的出库,在商品出库时经过触发器或存储过程同时完成商品库存台帐的更新。实现按商品名称查询商品的入库情况及当前的库存量。实现按入库日期查询商品的入库情况及当前的库存量。实现按商品名称查询商品的出库情况及当前的库存量。实现按出库日期查询商品的出库情况及当前的库存量。按时间段查询商品库存情况。实现分别按年、季度和月对入库商品数量的统计。实现分别按年、季度和月对出库商品数量的统计。2、系统模块设计库存物资管理大致能够分为以下3大模块,如下图所示:首先是商品入库模块,该模块主要就是描述把采购回来的商品,分类别的放置到指定的仓库中去,然后是商品出库模块,该模块主要描述从指定的仓库中拿出商品;最后时库存模块,这个模块主要是记录商品的库存数量变化。商品信息管理商品信息管理商品入库模块商品出库模块商品库存模块3、数据字典表表名中文名Ware商品Depot仓库Stock

库存Stocking入库Outbound出库商品Ware字段名数据类型是否能够为空IsPKIsFK中文名WNochar(10)NOTNULLYesNo商品编号WNamevarchar(50)NOTNULLNoNo商品名称WFactoryvarchar(50)NOTNULLNoNo生产厂家WType

varchar(20)NOTNULLNoNo型号Specvarchar(20)NOTNULLNoNo规格仓库Depot字段名数据类型是否能够为空IsPKIsFK中文名DNochar(10)NOTNULLYesNo仓库号DNamevarchar(50)NOTNULLNoNo仓库名称库存Stock

字段名数据类型是否能够为空IsPKIsFK中文名WNochar(10)NOTNULLYesYes商品编号Dnochar(10)NOTNULLYesYes仓库号WAmount

IntNOTNULLNoNo库存数量入库Stocking字段名数据类型是否能够为空IsPKIsFK中文名SNochar(10)NOTNULLYesNo入库号WNochar(10)NOTNULLNoYes商品编号Dnochar(10)NOTNULLNoYes仓库号SAmountIntNOTNULLNoNo入库数量SDatesmalldatetimeNOTNULLNoNo入库日期Suppliervarchar(50)NOTNULLNoNo送货人姓名出库Outbound字段名数据类型是否能够为空IsPKIsFK中文名ONochar(10)NOTNULLYesNo出库号WNochar(10)NOTNULLNoYes商品编号Dnochar(10)NOTNULLNoYes仓库号OAmountintNOTNULLNoNo出库数量ODatesmalldatetimeNOTNULLNoNo出库日期Buyersvarchar(50)NOTNULLNoNo提货人姓名设计完整的E-R图。商品编号商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称商品仓库库存nm商品仓库入库nm商品仓库出库nm库存关系:入库关系:出库关系:分E-R图:总E-R图:总E-R图:商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称入库日期送货人姓名入库数量入库入库号nnnmmm库存库存数量提货人姓名出库日期出库数量出库出库号进行数据库的逻辑设计。关系模式如下:商品(商品编号、商品名称、生产厂家、型号、规格)商品编号为主键仓库(仓库号、仓库名称)仓库号为主键库存(商品编号,仓库号,库存数量)(商品编号,仓库号)为主键,同时也分别为外键入库(入库号,商品编号,仓库号,入库数量,入库日期,送货人姓名)(入库号)为主键,(商品编号,仓库号)为外键出库(出库号,商品编号,仓库号,出库数量,出库日期,提货人姓名)(出库号)为主键,(商品编号,仓库号)为外键完成物理数据库的设计,(包括数据库、表、索引、视图、完整性约束的物理设计。)创立数据库createdatabaseMaterial_DB创立商品表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');创立仓库表useMaterial_DBcreatetableDepot(/*定义仓库表*/DNochar(10)NOTNULLprimarykey,DNamevarchar(50)NOTNULL)useMaterial_DB/*插入仓库信息*/insertintoDepotvalues('001','胜昌');insertintoDepotvalues('002','德隆');insertintoDepotvalues('003','豪友');创立库存表useMaterial_DBcreatetableStock(/*定义库存关系*/WNochar(10)NOTNULL,DNochar(10)NOTNULL,--WDatesmalldatetimeNOTNULL,WAmountintNOTNULL,primarykey(WNo,Dno),foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo))创立入库表useMaterial_DBcreatetableStocking(/*定义入库关系*/SNochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,DNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo),SAmountIntNOTNULL,SDatesmalldatetimeNOTNULL,Suppliervarchar(50)NOTNULL)创立出库表useMaterial_DBcreatetableOutbound(/*定义出库关系*/ONochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),DNochar(10)NOTNULL,foreignkey(DNo)referencesDepot(DNo),OAmountintNOTNULl,ODatesmalldatetimeNOTNULL,Buyersvarchar(50)NOTNULL)完整约束的说明:商品Ware的WNo商品编号要求在100000~999999之间,WName不能取空;仓库Depot的DNo仓库编号要求在001~999之间,DName不能取空;该仓库的商品型号统一为DA01-DA99格式,规格为CX100-CX199格式。useMaterial_DB/*定义表Ware唯一性的约束条件*/alterTableWareaddunique(WName,WFactory,WType,Spec);alterTableWare/*定义WType的完整性约束条件:商品型号统一为DA01-DA99格式*/addconstraintC2check(WTypelike'[D][A][0-9][1-9]');alterTableWare/*定义Spec的完整性约束条件:规格为CX100-CX199格式*/addconstraintC3check(Speclike'[C][X][1-9][0-9][0-9]');useMaterial_DBaltertableWare/*添加对表Ware的WNo属性的完整性约束,要求在~之间*/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/*添加对表Ware的WNo属性的完整性约束,要求在~999999之间*/addconstraintC4check(WNolike'[1-9][0-9][0-9][0-9][0-9][0-9]');创立视图经过WLST(提取”物料视图”每个字的头字母)视图来浏览库存中相同编号商品的信息,其中包括:商品编号WNo、商品名WName、商品厂家WFactory、商品型号WType、商品规格Spec、商品数量SNum、createviewVWname(商品编号,商品名称,库存数量)--视图ASSELECTStock.WNo,Ware.WName,sum(Stock.WAmount)fromWare,StockwhereStock.WNo=Ware.WNogroupbyStock.WNo,Ware.WName;实现商品的入库,在商品入库时经过触发器或存储过程同时完成商品库存台帐的更新。入库表的触发器createtriggerstocking_intoonstocking--这是入库表的触发器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','小风');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','小风');insertintostockingvalues('110','100004','001',200,'-06-09','小敏');insertintostockingvalues('111','100004','002',200,'-06-02','小风');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','小风');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','小风');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','小红');实现商品的出库,在商品出库时经过触发器或存储过程同时完成商品库存台帐的更新。出库表触发器createtriggeroutbound_donOutbound--这是出库表的触发器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'库存中没有这种产品'rollbackendendinsertintoOutboundvalues('1','100005','001',50,'-01-02','小红');insertintoOutboundvalues('2','100005','002',30,'-01-02','小红');insertintoOutboundvalues('3','100001','003',50,'-01-02','小风');insertintoOutboundvalues('4','100005','001',30,'-01-02','小红');insertintoOutboundvalues('5','100002','002',20,'-02-08','小风');insertintoOutboundvalues('6','100003','003',50,'-03-09','小红');insertintoOutboundvalues('7','100000','002',30,'-04-15','小风');insertintoOutboundvalues('8','100004','002',50,'-04-15','小风');insertintoOutboundvalues('9','100005','001',30,'-06-02','小红');insertintoOutboundvalues('10','100005','002',50,'-04-15','小红');insertintoOutboundvalues('11','100001','003',50,'-06-03','小风');insertintoOutboundvalues('12','100005','001',30,'-04-15','小红');insertintoOutboundvalues('13','100002','002',20,'-07-05','小风');insertintoOutboundvalues('14','100003','003',50,'-07-05','小红');insertintoOutboundvalues('15','100000','002',40,'-07-05','小风');insertintoOutboundvalues('16','100004','002',50,'-07-05','小风');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','小敏');实现按商品名称查询商品的入库情况及当前的库存量。/*方法一*/ 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实现按入库日期查询商品的入库情况及当前的库存量。/*方法一复合条件查询*/ 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;/*方法三相关子查询*/selectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmountfromStockass,Stockingwhereexists(select*whereSDate='-01-0200:00:00'ands.WNo=Stocking.WNoands.DNo=Stocking.DNo);实现按商品名称查询商品的出库情况及当前的库存量。/*方法一*/ 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/*方法二*/ /*创立视图*/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);实现按出库日期查询商品的出库情况及当前的库存量。/*方法一*/selectONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount fromStockass,Outbound whereODate='-01-020:00:00'ands.WNo=Outbound.WNo ands.DNo=Outbound.DNo/*方法二*//*创立视图*/ 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);按时间段查询商品库存情况。/*时间在-01-02和-01-02之间的入库出库情况*//*方法一*/ selectw.WNameas商品,sum(SAmount)as入库数量 fromStockingass,Wareasw wherew.WNo=s.WNoandSDatebetween'-01-02'and'-01-02' groupbyw.WName selectw.WNameas商品,sum(OAmount)as出库数量 fromOutboundaso,Wareasw wherew.WNo=o.WNoandODatebetween'-01-02'and'-01-02' groupbyw.WName/*方法二*/ selectw.WNameas商品,sum(SAmount)as入库数量 fromStockingass,Wareasw wherew.WNo=s.WNoandSDatein ( selectSDate fromStockingass whereSDatebetween'-01-02'and'-01-02' ) groupbyw.WName selectw.WNameas商品,sum(OAmount)as出库数量 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实现分别按年、季度和月对入库商品数量的统计。/*方法一:年:*/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实现分别按年、季度和月对出库商品数量的统计。/*第一种方法:复合条件查询按年:*/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(商品,出库数量)asselectWName,sum(OAmount)OsumfromOutboundasoJOINWareaswono.WNo=w.WNowhereyear(ODate)=groupbyWNamewithcheckoption;/*按季度:*/createviewjidu_outb(商品,出库数量)asselectWName,sum(OAmount)OsumfromOutboundasoJOINWareaswono.WNo=w.WNowhereODatebetween'-09-010:00:00'and'-11-3