好湿?好紧?好多水好爽自慰,久久久噜久噜久久综合,成人做爰A片免费看黄冈,机机对机机30分钟无遮挡

主頁 > 知識庫 > SQL Server 排序函數 ROW_NUMBER和RANK 用法總結

SQL Server 排序函數 ROW_NUMBER和RANK 用法總結
熱門標簽：長沙智能外呼系統 廣東防封卡外呼系統原理是什么 菏澤語音電銷機器人加盟公司 分享百度地圖標注多個位置 外呼系統改進 知名電銷機器人價格 湖南電腦外呼系統平臺 地圖標注牌 電銷機器人公司 需要哪些牌照

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中Partition By及row_number 函數使用詳解
  • SQLSERVER分頁查詢關于使用Top方式和row_number()解析函數的不同
  • ROW_NUMBER SQL Server 2005的LIMIT功能實現(ROW_NUMBER()排序函數)
  • SQL Server中row_number函數的常見用法示例詳解

標簽：天水 商洛 西寧 珠海 呼和浩特 泉州 福建 美容院

巨人網絡通訊聲明：本文標題《SQL Server 排序函數 ROW_NUMBER和RANK 用法總結》，本文關鍵詞  SQL,Server,排序,函數,ROW,NUMBER,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《SQL Server 排序函數 ROW_NUMBER和RANK 用法總結》相關的同類信息！
  • 本頁收集關于SQL Server 排序函數 ROW_NUMBER和RANK 用法總結的相關信息資訊供網民參考！
    • 推薦文章
    主站蜘蛛池模板： 韩国r级未删减在线观看 | 上课把女同桌内裤摸湿| 人c交| 黑豹×人类h嗯太大了嗯不| 别进的太深受不了太大了| 《情欲电车2》日本| 91精品一区国产高清在线gif| www.免费视频??????| 钢铁侠| 美女视频黄频女a大全| 古装一级毛片免费观看视频| 91久久婷婷国产麻豆精品电影| 重口XXOO变态另类毛片| freefromvideos性欧美整片| 国产高清???精| 好想被男人吃出水水| 亚洲免费视频一区| 麻豆精品国产一二三产区风险| 亚洲另类小说交换| 无码国产精品一区二区免费式直播| 国产精品福利| 国产黄色免费| 春暖花开sex8亚洲区在线播放| 硬度不够吃什么药调理| 一本久道久久综合多人| 私人玩物全新白丝黄瓜道具| 嫩嫩草的香味2未删减版下载| 国产做受???777cos游戏| 三男一女做爰视频试看| 国产精品www色诱视频| 奶大灬大灬大灬硬灬爽灬无码视频| 韩国videos18高清hd| 99精品久久99久久久久| 啊啊好深好猛啊| 一本色道久久综合无码的注意事项| 无遮挡特级一级毛| 风流娇妻| 91成人免费观看| 蘑菇直播app下载| 国产精品www夜色视频| 大ji巴放进女人动态图|