SQL Server 排序函数 ROW_NUMBER、RANK、DENSE_RANK、INTERSECT、UNION、UNION ALL 用法总结

最新推荐文章于 2023-08-21 10:38:02 发布
最新推荐文章于 2023-08-21 10:38:02 发布
阅读量1k 收藏
点赞数
分类专栏： SQL Server 文章标签： sql server join internet sql date ide
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


13  INTERSECT

和 UNION 指令类似， INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不同的地方是， UNION 基本上是一个 OR (如果这个值存在于第一句或是第二句，它就会被选出)，而 INTERSECT 则比较像 AND ( 这个值要存在于第一句和第二句才会被选出)。 UNION 是联集，而 INTERSECT 是交集。

INTERSECT 的语法如下：

[SQL语句 1]
INTERSECT
[SQL语句 2]

假设我们有以下的两个表格，

Store_Information 表格

store_nameSalesDate
Los Angeles$1500Jan-05-1999
San Diego$250Jan-07-1999
Los Angeles$300Jan-08-1999
Boston$700Jan-08-1999

Internet Sales 表格
DateSales
Jan-07-1999$250
Jan-10-1999$535
Jan-11-1999$320
Jan-12-1999$750

而我们要找出哪几天有店面交易和网络交易。要达到这个目的，我们用以下的 SQL 语句：

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales

结果:

Date
Jan-07-1999

请注意，在 INTERSECT 指令下，不同的值只会被列出一次。


14.UNION、UNION ALL

下面的例子中使用的原始表：

Employees_China:

E_IDE_Name
01Zhang, Hua
02Wang, Wei
03Carter, Thomas
04Yang, Ming

Employees_USA:

E_IDE_Name
01Adams, John
02Bush, George
03Carter, Thomas
04Gates, Bill

使用 UNION 命令

实例

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1
UNION ALL
SQL Statement 2

使用 UNION ALL 命令

实例：

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill


liuyang1981
关注
专栏目录
SQL基础系列（八）——排序、分组排序（RANK）
weixin_44077468的博客
05-09 9428
窗口函数可以进行排序，生成序号等操作，结合查询语句，可以进行一些较为复杂的查询。本文主要介绍窗口函数的用法，以及相关的查询示例。
SQL Server中Row_number() 和 Rank() 的用法
lijuan2441988的专栏
10-15 731
1.ROW_NUMBER()基本用法： SELECT   SalesOrderID,   CustomerID,   ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber  FROM Sales.SalesOrderHeader 结果集： SalesOrderID    CustomerID    RowNumber --
sqlserver 查询中使用Union或Union All
weixin_30493401的博客
04-22 552
　　 在 程序人生网站上 看到了 这篇文章 就收藏了 哈 http://www.ourcodelife.com/article-415-1.html 　　首先，在程序人生网站上，需要负责任的指出的是在SQL Server查询中使用Union或Union All后Order by排序无效，我不确认是不是微软的bug，不过这里却是我实际编程工作的经验，但愿大家看到这篇文章后，不要再走弯路，...
Sql rank类的用法
热门推荐
jin_yunyou的博客
07-15 1万+
Sql rank类的用法
SQL rank() 用法
weixin_34252090的博客
12-12 1534
WITH vep AS ( SELECT package.OrderCode , RANK() OVER ( PARTITION BY package.OrderCode ORDER BY package.ID ) AS Index , COUNT(1) OVER...
sql-92-bnf.tar.gz_BNF SQL92_bnf_sql b_sql server bnf_sql92 bnf
09-22
- **窗口函数**：ROW_NUMBER、RANK、DENSE_RANK等，为数据分析提供了强大的工具。 2. **BNF描述**： - **基本结构**：BNF通过非终结符和终结符来描述语法规则，非终结符代表语言的更高层次结构，终结符则是语言的...
sql99.rar_SQL99 chm_sql_sql 99_sql99_sql99文档下载
09-19
2. **窗口函数**：通过OVER()子句，SQL99引入了窗口函数，如ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()等，用于计算行号、排名或基于分组的数据计算。 3. **集合操作**：SQL99支持并集（UNION）、交集...
:open_file_folder:HackerRank SQL跟踪解决方案
01-27
5. **窗口函数**：RANK()、DENSE_RANK()、ROW_NUMBER()以及LEAD()和LAG()等功能，用于处理行级别的排序和分析。 6. **集合操作**：UNION、INTERSECT和EXCEPT操作用于合并或对比不同查询的结果集。 压缩包中的...
oracle-plsql.zip_oracle_pl sql 分组查询_plsql_plsql 分组查询
09-14
5. **高级查询**：包括使用集合操作（UNION、INTERSECT、EXCEPT）和窗口函数（ROW_NUMBER、RANK、DENSE_RANK等），这些功能使查询更复杂和灵活。 6. **数据字典**：Oracle的数据字典是系统维护的一系列表和视图，...
Advanced-SQL.zip_advanced sql_高级数据库_pdf
09-24
4. **窗口函数**：如ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()等，它们提供了一种在数据集上进行行间计算的能力，对于时间序列分析和排名问题特别有用。 5. **集合操作**：...
oracle，在一条sql语句里面的wmsys.wm_concat()、row_number()、union all 的用法
笨笨鸟吃柠檬的专栏
07-10 575
select a.plan_id, '补充说明：' || a.contents as caozuo, ' ' as remarks, a.create_on, a.create_by from PROMOTION_PLAN_SUPPLEMENT a where a.plan_id = '28bae9cc089a446db7264a9ba
SQL学习笔记——rank类的用法
最新发布
William_lyx的博客
08-21 1081
参考文章：
union与union all，ROW_NUMBER()函数
wuchenq的博客
01-20 1643
1、union 使用的时候将两张报表合并，前提条件是，两张表的列是完全一致的， union自带去重功能，要完全合并，请用union all 2、ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2) 分组排序，PARTITION BY相当于 group by就是分组，order by就是排序。
SQL的RANK()OVER 划分等级的用法
foart的专栏
04-25 7537
--例子：用月份排序对号码进行分组划分等级 select imonth,vcmsisdn,phonebrand,phonetype, RANK() OVER ( PARTITION BY vcmsisdn ORDER BY imonth desc) rn from TableA
distinct 排序_SQL优化（避免排序）-union all代替 union+exists 代替 distinct
weixin_40009207的博客
11-25 1184
概述大家都知道数据库有些命令会对结果进行排序，当数据量较大时，排序会非常耗时。今天主要讨论如何避免无谓的排序，提升SQL执行效率。会进行排序的SQL 命令下面列举会排序的代表性运算：GROUP BY 子句ORDER BY 子句聚合函数(SUM,COUNT,AVG,MAX,MIN)DISTINCT集合运算(UNICON,INTERSECT,EXCEPT)窗口函数(RANK,ROW_NUMBER等)实...
Sql Server 2005 UNION ALL查询的排序无效的完美解决方案
12-04 228
在sql server中我们是不能再union查询中直接使用order by 进行排序的，因此我们必须使用一些比较巧妙的方法来得到我们想要的结果。 比如： Select ProvinceName,AuthCount FROM UserStat UNION ALL Select ‘统计’,COUNT(AuthCount) FROM UserStat 其中我们需要对前面那个查询进行...
【Oracle】SQL 数据求交集 去重 性能 join, union, unionAll, distinct, ROWID, ROW_NUMBER() OVER(PARTITION BY ...)
许恒的博客
01-07 1217
    http://www.w3school.com.cn/sql/sql_union.asp SQL UNION 和 UNION ALL 操作符 SQL Full Join SQL Select Into SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有...
MySQL 下 ROW_NUMBER / DENSE_RANK / RANK 的实现
Bryan__的专栏
05-01 4800
来自：http://blog.sina.com.cn/s/blog_562b10b901011c17.html CREATE TABLE test_rownum ( ID int, Ke CHAR(1), val INT ); INSERT INTO test_rownum SELECT 1, 'A', 1 UNION ALL SELECT 2, 'A', 2 UNIO
SqlServer ROW_NUMBER() 排序函数
极客神殿
02-08 1万+
1.使用row_number（）函数进行编号：如select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer原理：先按psd进行排序，排序完后，给每条数据进行编号。2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：select DID,customerID,totalPrice,ROW
SQLServer 2005 T-SQL 查询深度解析
6. **窗口函数**：阐述了ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG等窗口函数的使用，这些函数在处理行级排序和计算累计值时非常有用。 7. **动态SQL**：解释了如何创建和执行动态SQL语句，以应对需要在运行时确定...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值