文件名称：排名次查询-SQL培训PPT
文件大小：1.22MB
文件格式：PPT
更新时间：2024-05-15 15:55:35
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 *