【文件属性】：
文件名称：排名次查询-SQL培训PPT
文件大小：1.22MB
文件格式：PPT
更新时间：2021-04-25 22:08:55
SQL SQL教程
排名次查询
在SQL2005前的排名查询。
SQL2005的排名查询。
ROW_NUMBER OVER()
DENSE_RANK() Over() , RANK() Over(), NTILE(n) Over()
Sum() Over() 等 做分组聚合
注意并列情况。
*
ROW_NUMBER OVER() 做排名次
Select AbsID, ItmID, WhsID, Qty,
ROW_NUMBER() Over(Order BY Qty ) ROWIndex
From SAOrdA TD
Order By ROWIndex
Select AbsID, ItmID, WhsID, Qty,
ROW_NUMBER() Over(PARTITION BY AbsID,ItmID Order BY Qty ) ROWIndex,
DENSE_RANK() Over(PARTITION BY AbsID,ItmID Order BY Qty ) DENSEIndex
From SAOrdA TD Order By AbsID,ItmID, ROWIndex
Select AbsID, ItmID, WhsID, Sum(Qty) As SumQty,
ROW_NUMBER() Over(Order BY Sum(Qty) ) SumQtyIndex
From SAOrdA TD
Group By AbsID, ItmID, WhsID
Order By SumQtyIndex
DENSE_RANK() Over() , RANK() Over(), NTILE(n) Over() 做分组排名
Select AbsID, ItmID, WhsID, Qty,
DENSE_RANK() Over(PARTITION BY AbsID,ItmID Order BY Qty ) DENSEIndex,
RANK() Over(PARTITION BY AbsID,ItmID Order BY Qty ) RANKIndex,
NTILE(3) Over(PARTITION BY AbsID,ItmID Order BY Qty ) NTILEIndex
From SAOrdA TD
Order By AbsID, ItmID, DENSEIndex, RANKIndex, NTILEIndex
Sum() Over() 等 做分组聚合
Select AbsID, ItmID, WhsID, Qty, ROW_NUMBER() Over(Order BY Qty ) As QtyIndex,
SUM(Qty) OVER(PARTITION BY AbsID, ItmID) AS AbsTotal,
Max(ItmName) OVER(PARTITION BY AbsID, ItmID) AS MaxItmName
From SAOrdA TD
Order By AbsID, ItmID, WhsID
Select AbsID, ItmID, WhsID, Qty, ROW_NUMBER() Over(Order BY Qty ) As QtyIndex,
SUM(Qty) OVER(PARTITION BY AbsID, ItmID) AS AbsTotal,
Max(ItmName) OVER(PARTITION BY AbsID, ItmID) AS MaxItmName
From SAOrdA TD
Order By AbsID, ItmID, WhsID
*