【文件属性】：
文件名称：查询案例-SQL培训PPT
文件大小：1.22MB
文件格式：PPT
更新时间：2021-04-25 22:08:55
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
*