SQL Server 排序函数 ROW_NUMBER和RANK 用法总结发布：mdxy-dxy 字体：[增加 减小]
下面的例子和SQL语句均在SQL Server 2008环境下运行通过，使用SQL Server自带的AdventureWorks数据库。
1.ROW_NUMBER()基本用法：
SELECT
SalesOrderID,
CustomerID,
ROW_NUMBER()
OVER (ORDER BY SalesOrderID) AS RowNumber
FROM
Sales.SalesOrderHeader
结果集：
SalesOrderID
CustomerID RowNumber
--------------- -------------
---------------
43659
676
1
43660
117
2
43661
442
3
43662
227
4
43663
510
5
43664
397
6
43665
146
7
43666
511
8
43667
646
9
:
2.RANK()基本用法:
SELECT
SalesOrderID,
CustomerID,
RANK() OVER
(ORDER BY CustomerID) AS Rank
FROM
Sales.SalesOrderHeader
结果集：
SalesOrderID
CustomerID Rank
--------------- -------------
----------------
43860
1
1
44501
1
1
45283
1
1
46042
1
1
46976
2
5
47997
2
5
49054
2
5
50216
2
5
51728
2
5
57044
2
5
63198
2
5
69488
2
5
44124
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
100
43760
16352
101
43761
16493
102
:
43857
533
199
43858
36 200
4.带Group by的ROW_NUMBER()用法：
WITH CustomerSum
AS
(
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
1
697
1179475.8399
2
170
1134747.4413
3
328
1084439.0265
4
514
1074154.3035
5
155
1045197.0498
6
72
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 CustomerSum
RANK()的结果集：
CustomerID
TotalAmt
Rank
----------- ---------------------
--------------------
678
1179857.4657
1
697
1179475.8399
2
170
1134747.4413
3
328
1084439.0265
4
514
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
1
47979
101
1
49048
101
1
50200
101
1
51700
101
1
57022
101
1
63138
101
1
69400
101
1
43855
102
2
44498
102
2
45280
102
2
46038
102
2
46951
102
2
47978
102
2
49103
102
2
50199
102
2
51733
103
3
57058
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
1
678
1179800 2
2
170
1134700 3
3
328
1084400 4
4
:
87
213300 170
170
667
210600 171
171
196
207700 172
172
451
206100 173
173
672
206100 173
173
27
205200 175
174
687
205200 175
174
163
204000 177
175
102
203900 178
176
:
8.NTILE()基本用法：
SELECT
SalesOrderID,
CustomerID,
NTILE(10000)
OVER (ORDER BY CustomerID) AS NTile
FROM
Sales.SalesOrderHeader
结果集：
SalesOrderID
CustomerID NTile
--------------- -------------
---------------
43860
1
1
44501
1
1
45283
1
1
46042
1
1
46976
2
2
47997
2
2
49054
2
2
50216
2
2
51728
2
3
57044
2
3
63198
2
3
69488
2
3
44124
3
4
:
45024
29475
9998
45199
29476
9998
60449
29477
9998
60955
29478
9999
49617
29479
9999
62341
29480
9999
45427
29481
10000
49746
29482
10000
49665
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 1
44501
1
2
1
1 1
45283
1
3
1
1 1
46042
1
4
1
1 1
46976
2
5
5
2 2
47997
2
6
5
2 2
49054
2
7
5
2 2
50216
2
8
5
2 2
51728
2
9
5
2 3
57044
2
10
5
2 3
63198
2
11
5
2 3
69488
2
12
5
2 3
44124
3
13 13
3 4
44791
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
1
43660
279
2001-07-01 00:00:00.000
2
43681
279
2001-07-01 00:00:00.000
3
43684
279
2001-07-01 00:00:00.000
4
43685
279
2001-07-01 00:00:00.000
5
43694
279
2001-07-01 00:00:00.000
6
43695
279
2001-07-01 00:00:00.000
7
43696
279
2001-07-01 00:00:00.000
8
43845
279
2001-08-01 00:00:00.000
9
43861
279
2001-08-01 00:00:00.000
10
:
48079
287
2002-11-01 00:00:00.000
1
48064
287
2002-11-01 00:00:00.000
2
48057
287
2002-11-01 00:00:00.000
3
47998
287
2002-11-01 00:00:00.000
4
48001
287
2002-11-01 00:00:00.000
5
48014
287
2002-11-01 00:00:00.000
6
47982
287
2002-11-01 00:00:00.000
7
47992
287
2002-11-01 00:00:00.000
8
48390
287
2002-12-01 00:00:00.000
9
48308
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
1
Australia
29061
4.409
2
Australia
29290
5.514
3
Australia
29287
5.514
4
Australia
28924
5.514
5
:
Canada
29267
5.514
1
Canada
29230
5.514
2
Canada
28248
5.514
3
Canada
27628
5.514
4
Canada
27414
5.514
5
:
France
24538
4.409
1
France
24535
4.409
2
France
23623
4.409
3
France
23611
4.409
4
France
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.8318
Australia
29061
4.409 2
3364.8318
Australia
29290
5.514 3
3364.8318
:
Canada
29267
5.514 1
12824.756
Canada
29230
5.514 2
12824.756
Canada
28248
5.514 3
12824.756
您可能感兴趣的文章:
C#拼接SQL语句
用ROW_NUMBER实现的高效分页排序
sqlserver巧用row_number和partition
by分组取top数据
sqlserver2005使用row_number()
over分页的实现方法
SqlServer2005中使用row_number()在一个查询中删除重复记录的方法
一个基于ROW_NUMBER()的通用分页存储过程代码
SqlServer
2005中使用row_number()在一个查询中删除重复记录
SQLSERVER
2005的ROW_NUMBER、RANK、DENSE_RANK的用法
SQL为查询的结果加上序号(ROW_NUMBER)
合并多个查询结果
SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
ROW_NUMBER
SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函数)
