mysql的组内排序生成序号_sql 分组查询，组内排序，组内添加序号（SQL Server 排序函数 ROW

本文链接：https://blog.csdn.net/weixin_33148993/article/details/113137656

下面的例子和SQL语句均在SQL Server 2008环境下运行通过，使用SQL Server自带的AdventureWorks数据库。

-- 添加序列号

-- 行号用法：

ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber

-- 排序函数的用法：

RANK() OVER(PARTITION BY CountryName ORDER BY TotalAmt, CustomerID DESC) AS Rank

SELECT *,

RANK() OVER(PARTITION BY CountryName ORDER BY TotalAmt, CustomerID DESC) AS Rank

FROM CTETerritory

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

(

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

mysql的组内排序生成序号_sql 分组查询，组内排序， 组内添加序号 （SQL Server 排序函数 ROW_NUMBER和RANK 用法总结）...

mysql的组内排序生成序号_sql 分组查询，组内排序，组内添加序号（SQL Server 排序函数 ROW_NUMBER和RANK 用法总结）...