SQL Server 排序函数 ROW_NUMBER和RANK 用法总结(sql的排序函数)

1.ROW_NUMBER()基本用法：

SELECT SalesOrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID RowNumber--------------- ------------- --------------- 9 :2.RANK()基本用法:

SELECT SalesOrderID, CustomerID, RANK() OVER (ORDER BY CustomerID) AS Rank FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID Rank--------------- ------------- ---------------- 1 1 1 1 2 2 2 2 2 2 2 2 3 :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 AND 结果集：

SalesOrderID CustomerID RowNumber--------------- ------------- -------------- : .带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------------- --------------- --------------- . . . . . . . 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----------- --------------------- -------------------- . . . . . 5 :6.DENSE_RANK()基本用法：

SELECT SalesOrderID, CustomerID, DENSE_RANK() OVER (ORDER BY CustomerID) AS DenseRank FROM Sales.SalesOrderHeader WHERE CustomerID > 结果集：SalesOrderID CustomerID DenseRank------------ ----------- -------------------- 3 :

7.RANK()与DENSE_RANK()的比较：

WITH CustomerSum AS( SELECT CustomerID, ROUND(CONVERT(int, SUM(TotalDue)) / , 8) * 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----------- ----------- ------- -------------------- 1 2 3 4 4 : :

8.NTILE()基本用法：

SELECT SalesOrderID, CustomerID, NTILE() OVER (ORDER BY CustomerID) AS NTile FROM Sales.SalesOrderHeader结果集：SalesOrderID CustomerID NTile--------------- ------------- --------------- 1 1 1 1 2 2 2 2 2 2 2 2 3 4 :

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() OVER (ORDER BY CustomerID) AS NTile FROM Sales.SalesOrderHeader结果集：OrderID CustomerID RowNumber Rank DenseRank NTile-------- ------------- --------- ------- --------- -------- 1 1 1 1 1 2 1 1 1 3 1 1 1 4 1 1 2 5 5 2 2 6 5 2 2 7 5 2 2 8 5 2 2 9 5 2 2 5 2 2 5 2 2 5 2 3 3 3 3 4 :

.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--------------- ---------------- ------------ -------------- : -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. : -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. -- ::. :

.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 4. 1Australia 4. 2Australia 5. 3Australia 5. 4Australia 5. 5 :Canada 5. 1Canada 5. 2Canada 5. 3Canada 5. 4Canada 5. 5 :France 4. 1France 4. 2France 4. 3France 4. 4France 4. 5 :

.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 4. 1 .Australia 4. 2 .Australia 5. 3 . :Canada 5. 1 .Canada 5. 2 .Canada 5. 3 .

推荐整理分享SQL Server 排序函数 ROW_NUMBER和RANK 用法总结(sql的排序函数)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:sql排序语句,sqlserver排序函数,sqlserver排序语句,sql 排序 序号,sqlserver排序规则选择什么,sqlserver排序语句,sql排序语句,sqlserver排序语句,内容如对您有帮助，希望把文章链接给更多的朋友！

数据库Left join , Right Join, Inner Join 的相关内容，非常实用 一.先看一些最简单的例子例子TableAaidadate1aaa3TableBbidbdate1bbb4两个表a,b相连接,要取出id相同的字段select*fromainnerjoinbona.aid=b.bid这是仅取出匹配的数据.

MySql 安装时的错误 解决办法1.卸载重装，不设root密码，因为MySql默认密码为空。解决办法2.保持现在的密码，打开MySQLCommandLineClient输入你的密码（安装时设的那个，如果密

SQL Server 中查看SQL句子执行所用的时间 setstatisticsprofileonsetstatisticsioonsetstatisticstimeongo你执行的SQL语句gosetstatisticsprofileoffsetstatisticsiooffsetstatisticstimeoff执行完后点消息即可。