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

发布时间：2020-12-12 15:11:56 所属栏目：MsSql教程来源：网络整理

导读：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 ? ? ProductID Name Price PriceRank 8 Desk 495.0000 1 10 Ex

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

ProductIDNamePricePriceRank 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

NameDateOrderedTotalOrderAmountBestCustomer 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,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

NameDateOrderedTotalOrderAmountBestCustomer 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,NTILE(4) OVER (ORDER BY Price DESC) as Quartile
 FROM Produts

ProductIDNamePriceQuartile 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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

1 ROW_NUMBER