SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE

SQLServer2005新增序列功能的几个函数：ROW_NUMBER, RANK, DENSE_RANK, and NTILE

1 ROW_NUMBER

SELECT ProductID, Name, Price,        ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products

 

 

ProductIDNamePricePriceRank8Desk495.0000110Executive Chair295.000029Chair125.000035Mouse14.950046Mousepad9.9900511Scissors8.500064Stapler7.950073Binder1.95008...

 

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount,        ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab        INNER JOIN Orders AS o ON            o.OrderID = tab.OrderID        INNER JOIN Customers AS c ON            c.CustomerID = o.CustomerID

 

 

NameDateOrderedTotalOrderAmountBestCustomerBob12/1/200512649.99001Bob12/19/2005265.85002Tito12/22/200514.95001Tito12/18/200512.44002Darren1/2/2006620.00001Bruce1/5/200614.95001Bruce1/4/20069.99002Lee Ann1/3/20068.50001...

2 RANK

 

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount,        RANK() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab        INNER JOIN Orders AS o ON            o.OrderID = tab.OrderID        INNER JOIN Customers AS c ON            c.CustomerID = o.CustomerID

 

 

NameDateOrderedTotalOrderAmountBestCustomerBob12/1/200512649.99001Darren1/2/2006620.00002Bob12/19/2005265.85003Tito12/22/200514.95004Bruce1/5/200614.95004Tito12/18/200512.44006Bruce1/4/20069.99007Lee Ann1/3/20068.50008...

3 NTILE

SELECT ProductID, Name, Price, NTILE(4) OVER (ORDER BY Price DESC) as Quartile
FROM Produts

 

 

ProductIDNamePriceQuartile8Desk495.0000110Executive Chair295.000019Chair125.000025Mouse14.950026Mousepad9.9900311Scissors8.500034Stapler7.950043Binder1.95004