在现代数据驱动的应用程序中，对数据进行高效的查询和排序是至关重要的。SQL语言提供了多种强大的工具来实现这一目标，其中ROW_NUMBER()函数就是一个非常有用的工具。它可以在分组的基础上对数据进行排序，并为每一行分配一个唯一的行号。这在处理复杂的数据集时尤其有用，例如在分页显示数据、排名统计、或者需要在分组内进行排序的情况下。本文将详细介绍如何在数据库中使用ROW_NUMBER()函数进行分组排序，并通过具体的示例来演示其使用方法。
一、前言
row_number() over (partition by order by) 是一种SQL窗口函数，在Oracle、Hive 以及mysql8.0以上版本可以使用，用于在每个分区内对每一行进行排序并编号，从1开始编号，赋予其连续的编号。它一般用于分析和报表等场景，可以帮助我们对数据进行分区后排序，获取排名信息。
row_number() 函数搭配partition by与order by函数可以完成以下功能。
对查询结果集中的每一行分配一个唯一的数字，从1开始编号。
结合partition by可以先对结果进行分组，然后组内每条数据再从1开始编号。
应用场景：比如学校考试结束后，按科目进行分组，每个科目按成绩进行排序，获取前十名。
总结：row_numer是一个分组排序函数，可以对查询结果集先进行分组，然后每组内再进行排序。对每个分组内的每行分配一个从1开始的连续唯一编号。
二、ROW_NUMBER()语法
注意：以下两种写法都是分组排序函数，语法达到的效果是一致的。
ROW_NUMBER() OVER (partition by order by)在Oracle、Hive 以及mysql8.0以上版本可以使用。
ROW_NUMBER() OVER (distribute by sort by)在
mysql8.0中暂时不支持，hive支持。
第一种写法：row_number() over (partition by 分组列 order by 排序列 asc/desc) as 别名
第二种写法：row_number() over(distribute by 分组列 sort by 排序列 asc/desc) as 别名
简单来说就是函数执行时首先会根据partition by的列来进行分组，分完组后在每个分组内再根据order by 的列来进行排序。
功能：
ROW_NUMBER() 函数
为每个分组内的每一行分配一个唯一的连续编号。分组是通过 PARTITION BY 子句实现的，它指定了分组的依据。
排序是通过 ORDER BY 子句实现的，它指定了行号分配的顺序。
注意：
over()中可以只有partition by，也可以只有order by。
partition by后面跟的分组列可以有多个，order by后面跟的排序列也可以有多个。
问题
Q：row_number函数为每个分组内的每一行分配一个唯一的连续编。即分组内的每行数据编号只会从1开始分配并且不重复。假如我们是对考试分数进行排序，希望分数相同的人排名一样该怎么办呢？
A：这时候就要使用到RANK()函数或者DENSE_RANK()函数了。这两个函数会对相同的值分配相同的排名。
三、用法示例
以下示例基于mysql8.0进行执行
准备数据
createtabletest(idvarchar(10)NOTNULL,`name`varchar(10)NULL,agevarchar(10)NULL,salaryintNULL);--数据是每个人不同年龄段的薪资数据insertintotest(id,`name`,age,salary)values(101,\'张三\',24,15000);insertintotest(id,`name`,age,salary)values(101,\'张三\',22,8000);insertintotest(id,`name`,age,salary)values(101,\'张三\',20,6500);insertintotest(id,`name`,age,salary)values(102,\'李四\',23,18000);insertintotest(id,`name`,age,salary)values(102,\'李四\',22,8500);insertintotest(id,`name`,age,salary)values(102,\'李四\',21,7500);insertintotest(id,`name`,age,salary)values(103,\'王五\',24,25000);insertintotest(id,`name`,age,salary)values(103,\'王五\',22,18000);insertintotest(id,`name`,age,salary)values(103,\'王五\',20,12000);select*fromtest;
表数据：
3.1、对查询结果进行倒序排序（无分组）
SELECTid,`name`,age,salary,ROW_NUMBER()OVER(ORDERBYsalaryDESC)rnFROMtest;
注：如果不指定分组那么会对全局进行排序，将所有数据视为一组； 然后每组内对每一行从1开始进行连续编号。如上图rn从1开始编号到9。
3.2、对查询结果分组后排序
SELECTid,`name`,age,salary,ROW_NUMBER()OVER(PARTITIONBYnameORDERBYsalaryDESC)RNFROMtest;
注：先执行PARTITION BY按name分组，然后ORDER BY在分组内按照salary排序。
如上图：RN会对每组内的每行数据分配一个唯一的连续编号。
3.3、查询每个id最高的薪资
也就是分组后按薪资排序，并找出每个分组内薪资最高（排序为1）的记录
SELECT*FROM(SELECTid,`name`,age,salary,ROW_NUMBER()OVER(PARTITIONBYidORDERBYsalaryDESC)RNFROMtest)aWHEREa.RN=1;
查到每个id的最高薪资，即每个id分组内排名为1的
举一反三：我们也可以通过上述这个示例，比如我们可以实现比赛中获取每个分组内的前10名。即先进行分组，然后对分数进行排序后获取RN<=10的。
3.4、找出年龄在20岁到22岁数据，并按薪资排序
SELECTid,name,age,salary,row_number()over(ORDERBYsalaryDESC)RNFROMtestWHEREageBETWEEN20AND22;
注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、
order by 的执行。
partition by 用于给结果集分组，如果没有指定分组列那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。
3.5、根据多个字段分组、根据多个字段排序
select*,ROW_Number()OVER(PARTITIONBYid,nameORDERBYageDESC,salaryasc)ASRNfromtest
partition by可以根据多个字段进行分组、order by也可以根据多个字段排序。
四、扩展延伸
4.1、使用ROW_NUMBER()函数进行数据去重
假如我们在对表执行insert的时候，不小心多执行了几次，如何利用row_number对数据进行去重呢？
数据准备
createtabletest(idvarchar(10)NOTNULL,`name`varchar(10)NULL,agevarchar(10)NULL,salaryintNULL);insertintotest(id,`name`,age,salary)values(101,\'张三\',22,8000);insertintotest(id,`name`,age,salary)values(101,\'张三\',22,8000);insertintotest(id,`name`,age,salary)values(101,\'张三\',22,8000);insertintotest(id,`name`,age,salary)values(102,\'李四\',23,12000);insertintotest(id,`name`,age,salary)values(102,\'李四\',23,12000);insertintotest(id,`name`,age,salary)values(102,\'李四\',23,12000);insertintotest(id,`name`,age,salary)values(103,\'王五\',24,25000);insertintotest(id,`name`,age,salary)values(103,\'王五\',24,25000);insertintotest(id,`name`,age,salary)values(103,\'王五\',24,25000);
重复数据如下：
如上图，每条数据都重复插入了3次，那么该如何去重呢？
--创建一个新的表test_new,然后对test表中的数据根据id进行分组，取每组中的第一条数据即可实现去重效果。insertintotest_newselectid,`name`,age,salaryfrom(select*,ROW_Number()OVER(PARTITIONBYidORDERBYageDESC)ASRNfromtest)MwhereM.RN=1;
五、row_number()、rank() 和 dense_rank() 的区别
ROW_NUMBER()：为每一行分配唯一的行号，适合唯一标识需求。
RANK()：为重复值分配相同的排名，并在后续排名中跳过名次，适合需要处理排名的场景。
DENSE_RANK()：为重复值分配相同的排名，但不跳过名次，适合希望连续排名的场景。
下面表格总结了这三个函数的主要区别：
| 函数 | 特点 | 排名示例 |
|---|---|---|
| ROW_NUMBER | 为每行分配唯一的数字 | 1, 2, 3, 4, … |
| RANK | 相同的值共享相同的排名，排名会跳过数字 | 1, 1, 3, 4, … |
| DENSE_RANK | 相同的值共享相同的排名，不跳过数字 | 1, 1, 2, 3, … |
总结
通过本文的介绍，您应该已经掌握了如何在数据库中使用ROW_NUMBER()函数进行分组排序。我们详细讲解了ROW_NUMBER()函数的基本语法、应用场景以及具体的使用方法，并通过多个示例演示了其强大的功能。ROW_NUMBER()函数不仅可以帮助您高效地对数据进行排序和分组，还能在处理复杂数据集时提供极大的便利。希望本文的内容能够对您有所帮助，使您在日常工作中能够更加熟练地运用SQL语言来解决各种数据问题。如果您在使用过程中遇到任何问题，欢迎随时查阅本文的教程，或者寻求专业人士的帮助。祝您在数据处理的世界里游刃有余!