Notice (8): file_put_contents(): Write of 274 bytes failed with errno=28 No space left on device [CORE/src/Log/Engine/FileLog.php, line 140]

Notice: file_put_contents() [function.file-put-contents]: Write of 1108 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5131 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 3150 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5437 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 3150 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 822 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 3149 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 110 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 2789 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (512): long cache was unable to write 'a79bc20a9f7fb15e13a96e5344be3a50' to Cake\Cache\Engine\FileEngine cache [CORE/src/Cache/Cache.php, line 275]

Notice: file_put_contents() [function.file-put-contents]: Write of 2584 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 118 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 2789 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (512): long cache was unable to write 'ffbc300c2d3f3f2d772fc5e741515fbf' to Cake\Cache\Engine\FileEngine cache [CORE/src/Cache/Cache.php, line 275]

Notice: file_put_contents() [function.file-put-contents]: Write of 2584 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 90093 of 90101 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2488 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 94197 of 94197 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2488 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
SQLServer排序函数ROW_NUMBER和RANK用法总结 - 站长搜索
娱乐休闲

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

电子贺卡

电子贺卡

公司企业

校园文学

劳动与社会保障

社会学

设计艺术

外交/国际关系

外交/国际关系

探索/科学考察

家用电器

机关/科研机构

古典文学

群众文娱活动研究

首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

SQLServer排序函数ROW_NUMBER和RANK用法总结

数据库操作教程 2022-09-23 16:41:35 转载来源: 网络整理/侵权必删

1.ROW_NUMBER()基本用法：SELECT SalesOrderID, CustomerID, ROW_NUMBER()OVER(ORDERBYSalesOrderID)ASRowNumber FROMSales

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

标签: SQLServer 排序 函数 ROW NUMBER RANK 用法 总结


声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: SQLServer2008的逻辑查询处理步骤
上一篇: SQLServer中查看SQL句子执行所用的时间

猜您喜欢

热门文章

热点

标签列表

站长搜索

http://www.adminso.com

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

会员注册

会员登陆

在线投稿

提交网站

站长资讯

网站列表

域名库

关键词库

帮助中心

免责声明

隐私保护

域名查询

广告赞助

网站地图

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持