1. 从“分组排序”这个高频需求说起如果你写过SQL尤其是处理过报表、排行榜或者需要基于分组进行复杂排序的场景那么对“分组排序”这个词一定不陌生。简单来说它就是在每个分组内部对数据进行排序并赋予一个序号。比如你想找出每个部门里工资最高的前三名员工或者找出每个班级里成绩排名第一的学生。在MySQL 8.0之前要实现这个需求通常需要借助用户变量User-Defined Variables或者写一些相当绕的子查询代码不仅冗长而且执行效率往往不高可读性也差。我记得在早期版本里为了给每个部门的员工按工资排名我得写一个自连接Self-Join或者用变量去累加计数稍不留神逻辑就写错了调试起来非常痛苦。直到MySQL 8.0引入了窗口函数Window Functions这一切才变得优雅而高效。窗口函数允许你在与当前行相关的一组行这个“窗口”上进行计算而无需对结果集进行分组聚合从而改变了行与行之间的关系。在众多窗口函数中ROW_NUMBER()、RANK()和DENSE_RANK()这三个函数可以说是解决“分组排序”问题的“三剑客”。它们功能相似都用于生成序号但在处理“并列”情况时的规则截然不同这也正是它们容易被混淆和用错的地方。今天我们就来彻底拆解这三个函数通过大量的实例让你不仅知道怎么用更明白在什么场景下该用哪一个。2. 核心概念窗口函数与OVER()子句在深入这三个排序函数之前我们必须先理解它们赖以生存的土壤——窗口函数和OVER()子句。窗口函数的核心思想是“基于窗口进行计算”。这里的“窗口”是由OVER()子句定义的一个数据行的集合。它可以是整个结果集也可以是按某个字段分区PARTITION BY后的子集。OVER()子句是窗口函数的灵魂它主要包含两个部分PARTITION BY 用于将数据划分成不同的分区也就是我们常说的“分组”。窗口函数会独立地在每个分区内进行计算。如果省略PARTITION BY则整个结果集被视为一个分区。ORDER BY 用于指定分区内数据的排序方式。这对于ROW_NUMBER()、RANK()、DENSE_RANK()这类排序函数来说是必须的因为它决定了序号分配的规则。一个典型的窗口函数语法如下窗口函数 OVER ( [PARTITION BY 列清单] ORDER BY 排序列清单 [ASC|DESC] )理解了这个框架我们再来看具体的排序函数就会清晰很多。它们都是在OVER()定义的窗口内根据ORDER BY指定的顺序来生成序号。3. 排序函数三兄弟ROW_NUMBER, RANK, DENSE_RANK 的深度对比这三个函数都用于生成序号但处理“值相同”即并列的情况时策略完全不同。这是理解它们区别的关键。我们通过一个具体的例子来直观感受。假设我们有一张学生成绩表scoresstudent_idsubjectscore1数学952数学923数学924数学885数学886数学85现在我们分别用三个函数对数学科目的成绩进行降序排名SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) as row_num, RANK() OVER (ORDER BY score DESC) as rank_num, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank_num FROM scores WHERE subject 数学;查询结果会是student_idscorerow_numrank_numdense_rank_num195111292222392322488443588543685664从这个结果我们可以清晰地总结出三者的核心区别3.1 ROW_NUMBER()连续且唯一的序号ROW_NUMBER()是最“简单粗暴”的一个。它的规则是在分区内严格按照ORDER BY的顺序为每一行分配一个唯一的、连续的整数序号从1开始。即使两行的排序字段值完全相同它也会赋予不同的序号通常是按照其在结果集中出现的顺序。特点 序号连续、唯一无并列。上例表现 对于两个92分它给了2和3对于两个88分它给了4和5。序号是连续的1,2,3,4,5,6。典型场景 当你需要绝对唯一的标识或者进行分页查询时例如WHERE row_num BETWEEN 11 AND 20来获取第2页数据。注意由于它不处理并列所以不适合用于严格的“排名”场景如成绩排名并列应该共享名次。3.2 RANK()跳跃排名RANK()的规则是在分区内根据ORDER BY排序为每一行分配一个排名。如果值相同则共享相同的排名并且下一个排名会“跳跃”到正确的位置。特点 允许并列并列后的下一个排名会出现“跳跃”即序号不连续。上例表现 两个92分并列第2名所以下一个88分就是第4名跳过了第3名。两个88分并列第4名所以下一个85分就是第6名跳过了第5名。序号是1, 2, 2, 4, 4, 6。典型场景 标准的竞赛排名、成绩排名。比如奥运会颁奖金牌并列就没有银牌下一个直接是铜牌。这种排名方式符合大多数人对“排名”的直觉。3.3 DENSE_RANK()密集排名DENSE_RANK()的规则是在分区内根据ORDER BY排序为每一行分配一个排名。如果值相同则共享相同的排名但下一个排名是连续的不会跳跃。特点 允许并列且排名序号是连续的、密集的。上例表现 两个92分并列第2名下一个88分就是第3名没有跳过。两个88分并列第3名下一个85分就是第4名。序号是1, 2, 2, 3, 3, 4。典型场景 当你需要排名但又希望排名序号是连续的没有间隔时。例如在一些分级评定中如“A级”、“B级”你可能希望等级是连续的。为了让你一目了然我将它们的区别总结成下表特性ROW_NUMBER()RANK()DENSE_RANK()并列处理不处理强制分配唯一序号并列共享排名下一名次跳跃并列共享排名下一名次连续序号连续性连续不连续有跳跃连续序号唯一性唯一并列行不唯一并列行不唯一典型用途分页、生成唯一行标识竞赛排名、成绩排名带跳跃等级评定、连续排名4. 实战演练结合PARTITION BY的分组排序理解了基本区别后我们来看它们最强大的应用场景结合PARTITION BY进行分组排序。这才是解决文章开头提到的“每个部门前三名”、“每个班级第一名”等问题的利器。假设我们有一张员工表employeesemp_iddept_idsalary101‘D01’8000102‘D01’7500103‘D01’7500104‘D01’6000201‘D02’9000202‘D02’8500203‘D02’8200204‘D02’8200需求1找出每个部门工资最高的员工允许并列。如果直接用MAX()聚合函数我们只能得到最高工资的值要拿到对应的员工信息还得用子查询关联。用RANK()窗口函数则优雅得多SELECT * FROM ( SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_rank FROM employees ) AS ranked_employees WHERE salary_rank 1;在这个子查询中PARTITION BY dept_id将数据按部门分区。RANK()函数会分别在D01和D02两个分区内独立计算排名。ORDER BY salary DESC在每个分区内按工资降序排列。外层查询筛选出排名为1的行。结果会包含D01部门emp_id 101 (工资8000排名1)D02部门emp_id 201 (工资9000排名1)需求2找出每个部门工资排名前三的员工如果并列都算入。这个需求用DENSE_RANK()或RANK()都可以但含义略有不同。用DENSE_RANK()会选出“排名值3”的员工用RANK()可能会因为跳跃而选出更多或更少的员工。通常我们想要的是“前三名”这个概念即排名值3使用DENSE_RANK()更符合直觉因为它保证了排名值是连续的1,2,3。SELECT * FROM ( SELECT emp_id, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as salary_dense_rank FROM employees ) AS ranked_employees WHERE salary_dense_rank 3;结果D01部门101(1), 102(2), 103(2), 104(3) - 注意因为102和103并列第二所以104是第三名所有四名员工都被选出。D02部门201(1), 202(2), 203(3), 204(3) - 四名员工也都被选出。需求3给每个部门的员工生成一个唯一的、连续的工号假设按工资降序排。这里需要的是唯一标识所以必须用ROW_NUMBER()。SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_seq FROM employees;结果中每个部门内的dept_seq都是从1开始的连续唯一数字。实操心得在写这类分组排序查询时我习惯先写出内层的窗口函数查询把分区、排序和排名结果都查出来在开发工具里先看一遍中间结果。确认排名逻辑符合预期后再套上外层查询进行过滤。这样可以避免因为对函数行为理解不透彻而直接过滤出错。另外PARTITION BY和ORDER BY后面可以跟多个字段实现更复杂的排序逻辑比如ORDER BY score DESC, student_id ASC可以在分数相同的情况下再按学号升序排这会影响ROW_NUMBER()的结果。5. 进阶应用与性能考量掌握了基础用法我们来看看一些更进阶的场景和需要注意的性能问题。5.1 在UPDATE或DELETE语句中使用窗口函数有时我们可能需要根据排名来更新或删除数据。在MySQL 8.0中你不能直接在UPDATE或DELETE的WHERE子句里引用窗口函数。标准的做法是使用公共表表达式CTE, Common Table Expression。例如删除每个部门中工资排名最后的一名员工如果并列随机删除一个WITH RankedEmployees AS ( SELECT emp_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary ASC) as asc_rank FROM employees ) DELETE e FROM employees e JOIN RankedEmployees re ON e.emp_id re.emp_id WHERE re.asc_rank 1;这里我们在CTE里用ROW_NUMBER()按工资升序ORDER BY salary ASC排名最后一名就是排名第一。然后通过JOIN将这个排名信息关联回原表进行删除。注意这里用了ROW_NUMBER()来确保每个部门只有一行被标记为asc_rank 1即使有并列最后一名也只会删除其中一个。5.2 窗口函数与索引优化窗口函数的性能很大程度上依赖于其OVER()子句中PARTITION BY和ORDER BY所涉及的字段。如果能为这些字段建立合适的复合索引可以极大提升查询效率。对于查询RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)理想的索引是(dept_id, salary DESC)。这个索引可以快速完成按dept_id的分区PARTITION BY。在每个分区内数据已经按照salary DESC的顺序排列好了无需额外的排序操作filesort这对于ORDER BY是关键优化。你可以使用EXPLAIN命令查看查询计划如果出现了Using filesort就说明可能需要为ORDER BY的列添加索引。踩坑提醒窗口函数虽然强大但在大数据集上可能会成为性能瓶颈尤其是在没有合适索引的情况下。我曾经在一个百万级数据的表上执行一个复杂的多层窗口函数嵌套查询由于缺少对分区和排序键的索引查询跑了近一分钟。加上复合索引后时间缩短到几秒钟。所以对于使用窗口函数的查询务必检查PARTITION BY和ORDER BY的字段并考虑为其创建索引。另外要小心数据倾斜如果某个分区比如某个部门的数据量特别大即使有索引计算该分区的排名也可能较慢。5.3 组合使用其他窗口函数ROW_NUMBER(),RANK(),DENSE_RANK()常常与其他窗口函数一起使用解决更复杂的问题。场景计算每个部门员工的工资排名以及该员工工资与部门最高/最低工资的差距。SELECT emp_id, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank, salary - FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) as diff_from_top, salary - LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as diff_from_bottom FROM employees;这里我们同时使用了RANK()、FIRST_VALUE()和LAST_VALUE()窗口函数。FIRST_VALUE()获取窗口内第一行的值部门最高工资LAST_VALUE()默认的窗口范围是“当前行到分区结束”为了获取整个分区的最后一行部门最低工资我们需要用ROWS BETWEEN ...子句显式指定窗口范围为整个分区。6. 常见误区与排错指南即使理解了原理在实际使用中还是容易踩一些坑。下面是我总结的几个常见问题和排查思路。6.1 错误在WHERE子句中直接使用窗口函数列名这是最常见的语法错误。窗口函数是在SELECT阶段进行计算的而WHERE子句的执行顺序在SELECT之前。因此你不能在WHERE子句中直接引用窗口函数生成的别名。错误写法SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees WHERE rnk 3; -- 这里会报错Unknown column rnk in where clause正确写法使用子查询或CTE-- 使用子查询 SELECT * FROM ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) AS t WHERE t.rnk 3; -- 使用CTE (推荐更清晰) WITH RankedEmployees AS ( SELECT emp_id, salary, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) SELECT * FROM RankedEmployees WHERE rnk 3;6.2 混淆ORDER BY 对排名的影响与NULL值处理ORDER BY子句的排序方式ASC/DESC直接决定了排名的顺序。同时需要特别注意NULL值的处理。在排序中默认情况下NULL值会被视为最小值在ASC排序中排在最前面在DESC排序中排在最后面。这会影响排名结果。例如如果score列有NULL值RANK() OVER (ORDER BY score DESC)会让NULL值的行排在最后并共享最后一个排名。如果你希望忽略NULL值可能需要在窗口函数内部使用CASE WHEN或在外层进行过滤。6.3 性能在大数据集上使用无索引的ORDER BY如前所述如果OVER()子句中的ORDER BY字段没有索引并且数据量很大MySQL 就需要进行全表扫描和内存排序filesort性能会急剧下降。使用EXPLAIN查看执行计划时如果看到Using filesort就是一个强烈的警告信号。排查步骤使用EXPLAIN FORMATJSON或EXPLAIN ANALYZEMySQL 8.0.18获取详细的执行计划。观察filesort是否出现以及rows字段预估的行数。考虑为PARTITION BY和ORDER BY涉及的列创建复合索引。索引的顺序应与PARTITION BY列在前ORDER BY列在后排序方向一致。如果数据量极大即使有索引窗口函数计算也可能消耗大量内存。可以尝试调整sort_buffer_size等系统变量但更根本的是审视查询是否必要或考虑分批次处理数据。6.4 逻辑错误错误选择排序函数导致结果偏差这是业务逻辑层面的错误。回顾一下那个经典问题“取每个分组的前N条记录”。如果你想严格取前N行不管是否并列用ROW_NUMBER() N。如果你想取前N个名次允许并列用DENSE_RANK() N。用RANK() N可能会因为排名跳跃而导致实际取出的行数少于预期比如你想要前3名但第2名有3个人并列用RANK()取出来的排名可能是1,2,2,2,5,...这样排名为5的行不会被取出你可能只拿到了4行数据而不是“前三名”的所有人。我个人的经验是在涉及“排名”的业务需求沟通时一定要和产品经理或业务方确认清楚他们对“并列”情况的处理期望是“共享名次但后续名次跳跃”奥运模式RANK()还是“共享名次且后续名次连续”等级模式DENSE_RANK()又或者是“不允许并列必须分出先后”唯一序号ROW_NUMBER()。确认清楚这一点能避免很多返工和逻辑错误。窗口函数尤其是这三个排序函数是SQL中提升开发效率和查询能力的利器。从最初的绕路子查询到如今一行函数清晰表达其价值在于将复杂的逻辑封装成简洁的语义。真正掌握它们的关键不在于死记语法而在于理解数据分区的概念、排序的规则以及面对具体业务问题时能清晰地判断出该用哪把“钥匙”去开哪把“锁”。多写、多试、多结合EXPLAIN分析你就能越来越得心应手。