ROW_NUMBER() 函数

最新推荐文章于 2025-05-29 08:51:30 发布
最新推荐文章于 2025-05-29 08:51:30 发布
阅读量2.4k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： MS Sqlserver 文章标签： sql server insert null oracle table 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/cooleader320/article/details/2645222
本文介绍了 SQL Server 中的排序函数及其应用场景，包括 ROW_NUMBER() 函数的基本语法、使用方法和优势。通过示例展示了如何利用这些函数进行高效的数据排序和分页。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

全部四个排序函数都遵循类似的语法模式：

排序函数

() OVER(

  [PARTITION BY ]

  ORDER BY )

 

该函数只能在查询的两个子句中指定 — 在 SELECT 子句或 ORDER BY 子句中

     

 

实验数据：

 

 

 

 

 

 

 

USE demo

CREATE TABLE SpeakerStats

(

--演讲者的名字

  speaker        VARCHAR(10) NOT NULL PRIMARY KEY,

-- 议题

 track          VARCHAR(10) NOT NULL,

-- 平均得分

 score          INT         NOT NULL,

-- 填写评价的与会者相对于参加会议的与会者数量的百分比

 pctfilledevals INT         NOT NULL,

--该演讲者发表演讲的次数

  numsessions    INT         NOT NULL

)



 


 


 


 


 


 


 


 


 


 


 


 


 


 












SET NOCOUNT ON


INSERT INTO SpeakerStats VALUES('Dan',     'Sys', 3, 22, 4)


INSERT INTO SpeakerStats VALUES('Ron',     'Dev', 9, 30, 3)


INSERT INTO SpeakerStats VALUES('Kathy',   'Sys', 8, 27, 2)


INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',  9, 30, 3)


INSERT INTO SpeakerStats VALUES('Joe',     'Dev', 6, 20, 2)


INSERT INTO SpeakerStats VALUES('Robert',  'Dev', 6, 28, 2)


INSERT INTO SpeakerStats VALUES('Mike',    'DB',  8, 20, 3)


INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)


INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)


INSERT INTO SpeakerStats VALUES('Brian',   'Sys', 7, 22, 3)


INSERT INTO SpeakerStats VALUES('Kevin',   'DB',  7, 25, 4)


 


 


 




  • 

      ROW_NUMBER() 向查询的结果行提供连续的整数值










 


得分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL Server 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。


       如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，


您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高


的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词


典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 — score、


pctfilledevals、numsessions 和 speaker — 是唯一的，因此结果是确定的：


 




新函数的好处：


l   效率


                       SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。SQL Server 2005 查询的性能恶化是线性的，而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的


 


l   简单


                        SQL Server 2000 查询，它返回与上一个查询相同的结果






 
SELECT

  (SELECT COUNT(*)

   FROM SpeakerStats AS S2

   WHERE S2.score > S1.score

     OR (S2.score = S1.score

         AND S2.pctfilledevals > S1.pctfilledevals)

     OR (S2.score = S1.score

         AND S2.pctfilledevals = S1.pctfilledevals

         AND S2.numsessions > S1.numsessions)

     OR (S2.score = S1.score

         AND S2.pctfilledevals = S1.pctfilledevals

         AND S2.numsessions = S1.numsessions

         AND S2.speaker < S1.speaker)) + 1 AS rownum,

  speaker, track, score, pctfilledevals, numsessions

FROM SpeakerStats AS S1

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker







  •   通过行号来分页


           SQL Server2005的row_number比Oracle的更先进。因为它把Order by集成到了一 起，   不用像Oracle那样还要用子查询进行封装




  


 








 


上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：


然后，对于所请求的每个页，发出以下查询，只有属于预期页的行才会被扫描








 


 


 


  • 

    分段


            在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列


 


表，以标识应该为其独立计算排序值的行组。


 


在 PARTITION BY 子句中指定 track 列会使得为具有相同 track 的每个行组单独计算行号。


 


 


SELECT track, 


  ROW_NUMBER() OVER(


    PARTITION BY track 


    ORDER BY score DESC, speaker) AS pos, 


  speaker, score


FROM SpeakerStats


ORDER BY track, score desc, speaker


 

 


 


 


 

                

        

    

    
  



    



                



	

		

			

				
					
SQL ROW_NUMBER函数概述

				
			

			

				

					culuo4781的博客
				

				

					07-18
					
					2601
					
				

			

		

		

			
				
In this article, we’re going to discuss the SQL ROW_NUMBER function. This is a continuation of the SQL essential series. In this guide, I’ll explain what a window function is all about, and you’ll...

			
		

	



                

            
              



	

		

			

				
					
ROW_NUMBER()函数

				
			

			

				

					09-30
				

			

		

		

			
				
关于ROW_NUMBER()函数使用详细。。。。。。。。。

			
		

	



              


  
              

                


	

		

			

				
					
Row_number()函数用法

					
热门推荐

				
			

			

				

					Zhuxxxxx的博客
				

				

					03-28
					
					8万+
					
				

			

		

		

			
				
Row_number()函数是SQL SERVER系统函数中的一种，它为结果集的分区中的每一行分配一个连续的整数。简单的说就是生成一个独表，序号以每个分区的第一行开头，下面是其基本语法：

正在上传…重新上传取消





·PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的行号。PARTITION BY子句是可选的。如果未指定，ROW_NUMBER()函数会将整个结果集视为单个分区。

·ORDER BY子句定义结果集的每个分区...

			
		

	





	

		

			

				
					
MySQL 中 ROW_NUMBER() 函数详解

				
			

			

				

					qq_44805265的博客
				

				

					05-29
					
					2754
					
				

			

		

		

			
				
MySQL中的ROW_NUMBER()函数详解：ROW_NUMBER()是SQL窗口函数，为查询结果中的每一行分配唯一连续序号。其基本语法包含PARTITION BY（分组）和ORDER BY（排序）子句，特点是严格递增、不重复处理相同值。该函数适用于分页查询、删除重复数据、分组取TopN等场景，与RANK()和DENSE_RANK()不同，它不会共享排序值。MySQL 8.0+原生支持，低版本可通过变量模拟。使用时需注意性能优化，如创建适当索引、减少计算范围，并避免常见错误如未正确指定排序规则。

			
		

	



		

			
		



	

		

			

				
					
ROW_NUMBER()函数的使用

				
			

			

				

					weixin_34361881的博客
				

				

					09-29
					
					202
					
				

			

		

		

			
				
Row_number() over只是用于把查询的数据，为他增加一列连续的ID数
ROW_NUMBER()函数是Sql2005中新添的一个函数。通常它被用在分页的SQL语句中。
微软官方的对此函数的描述是：返回结果集分区内行的序列号，每个分区的第一行从1开始。
我对此的理解：利用此函数可以为表中的某个字段建立序列，从1开始。就是说，根据已存在的某列，利用此函数可建立一新列，新列是数字，...

			
		

	





	

		

			

				
					
row_number函数

				
			

			

				

					lipan1413的博客
				

				

					11-04
					
					710
					
				

			

		

		

			
				
row_number函数）错误的先分组后排序正确分组排序函数
故事开始：前天室友收到中国移动苏州部的面试，问了一个窗口函数的问题，他提了一个问题，既然窗口函数实现的是组内排序，为什么我不可以先对数据细分组，然后在进行排序呢。然后，就有了一下的实验。
错误的先分组后排序

这显然是不对的，但是我也想了一下，怎么可以这样使用呢，group by不是去重了吗，怎么可能显示出来11 22 33 这种东西呢...

			
		

	





	

		

			

				
					
ROW_NUMBER 函数

				
			

			

				

					weixin_34200628的博客
				

				

					09-12
					
					184
					
				

			

		

		

			
				
ROW_NUMBER 是 SQL 2005 中新增的函数， 多用于分页， 基本的语法为




1



ROW_NUMBER() OVER([<partition_by_clause>] <order_by_clause>)





其中， 分区语句是可选的， 排序语句是必须的， 比如这样的语句：




1
2
3
4
5...

			
		

	





	

		

			

				
					
SQL Server中row_number函数的常见用法示例详解

				
			

			

				

					12-14
				

			

		

		

			
				
一.SQL Server Row_number函数简介 ROW_NUMBER()是一个Window函数，它为结果集的分区中的每一行分配一个连续的整数。 行号以每个分区中第一行的行号开头。 以下是ROW_NUMBER()函数的语法实例：  select *，row_...

			
		

	





	

		

			

				
					
SQLServer中Partition By及row_number 函数使用详解

				
			

			

				

					12-15
				

			

		

		

			
				
在SQL Server中，`PARTITION BY` 和 `ROW_NUMBER()` 是两种非常重要的分析函数，它们在处理大数据集时尤其有用。`PARTITION BY` 用于将结果集分成多个分区，而`ROW_NUMBER()` 用于为每个分区内的行分配唯一的序列号...

			
		

	





	

		

			

				
					
深入探讨:oracle中row_number() over()分析函数用法

				
			

			

				

					09-10
				

			

		

		

			
				
Oracle数据库中的`row_number() over()`分析函数是一个非常实用的工具，用于为数据集中的每一行分配一个唯一的整数，这个数字基于指定的排序条件。在处理大数据集时，它可以帮助我们进行分页、排名或者在复杂的查询...

			
		

	





	

		

			

				
					
row_number()函数

				
			

			

				

					聆听
				

				

					09-03
					
					3447
					
				

			

		

		

			
				
语法格式：row_number() over(partition by 分组列 order by排序列 desc)


SELECT
	ROW_NUMBER() OVER(PARTITION BY sh.BUSMAIINFID ORDER BY nvl(sh.TIMESTAMP, sh.CREATEDATE) DESC)AS RN,
	sh.ORGID,
	sh.BUSMAIINFID,
	sh.TIMESTAMP,
	sh.CREATEDATE
FROM
	REG_NETREGINFOACC sh
WH.

			
		

	





	

		

			

				
					
oracle的row_numer()函数的使用

				
			

			

				

					01-17
				

			

		

		

			
				
介绍了 row_number() over(order by column asc) 函数和 row_number() over(partition by column1 order by column2 asc)  的使用实例和方法

			
		

	





	

		

			

				
					
row_number() 函数

				
			

			

				

					秋名山的放牛娃的博客
				

				

					06-09
					
					636
					
				

			

		

		

			
				
row_number() over (partition by para1 order by col1[col2...] [desc|asc])表示根据para1分组，在分组内部根据col1排序，而次函数计算的值就表示每组内部排序后的顺序编码（组内连续的唯一的）。
下面展示的是我根据每月和usercode分组，根据时间降序，目的是为了找到每月的最新的一条h_afeecost的费用的数值。

			
		

	





	

		

			

				
					
SQL Server数据库ROW_NUMBER()使用详解

				
			

			

				

					weixin_30508309的博客
				

				

					01-30
					
					1385
					
				

			

		

		

			
				
SQL Server数据库ROW_NUMBER()函数的使用是本文我们要介绍的内容，接下来我们就通过几个实例来一一介绍ROW_NUMBER()函数的使用。
实例如下：
1.使用row_number（）函数进行编号，如
selectemail,customerID,ROW_NUMBER()over(orderbypsd)asrowsfromQT_Customer
原理：先按...

			
		

	





	

		

			

				
					
row_number()函数用法

				
			

			

				

					zhiguo2010的专栏
				

				

					05-26
					
					1220
					
				

			

		

		

			
				
 row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示：  select row_number() over(order by field1) as row_number,* from t_table    其中row_number列是由row_number函数生成的序号列。在使用row_nu

			
		

	





	

		

			

				
					
SQL Server 2005的ROW_NUMBER()分页效率比较

				
			

			

				

					weixin_30725315的博客
				

				

					03-07
					
					179
					
				

			

		

		

			
				
很多年以后，还有很多程序员不知道SQL Server2005有了更方便的分页方法，这就是ROW_NUMBER()函数。我们知道SQL2000时代的分页方式是TOP加NOT IN截取中间数据，效率也是很不错的，但这两种效率到底如何呢，我们这次以一万、十万和百万数据量的数据做演示，比较这两种分页方式的效率。另外为何使用TOP+NOT IN来和ROW_NUMBER()比较，是因为和游标方式及ISNULL...

			
		

	





	

		

			

				
					
Oracle中 ROW_NUMBER()的语法及在对应不同需求下应如何使用

				
			

			

				

					sxk1101的博客
				

				

					06-28
					
					3189
					
				

			

		

		

			
				
Oracle数据库中的函数是一个窗口函数，它为查询结果集中的每一行分配一个唯一的序号。这个函数在数据分析、分页查询、数据去重和排名问题等方面非常有用。

			
		

	





	

		

			

				
					
关于ROW_NUMBER()函数

				
			

			

				

					fredlau 痞子刘的博客
				

				

					04-16
					
					822
					
				

			

		

		

			
				
 关于ROW_NUMBER()函数原文地址：http://hi.baidu.com/fuhengyu/blog/item/adb30aafe4df88c87cd92ae9.html微软先前发布的MSSQL2005,对TSQL进行了小规模的加强，有些函数的确非常实用。就比如ROW_NUMBER()函数，实现了原先一直要 用存储过程来进行大数据分页的功能。现在有了这个函数，只要简单一个语句就能解决

			
		

	





	

		

			

				
					
sql 窗口函数ROW_NUMBER() 分组/排序/计数

				
			

			

				

					weixin_46484674的博客
				

				

					09-07
					
					1万+
					
				

			

		

		

			
				
分区的，使用它 为返回的结果集中的每一行 生成一个序列号（为行分配序号），第一个数字以1开头。CTE、存储过程中使用。分组、排序、计数都能使用

			
		

	





	

		

			

				
					
row＿number函数

					
最新发布

				
			

			

				

					07-05
				

			

		

		

			
				
### 什么是 ROW_NUMBER() 函数？

ROW_NUMBER() 是 SQL 中一个重要的窗口函数，主要用于为查询结果集中的每一行分配一个唯一的连续整数编号。这个编号的生成基于指定的排序顺序，并且可以在每个分区（如果使用了 `PARTITION BY` 子句）内独立计算[^3]。

与传统的行号功能不同，ROW_NUMBER() 提供了更强大的灵活性和控制能力，允许用户根据特定需求对数据进行编号。它广泛应用于数据分页、排名分析以及复杂的数据处理任务中。

### ROW_NUMBER() 的基本语法

```sql
ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```

- `PARTITION BY`：将数据划分为多个逻辑分区，ROW_NUMBER() 在每个分区内单独计数。
- `ORDER BY`：定义每一行在分区内的排序规则，从而决定编号的顺序。

### 常见用途和使用示例

#### 示例1: 对查询结果添加行号

当需要为查询结果的每一行分配唯一编号时，可以使用 ROW_NUMBER() 来实现。例如，从 `test_user` 表中查询所有记录并按 `id` 排序后添加编号列：

```sql
SELECT 
    ROW_NUMBER() OVER (ORDER BY id) AS 编号,
    *
FROM test_user;
```

该语句会在结果集中新增一列“编号”，并按照 `id` 升序排列为每条记录分配递增的整数[^2]。

#### 示例2: 按分组生成行号

在某些场景下，可能希望在不同的数据分组内部重新开始编号。此时可以结合 `PARTITION BY` 使用。例如，在 `orders` 表中按客户分组，并为每个客户的订单编号：

```sql
SELECT 
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq
FROM orders;
```

此查询会为每个 `customer_id` 分区内的订单按日期排序并分配序列号，适用于跟踪客户订单的先后顺序。

#### 示例3: 数据去重与筛选

ROW_NUMBER() 可用于识别和删除重复数据。例如，查找 `employees` 表中每个部门工资最高的员工：

```sql
WITH RankedEmployees AS (
    SELECT 
        department_id,
        employee_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT *
FROM RankedEmployees
WHERE rank = 1;
```

通过在每个部门内按工资降序排列并选择排名为1的记录，可以快速获取每个部门薪资最高的员工信息。

#### 示例4: 实现分页查询

在 Web 应用中，数据分页是常见的需求。ROW_NUMBER() 可以帮助构建高效的分页机制。例如，获取第 2 页的数据，每页显示 10 条记录：

```sql
WITH PagedData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY id) AS row_num,
        *
    FROM products
)
SELECT *
FROM PagedData
WHERE row_num BETWEEN 11 AND 20;
```

通过限制 `row_num` 的范围，可以轻松地提取特定页面的数据内容。

### 注意事项

尽管 ROW_NUMBER() 非常强大，但在使用时也需要注意以下几点：

- **唯一性问题**：当 `ORDER BY` 列存在重复值时，ROW_NUMBER() 仍然会为每行分配唯一的编号，但其顺序可能不可预测。
- **性能影响**：对于大数据量表，使用复杂的 `PARTITION BY` 和 `ORDER BY` 可能会影响查询性能，建议合理使用索引优化。
- **替代函数**：在某些情况下，如需允许重复编号，可以考虑使用 RANK() 或 DENSE_RANK() 等其他窗口函数。

---



			
		

	



              




          




        



    





    



      

      

        
      

      





	

		
评论 2

	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值