《SQLServer数据库应用与维护》-综合实训4-参考答案

实训四复杂查询一、实验目的根据项目要求进行相应数据库表数据的复杂查询进一步学习视图的原理进一步认识金蝶ERP系统及其数据库结构二、实验软件要求金蝶K3/WISE三、实验内容第一题【采购订单复杂查询】（1）查询向苏州电器厂订购的采购订单的总数量，总金额（含税）。你所使用的T-SQL语句是：selectsum(FAuxQty)as'采购订单总数量',sum(FAllAmount)as'总金额(含税)'fromPOOrder,POOrderEntry,t_Item,t_SupplierwherePOOrder.FInterID=POOrderEntry.FInterIDandt_Item.FItemID=POOrderEntry.FItemIDandt_Supplier.FItemID=POOrder.FSupplyIDandt_Supplier.FName='苏州电器厂'请将查询的结果截图如下：（2）查询哪一个供应商供应的轴承价格最高？要求列出供应商名称，采购日期。你所使用的T-SQL语句是：selecttop1t_Supplier.FNameas'供应商名称',POOrderEntry.FDateas'日期'fromPOOrder,POOrderEntry,t_Item,t_SupplierwherePOOrder.FInterID=POOrderEntry.FInterIDandt_Item.FItemID=POOrderEntry.FItemIDandt_Supplier.FItemID=POOrder.FSupplyIDandt_Item.FName='轴承'orderbyFAuxTaxPricedesc请将查询的结果截图如下：（3）查询2017年采购的各物料的平均单价（含税）。要求列出物料名称，平均单价（含税），并按平均单价降序排列。你所使用的T-SQL语句是：selectt_Item.FNameas'物料名称',avg(FAuxTaxPrice)as'平均单价（含税）'fromPOOrder,POOrderEntry,t_Item,t_SupplierwherePOOrder.FInterID=POOrderEntry.FInterIDandt_Item.FItemID=POOrderEntry.FItemIDandt_Supplier.FItemID=POOrder.FSupplyIDandPOOrder.FDate>'2017-1-1'groupbyt_Item.FNameorderbyavg(FAuxTaxPrice)desc请将查询的结果截图如下：（4）查询向二级供应商订购的采购订单信息，信息包括单据编号，供应商名称，物料名称，数量、含税单价，含税金额。你所使用的T-SQL语句是：selectPOOrder.FBillNoas'单据编号',t_Supplier.FNameas'供应商名称',t_Item.FNameas'物料名称',FAuxQty'数量',FAuxTaxPrice'含税单价',FAllAmountas'含税金额'fromPOOrder,POOrderEntry,t_Item,t_Supplier,t_SubMessagewherePOOrder.FInterID=POOrderEntry.FInterIDandt_Item.FItemID=POOrderEntry.FItemIDandt_Supplier.FItemID=POOrder.FSupplyIDandt_SubMessage.FInterID=t_Supplier.FTypeIDandt_SubMessage.FName='二级供应商'请将查询的结果截图如下：第二题【了解视图】请问职员表（t_Emp）是基本表还是视图?答：视图。如果是视图，请写出创建该视图的T-SQL语句：答：CREATEVIEWt_EmpASSELECTt1.FAccountName,t1.FAddress,t1.FAllotPercent,t1.FAllotWeight,t1.FBankAccount,t1.FBankID,t1.FBirthday,t1.FBrNO,t1.FCreditAmount,t1.FCreditDays,t1.FCreditLevel,t1.FCreditPeriod,t1.FDegree,t1.FDeleted,t1.FDepartmentID,t1.FDuty,t1.FEmail,t1.FEmpGroup,t1.FEmpGroupID,t1.FGender,t1.FHireDate,t1.FID,t1.FIsCreditMgr,t1.FItemDepID,t1.FItemID,t1.FJobTypeID,t1.FLeaveDate,t1.FMobilePhone,t1.FModifyTime,t1.FName,t1.FNote,t1.FNumber,t1.FOperationGroup,t1.FOtherAPAcctID,t1.FOtherARAcctID,t1.FParentID,t1.FPersonalBank,t1.FPhone,t1.FPreAPAcctID,t1.FPreARAcctID,t1.FProfessionalGroup,t1.FShortNumberFROMt_Base_Empt1with(nolock)INNERJOINHR_Base_Empt2with(nolock)ONt1.FItemID=t2.FItemIDGO该视图的信息来源于哪些基本表？答：t_Base_Emp、HR_Base_Emp第三题【创建视图】（1）创建视图V1，视图内容包括职员代码、名称、性别、出生日期、电话、移动电话你所使用的T-SQL语句是：CREATEVIEWV1ASselectFNumberas'职员代码',t_Base_Emp.FNameas'名称',t_SubMessage.FNameas'性别',FBirthdayas'出生日期',FPhoneas'电话',Mobileas'移动电话'fromt_Base_Emp,HR_Base_Emp,t_SubMessagewheret_Base_Emp.FItemID=HR_Base_Emp.FItemIDandt_Base_Emp.FGender=t_SubMessage.FInterID视图的内容截图如下：（2）创建视图V2，视图内容包括职员代码、名称、性别、部门名称、出生日期、电话、移动电话你所使用的T-SQL语句是：CREATEVIEWV2ASselectt_Base_Emp.FNumberas'职员代码',t_Base_Emp.FNameas'名称',t_SubMessage.FNameas'性别',t_Department.FNameas'部门名称',FBirthdayas'出生日期',t_Base_Emp.FPhoneas'电话',Mobileas'移动电话'fromt_Base_Emp,t_Department,HR_Base_Emp,t_SubMessagewheret_Department.FitemID=t_Base_Emp.FDepartmentIDandt_Base_Emp.FItemID=HR_Base_Emp.FItemIDandt_Base_Emp.FGender=t_SubMessage.FInterID视图的内容截图如下：（3）创建视图V3，视图内容包括职员代码、名称、性别、职员类别、部门名称、出生日期、电话、移动电话你所使用的T-SQL语句是：createviewv3asselectt_Base_Emp.FNumberas'职员代码',t_Base_Emp.FNameas'名称',HM_EM_Sex_Code.SexTypeas'性别',t_SubMessage.FNameas'职员类别',t_Department.FNameas'部门名称',FBirthdayas'出生日期',t_Base_Emp.FPhoneas'电话',Mobileas'移动电话'fromt_Base_EmpLEFTjoint_Departmentont_Department.FitemID=t_Base_Emp.FDepartmentIDleftjoinHR_Base_Empont_Base_Emp.FItemID=HR_Base_Emp.FItemIDleftjoint_SubMessageont_Base_Emp.FEmpGroup=t_SubMessage.FInterIDleftjoinHM_EM_Sex_Codeont_Base_Emp.FGender-1067=HM_EM_Sex_Code.ID视图的内容截图如下：（4）创建视图V4，视图内容包括下图框图的内容你所使用的T-SQL语句是：createViewv4asselectt_Base_Emp.FNumberas'职员代码',t_Base_Emp.FNameas'名称',t_item.FFullNameas'全名',t_SubMessage.FNameas'职员类别',t_Department.FNameas'部门名称',HM_EM_Sex_Code.SexTypeas'性别',FBirthdayas'出生日期',FDegreeas'文化程度',t_Base_Emp.FPhoneas'电话',Mobileas'移动电话',t_Base_Emp.FIDas'身份证号码',FDutyas'职务',FHireDateas'入职日期',FLeaveDateas'离职日期',t_BK_Acnt.FAcntNameas'户名',Sub2.FNameas'开户银行',FBankAccountas'银行账号',FAddressas'住址',FEmailas'电子邮件',t_Base_Emp.Fnoteas'备注',t_Base_Emp.FIsCreditMgras'是否进行信用管理',FEmpGroupIDas'班组',FJobTypeIDas'工种',FProfessionalGroupas'业务组'fromt_Base_EmpLEFTjoint_Departmentont_Department.FitemID=t_Base_Emp.FDepartmentIDleftjoinHR_Base_Empont_Base_Emp.FItemID=HR_Base_Emp.FItemIDleftjoint_SubMessageont_Base_Emp.FEmpGroup=t_SubMessage.FInterIDleftjoinHM_EM_Sex_Codeont_Base_Emp.FGender-1067=HM_EM_Sex_Code.IDleftjoint_itemont_Base_Emp.FItemID=t_item.FItemIDleftjoint_BK_Acntont_Base_Emp.FBankAccount=t_BK_Acnt.FItemIDleftjoint_SubMessageSub2ont_Base_Emp.FBankID=Sub2.FInterIDwheret_Base_Emp.FName='王刚'视图的内容截图如下：第四题【分析视图】采购订单视图（vwICBill_26）的信息来源于哪些基本表：POOrder.FInterIDAS[FInterID],POOrderEntry.FEntryIDAS[FEntryID],t_Supplier.FNameAS[FSupplyID],POOrder.FdateAS[Fdate],POOrder.FBillNoAS[FBillNo],casewhenPOOrder.FCheckerID>0then'※'whenPOOrder.FCheckerID<0then'※'else''endAS[FCheckFlag],casewhenPOOrder.FStatus=3then'※'else''endAS[FStatusEx],POOrderEntry.FMapNumberAS[FMapNumber],POOrderEntry.FMapNameAS[FMapName],t_ICItem.FShortNumberAS[FShortNumber],t_ICItem.FNameAS[FItemID],t_ICItem.FmodelAS[Fmodel],t_MeasureUnit.FNameAS[FUnitID],t_Currency.FNameAS[FCurrencyID],POOrder.FExchangeRateAS[FExchangeRate],POOrder.FDeliveryPlaceAS[FDeliveryPlace],t_Emp.FNameAS[FMangerID],t_Department.FNameAS[FDeptID],t_Emp.FNameAS[FEmpID],t_User.FNameAS[FBillerID],POOrderEntry.FSourceTranTypeAS[FSourceTranType],POOrderEntry.FSourceBillNoAS[FSourceBillNo],POOrderEntry.FAuxQtyAS[FAuxQty],POOrderEntry.FdateAS[FCommitdate],POOrderEntry.FauxpriceAS[Fauxprice],POOrderEntry.FTaxRateAS[FTaxRate],POOrderEntry.FCessAS[FCess],POOrderEntry.FAmountAS[FAmount],POOrderEntry.FNoteAS[FNote],t_User.FNameAS[FCheckerID],t_MeasureUnit.FNameAS[FUnitGroupID],POOrderEntry.FQtyAS[FQty],casewhenPOOrder.FCancellation=1then'※'else''endAS[FCancellation],t_SonCompany.FNameAS[FRelateBrID],t_ICItem.FNumberAS[FNumber],t_SubMessage.FNameAS[FPOStyle],POOrderEntry.FCommitQtyAS[FCommitQty],POOrderEntry.FAuxCommitQtyAS[FAuxCommitQty],POOrderEntry.FStockQtyAS[FStockQty],POOrderEntry.FAuxStockQtyAS[FAuxStockQty],CaseWHENt_ICItem.FCUUnitID=0THEN''Elset_SubMessage00.FNameendAS[FCUUnitID],CaseWHENt_ICItem.FCUUnitID=0THEN0ElsePOOrderEntry.FQty/t_SubMessage00.FCoefficientendAS[FcuQty],POOrderEntry.FSupConfirmAS[FSupConfirm],POOrderEntry.FSupConDateAs[FSupConDate],POOrderEntry.FSupConMemAS[FSupConMem],POOrderEntry.FSupConQtyAS[FSupConQty],POOrderEntry.FSupConFetchDateAs[FSupConFetchDate],tuc.FNameAS[FSupConfirmor]字段名类型描述来源（表或视图）源表字段名描述FAmountFLOAT金额POOrderEntryFAmount金额FAuxCommitQtyFLOAT关联数量POOrderEntryFAuxCommitQty辅助到货数量FauxpriceFLOAT单价POOrderEntryFauxprice辅助单价FAuxQtyFLOAT数量POOrderEntryFAuxQty辅助订货数量FAuxStockQtyFLOAT入库数量POOrderEntryFAuxStockQty辅助入库数量FBillerIDSTRING制单t_UserFName用户名称FBillNoSTRING编号POOrderFBillNo采购订单号FCancellationSTRING作废标志POOrderFCancellation作废FCessFLOAT税率(%)POOrderEntryFCess税率(%)FCheckerIDSTRING审核人POOrderFCheckerID审核人FCheckFlagSTRING审核标志POOrderFCheckerID审核人FCommitdateDATETIME交货日期POOrderEntryFdate交货日期FCommitQtyFLOAT基本单位关联数量POOrderEntryFCommitQty到货数量FcuQtyFLOAT常用单位数量POOrderEntry,t_SubMessage00POOrderEntry.FQty/t_SubMessage00.FCoefficient常用单位数量FCurrencyIDSTRING币别t_CurrencyFName币别名称FCUUnitIDSTRING常用单位t_ICItemFCUUnitID常用计量单位FdateDATETIME日期POOrderFdate单据日期FDeptIDSTRING部门t_DepartmentFName部门名称FEmpIDSTRING业务员t_EmpFName姓名FEntryIDINTEGER单据分录号POOrderEntryFEntryID分录号FExchangeRateFLOAT汇率POOrderFExchangeRate汇率FInterIDINTEGER单据内码POOrderFInterID采购订单内码FItemIDSTRING产品名称t_ICItemFName产品名称FMangerIDSTRING主管t_EmpFName姓名FMapNameSTRING对应名称POOrderEntryFName对应名称FMapNumberSTRING对应代码POOrderEntryFMapNumber对应代码FmodelSTRING规格型号t_ICItemFmodel规格型号FNoteSTRING备注POOrderEntryFNote备注FNumberSTRING产品长代码t_ICItemFNumber产品代码FPOStyleSTRING采购方式t_SubMessageFName资料名称FQtyFLOAT基本单位数量POOrderEntryFQty订货数量FRelateBrIDSTRING供货机构t_SonCompanyFName分支机构名称FShortNumberSTRING产品代码t_ICItemFShortNumber产品简码FSourceBillNoSTRING源单单号POOrderEntryFSourceBillNo源单单号FSourceTranTypeINTEGER源单类型POOrderEntryFSourceTranType源单类型FStatus=3STRING关闭标志POOrderFstatus状态FStockQtyFLOAT基本单位入库数量POOrderEntryFStockQty入库数量FSupplyIDSTRING供应商t_SupplierFName供应商名称FTaxRateFLOAT折扣率POOrderEntryFTaxRate折扣率FUnitGroupIDSTRING基本单位