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

1 ROW_NUMBER
SELECT ProductID, Name, Price, 
       ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRank
FROM Products

ProductID Name Price PriceRank
8 Desk 495.0000 1
10 Executive Chair 295.0000 2
9 Chair 125.0000 3
5 Mouse 14.9500 4
6 Mousepad 9.9900 5
11 Scissors 8.5000 6
4 Stapler 7.9500 7
3 Binder 1.9500 8
...

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

Name DateOrdered TotalOrderAmount BestCustomer
Bob 12/1/2005 12649.9900 1
Bob 12/19/2005 265.8500 2
Tito 12/22/2005 14.9500 1
Tito 12/18/2005 12.4400 2
Darren 1/2/2006 620.0000 1
Bruce 1/5/2006 14.9500 1
Bruce 1/4/2006 9.9900 2
Lee Ann 1/3/2006 8.5000 1
...

2 RANK

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

Name DateOrdered TotalOrderAmount BestCustomer
Bob 12/1/2005 12649.9900 1
Darren 1/2/2006 620.0000 2
Bob 12/19/2005 265.8500 3
Tito 12/22/2005 14.9500 4
Bruce 1/5/2006 14.9500 4
Tito 12/18/2005 12.4400 6
Bruce 1/4/2006 9.9900 7
Lee Ann 1/3/2006 8.5000 8
...

3 NTILE

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

ProductID Name Price Quartile
8 Desk 495.0000 1
10 Executive Chair 295.0000 1
9 Chair 125.0000 2
5 Mouse 14.9500 2
6 Mousepad 9.9900 3
11 Scissors 8.5000 3
4 Stapler 7.9500 4
3 Binder 1.9500 4

posted on 2006-03-23 19:56  周 金根  阅读(1493)  评论(1编辑  收藏  举报

导航