文件名称：查询案例-SQL培训PPT
文件大小：1.22MB
文件格式：PPT
更新时间：2024-05-15 15:55:35
SQL SQL教程
查询案例 * 1：随机取十条记录 newid() 函数 (Newid, CheckSum, rand) 2：从第3 到 第8的记录 (有序的与无序) 3: 列出分仓库存表的不同记录（去处重复数据） 4：列出销售订单某物料最高单价及最低单价的记录 5：假设已知仓库A,B, C，列出物料在A,B,C仓库的库存数量。 物料， A , B, C HW01 100 200 300 6：按物料供应商，统计金额，数量，供应商平均价(降序)显示。（条件：采购中物料高于平均采购单价） A: 物料供应商得到平均价 B:排序 B:物料得到平均价 C:比较 7：列出各供应商的最后采购订单。 说明：供应商：BPVnd（VndID, VndName）, 销售订单PUOrd (CrdID, DocNum, DocDate， DocTotal) 显示信息：VndID, VndName, DocNum, DocDate, Qty)。 注意：连接关系：BPVnd.VndID = PUOrd.CrdID 8：列出各客户的订单金额总计及金额总计排名和订单凭证笔数,并且按金额总计降序显示。 说明：客户：BPClt（CltID, CltName）, 销售订单SAOrd (CrdID, DocNum, DocTotal) 显示信息：CltID, CltName, SumDocTotal, SumDocTotalIndex, DocCount)。 注意：连接关系：BPClt.CltID = SAOrd.CrdID 9：列出各仓库中存货数量多的前三名物料,并按数量降序显示。 4：列出销售订单某物料最高单价及最低单价的记录 Select * From SAOrdA TD Where TD.ItmID = 'Hw-01' and (( TD.Price = (Select Max(Price) From SAOrdA T Where T.ItmID = TD.ItmID ) ) or (( TD.Price = (Select Min(Price) From SAOrdA T Where T.ItmID = TD.ItmID ) ) )) Select * From SAOrdA TD Where TD.ItmID = 'Hw-01' and (( TD.Price = (Select Max(Price) From SAOrdA T Where T.ItmID = TD.ItmID ) )) Union Select * From SAOrdA TD Where TD.ItmID = 'Hw-01' and (( TD.Price = (Select Min(Price) From SAOrdA T Where T.ItmID = TD.ItmID ) ) ) 5：假设已知仓库A,B, C，列出物料在A,B,C仓库的库存数量。 物料， A , B, C HW01 100 200 300 Select ItmID, Sum(case when WhsID = '04' then OnHand else 0 end) as AOnHand, Sum(case when WhsID = '01' then OnHand else 0 end) as BOnHand, Sum(case WhsID when '03' then OnHand else 0 end) as COnHand From BCStk Group By ItmID 6：按物料供应商，统计金额，数量，供应商平均价(降序)显示。（条件：采购中物料高于平均采购单价） A: 物料供应商得到平均价 B:排序 B:物料得到平均价 C:比较 Select S.CrdID, S.ItmID, S.LineSum, S.SumQty, (case when S.SumQty = 0 then null else S.LineSum / S.SumQty end) AvgPrice, S1.AvgPrice1 ItmAvgPrice From ( Select TM.CrdID, TD.ItmID, Sum(TD.LineSum) LineSum, Sum(TD.Qty) SumQty From PURevA TD Left Join PURev TM On TD.DocEntry = TM.DocEntry Group By TM.CrdID, TD.ItmID ) S Left Join (Select ItmID, LineSum, SumQty, (case when SumQty = 0 then null else LineSum / SumQty end) AvgPrice1 From ( Select TD.ItmID, Sum(TD.LineSum) LineSum, Sum(TD.Qty) SumQty From PURevA TD Left Join PURev TM On TD.DocEntry = TM.DocEntry Group By TD.ItmID ) S ) S1 On S.ItmID = S1.ItmID where (case when S.SumQty = 0 then null else S.LineSum / S.SumQty end) > S1.AvgPrice1 Order By S.ItmID, (case when S.SumQty = 0 then null else S.LineSum / S.SumQty end) Desc 7：列出各供应商的最后采购订单。 说明：供应商：BPVnd（VndID, VndName）, 销售订单PUOrd (CrdID, DocNum, DocDate， DocTotal) 显示信息：VndID, VndName, DocNum, DocDate, Qty)。 注意：连接关系：BPVnd.VndID = PUOrd.CrdID Select TB.VndID, TB.VndName, TM.DocNum, TM.DocDate, TM.DocTotal From BPVnd TB Left Join PUOrd TM On TB.VndID = TM.CrdID and TM.DocNum = (Select Top 1 T.DocNum From PUOrd T Where T.CrdID = TM.CrdID Order By T.DocDate Desc) Order By TB.VndID 8：列出各客户的订单金额总计及金额总计排名和订单凭证笔数,并且按金额总计降序显示。 说明：客户：BPClt（CltID, CltName）, 销售订单SAOrd (CrdID, DocNum, DocTotal) 显示信息：CltID, CltName, SumDocTotal, SumDocTotalIndex, DocCount)。 注意：连接关系：BPClt.CltID = SAOrd.CrdID Select TB.CltID, TB.CltName, S1.SumDocTotal, S1.DocCount, Count(TB.CltID) + Isnull(S1.SumDocTotalIndex, 1) SumDocTotalIndex From BPClt TB Left Join (Select T.CrdID, Sum(T.DocTotal) SumDocTotal, Count(T.DocNum) DocCount, 0 SumDocTotalIndex From SAOrd T Group By T.CrdID) S1 On TB.CltID = S1.CrdID Left Join (Select T.CrdID, Sum(T.DocTotal) SumDocTotal1 From SAOrd T Group By T.CrdID) S2 On Isnull(S2.SumDocTotal1, 0) >= Isnull(S1.SumDocTotal, 0) Group By TB.CltID, TB.CltName, S1.SumDocTotal, S1.DocCount, S1.SumDocTotalIndex Order By S1.SumDocTotal Desc 9：列出各仓库中存货数量多的前三名物料,并按数量降序显示。 --BCStk(ItmID, WhsID, OnHand) (显示信息：ItmID, WhsID, OnHand, 名次)。注意：名次连续 Select * From (Select TB.WhsID, TB.ItmID, TB.OnHand, (Select Count(Distinct T.OnHand) From BcStk T Where T.WhsID = TB.WhsID and T.OnHand >= TB.Onhand) HandIndex From BCStk TB ) S Where S.HandIndex <= 3 Order By WhsID, HandIndex *