1.ROW_NUMBER()基本用法： SELECT SalesOrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber FROM Sales.SalesOrderHeader 结果集： SalesOrderID CustomerID RowNumber --------------- ------------- --------------- 4
1.ROW_NUMBER()基本用法：
SELECT SalesOrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID RowNumber--------------- ------------- ---------------43659 676 143660 117 243661 442 343662 227 443663 510 543664 397 643665 146 743666 511 843667 646 9 :2.RANK()基本用法:
SELECT SalesOrderID, CustomerID, RANK() OVER (ORDER BY CustomerID) AS Rank FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID Rank--------------- ------------- ----------------43860 1 144501 1 145283 1 146042 1 146976 2 547997 2 549054 2 550216 2 551728 2 557044 2 563198 2 569488 2 544124 3 13 :3.利用CTE来过滤ROW_NUMBER()的用法：
WITH NumberedRows AS( SELECT SalesOrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber FROM Sales.SalesOrderHeader)
SELECT * FROM NumberedRows WHERE RowNumber BETWEEN 100 AND 200结果集：
SalesOrderID CustomerID RowNumber--------------- ------------- --------------43759 13257 10043760 16352 10143761 16493 102 :43857 533 19943858 36 2004.带Group by的ROW_NUMBER()用法：
WITH CustomerSumAS( SELECT CustomerID, SUM(TotalDue) AS TotalAmt FROM Sales.SalesOrderHeader GROUP BY CustomerID)SELECT *, ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS RowNumber FROM CustomerSum结果集：CustomerID TotalAmt RowNumber------------- --------------- ---------------678 1179857.4657 1697 1179475.8399 2170 1134747.4413 3328 1084439.0265 4514 1074154.3035 5155 1045197.0498 672 1005539.7181 7 :5.ROW_NUMBER()或是RANK()聚合用法：
WITH CustomerSum AS( SELECT CustomerID, SUM(TotalDue) AS TotalAmt FROM Sales.SalesOrderHeader GROUP BY CustomerID)SELECT *, RANK() OVER (ORDER BY TotalAmt DESC) AS Rank--或者是ROW_NUMBER() OVER (ORDER BY TotalAmt DESC) AS Row_Number FROM CustomerSumRANK()的结果集：CustomerID TotalAmt Rank----------- --------------------- --------------------678 1179857.4657 1697 1179475.8399 2170 1134747.4413 3328 1084439.0265 4514 1074154.3035 5 :6.DENSE_RANK()基本用法：
SELECT SalesOrderID, CustomerID, DENSE_RANK() OVER (ORDER BY CustomerID) AS DenseRank FROM Sales.SalesOrderHeader WHERE CustomerID > 100结果集：SalesOrderID CustomerID DenseRank------------ ----------- --------------------46950 101 147979 101 149048 101 150200 101 151700 101 157022 101 163138 101 169400 101 143855 102 244498 102 245280 102 246038 102 246951 102 247978 102 249103 102 250199 102 251733 103 357058 103 3 :
7.RANK()与DENSE_RANK()的比较：
WITH CustomerSum AS( SELECT CustomerID, ROUND(CONVERT(int, SUM(TotalDue)) / 100, 8) * 100 AS TotalAmt FROM Sales.SalesOrderHeader GROUP BY CustomerID)SELECT *, RANK() OVER (ORDER BY TotalAmt DESC) AS Rank, DENSE_RANK() OVER (ORDER BY TotalAmt DESC) AS DenseRank FROM CustomerSum结果集：CustomerID TotalAmt Rank DenseRank----------- ----------- ------- --------------------697 1272500 1 1678 1179800 2 2170 1134700 3 3328 1084400 4 4 :87 213300 170 170667 210600 171 171196 207700 172 172451 206100 173 173672 206100 173 17327 205200 175 174687 205200 175 174163 204000 177 175102 203900 178 176 :
8.NTILE()基本用法：
SELECT SalesOrderID, CustomerID, NTILE(10000) OVER (ORDER BY CustomerID) AS NTile FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID NTile--------------- ------------- ---------------43860 1 144501 1 145283 1 146042 1 146976 2 247997 2 249054 2 250216 2 251728 2 357044 2 363198 2 369488 2 344124 3 4 :45024 29475 999845199 29476 999860449 29477 999860955 29478 999949617 29479 999962341 29480 999945427 29481 1000049746 29482 1000049665 29483 10000
9.所有排序方法对比：
SELECT SalesOrderID AS OrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber, RANK() OVER (ORDER BY CustomerID) AS Rank, DENSE_RANK() OVER (ORDER BY CustomerID) AS DenseRank, NTILE(10000) OVER (ORDER BY CustomerID) AS NTile FROM Sales.SalesOrderHeader结果集：OrderID CustomerID RowNumber Rank DenseRank NTile-------- ------------- --------- ------- --------- --------43860 1 1 1 1 144501 1 2 1 1 145283 1 3 1 1 146042 1 4 1 1 146976 2 5 5 2 247997 2 6 5 2 249054 2 7 5 2 250216 2 8 5 2 251728 2 9 5 2 357044 2 10 5 2 363198 2 11 5 2 369488 2 12 5 2 344124 3 13 13 3 444791 3 14 13 3 4 :
10.PARTITION BY基本使用方法：
SELECT SalesOrderID, SalesPersonID, OrderDate, ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY OrderDate) AS OrderRank FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL结果集：SalesOrderID SalesPersonID OrderDate OrderRank--------------- ---------------- ------------ -------------- :43659 279 2001-07-01 00:00:00.000 143660 279 2001-07-01 00:00:00.000 243681 279 2001-07-01 00:00:00.000 343684 279 2001-07-01 00:00:00.000 443685 279 2001-07-01 00:00:00.000 543694 279 2001-07-01 00:00:00.000 643695 279 2001-07-01 00:00:00.000 743696 279 2001-07-01 00:00:00.000 843845 279 2001-08-01 00:00:00.000 943861 279 2001-08-01 00:00:00.000 10 :48079 287 2002-11-01 00:00:00.000 148064 287 2002-11-01 00:00:00.000 248057 287 2002-11-01 00:00:00.000 347998 287 2002-11-01 00:00:00.000 448001 287 2002-11-01 00:00:00.000 548014 287 2002-11-01 00:00:00.000 647982 287 2002-11-01 00:00:00.000 747992 287 2002-11-01 00:00:00.000 848390 287 2002-12-01 00:00:00.000 948308 287 2002-12-01 00:00:00.000 10 :
11.PARTITION BY聚合使用方法：WITH CTETerritory AS( SELECT cr.Name AS CountryName, CustomerID, SUM(TotalDue) AS TotalAmt FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesTerritory AS ter ON soh.TerritoryID = ter.TerritoryID INNER JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = ter.CountryRegionCode GROUP BY cr.Name, CustomerID)SELECT *, RANK() OVER(PARTITION BY CountryName ORDER BY TotalAmt, CustomerID DESC) AS Rank FROM CTETerritory
结果集：
CountryName CustomerID TotalAmt Rank-------------- ------------- ----------- --------------Australia 29083 4.409 1Australia 29061 4.409 2Australia 29290 5.514 3Australia 29287 5.514 4Australia 28924 5.514 5 :Canada 29267 5.514 1Canada 29230 5.514 2Canada 28248 5.514 3Canada 27628 5.514 4Canada 27414 5.514 5 :France 24538 4.409 1France 24535 4.409 2France 23623 4.409 3France 23611 4.409 4France 20961 4.409 5 :
12.PARTITION BY求平均数使用方法：
WITH CTETerritory AS( SELECT cr.Name AS CountryName, CustomerID, SUM(TotalDue) AS TotalAmt FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesTerritory AS ter ON soh.TerritoryID = ter.TerritoryID INNER JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = ter.CountryRegionCode GROUP BY cr.Name, CustomerID)SELECT *, RANK() OVER (PARTITION BY CountryName ORDER BY TotalAmt, CustomerID DESC) AS Rank, AVG(TotalAmt) OVER(PARTITION BY CountryName) AS Average FROM CTETerritory
结果集：
CountryName CustomerID TotalAmt Rank Average-------------- ------------- ----------- ------- ------------------Australia 29083 4.409 1 3364.8318Australia 29061 4.409 2 3364.8318Australia 29290 5.514 3 3364.8318 :Canada 29267 5.514 1 12824.756Canada 29230 5.514 2 12824.756Canada 28248 5.514 3 12824.756
