SQL Server 排序函数 ROW_NUMBER和RANK 用法总结

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

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
:




本文转自94cool博客园博客,原文链接:http://www.cnblogs.com/94cool/archive/2010/03/05/1678913.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
6月前
|
SQL 数据库管理
第二章:基础查询与排序---SQL学习笔记
第二章:基础查询与排序---SQL学习笔记
79 0
|
6月前
|
SQL 存储
SQL组内排序
为什么要用ORDER BY来做,因为是这样的,由于采用的多线程,各个线程触发时间十分相近,但是我们需要对每一个项目进行分组,所以在此处,我们做了一个唯一标识IDENTIFICATION,每个项目每次执行时记录的6条日志里都会存储这个唯一标识。
|
11天前
|
SQL 存储 数据采集
sql中varchar转number时报错怎么解决
通过上述策略的综合运用,面对VARCHAR到NUMBER的转换挑战,不仅能够游刃有余地解决现有的问题,更能前瞻性地预防未来的隐患。在数字化转型的浪潮中,凭借其高性能、安全稳定的云产品与服务,为各类企业级应用保驾护航,助您轻松驾驭数据的海洋,实现业务的无缝扩展与全球布局。
24 0
|
3月前
|
SQL 数据挖掘
|
5月前
|
关系型数据库 MySQL
【随手记】MySQL中ROW_NUMBER()、RANK()和DENSE_RANK()函数的用法
【随手记】MySQL中ROW_NUMBER()、RANK()和DENSE_RANK()函数的用法
134 1
|
6月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之flink sql ROW_NUMBER()回退更新的机制,有相关文档介绍吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
79 1
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
55 0
|
6月前
|
SQL
SQL语句两个字段或多个字段同时order by 排序
SQL语句两个字段或多个字段同时order by 排序
979 0
|
6月前
|
SQL 数据库管理
SQL基础题----基本的SELECT语句、order by排序
SQL基础题----基本的SELECT语句 ambiguous 模糊
221 1
|
6月前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
50 0