你是不是也遇到过这种情况需求是“取每个部门薪资最高的员工”你上来就写了一刀GROUP BY department结果 SELECT 列表里除了部门名和聚合函数别的字段全报错或者数据能跑通但拿到的根本不是你要的那一行。这时候有经验的同事会告诉你用ROW_NUMBER() OVER(PARTITION BY ...)。很多初学者在这一步直接卡住因为这两个东西看起来都跟“分组”有关但行为完全不同。这篇文章专门把ROW_NUMBER() OVER(PARTITION BY ...)和GROUP BY的本质区别讲透。核心关键词就两个窗口函数和分组聚合。它们是 SQL 里两条完全不同的工作路线搞清楚之后去重、分组取 TopN、保留明细、配合聚合统计这些场景你都能一眼判断该用哪个。本文适合刚接触 SQL 窗口函数的人也适合准备 SQL 面试、或者工作中经常写统计报表但总被这两个语法绕晕的开发者和数据分析师。1. 一次搞懂两者的核心定位窗口函数与分组聚合是两条路线先说结论ROW_NUMBER() OVER(PARTITION BY ...)是一个窗口函数而GROUP BY是一个分组聚合语句。它们都能把数据“按某个字段归类”但归类之后干的事完全不一样。1.1ROW_NUMBER()到底做了什么ROW_NUMBER()的意思是“行号”。OVER(PARTITION BY 字段)指定了“在哪个范围内编号”这个范围就叫窗口。它的工作方式是一行一行地扫描数据给每一行按照窗口内定义的顺序编一个递增序号但数据行的总数不变每一行都原样保留。你可以把它理解成给全班同学按身高排座位号——每个人都会有一个号码但没有任何人被从名单里删掉。PARTITION BY相当于“按班级分组”ORDER BY相当于“按身高排序”排完之后每个班内部从 1 开始编号。这个编号结果是附加在每一行旁边的额外列不是把多行合并成一行。全程行不丢失这是和GROUP BY最根本的差异。1.2GROUP BY做了什么GROUP BY的工作方式完全相反把多行按照指定字段折叠成一组一组只保留一行。聚合函数比如COUNT()、SUM()、AVG()、MAX()、MIN()就是在这个折叠过程中对组内多行做计算。还是拿学生举例GROUP BY 班级就相当于“每个班只留下一张汇总卡片”卡片上可以写“这个班有 45 人”“平均身高 168cm”“最高的人 185cm”但卡片上没有“张三、李四分别多高”这种明细信息。所有不是分组字段、也不是聚合函数包住的字段都没资格出现在最终结果里这在 MySQL 里直接报错在 SQL Server、Oracle 里也明确不合法。1.3 一张对照表看清差异对比维度ROW_NUMBER() OVER(PARTITION BY ...)GROUP BY所属类别窗口函数分析函数分组聚合语句是否改变行数不改变每一行都保留会折叠一组只留一行作用方式逐行编号编号附加在原行旁边多行合成一行丢失明细能否单独输出非分组字段可以原字段都在不行非分组字段必须套聚合函数是否必须配合聚合函数不需要通常配合 COUNT/SUM/AVG/MAX/MIN 使用典型用途行号、去重、分组 TopN、相邻行比较统计汇总、报表聚合是否影响原始数据顺序不改变物理顺序只在窗口内逻辑排序结果自带分组排序特征这张表是全文的地图。后面所有场景、代码和踩坑都是围绕这张表的两行展开的。2. 使用场景拆解什么时候该用ROW_NUMBER什么时候该用GROUP BY“能不能用”和“该不该用”是两回事。有些场景两种写法都能出结果但结果的语义不同、性能也不同。2.1 去重场景ROW_NUMBER是首选GROUP BY也能做但有缺点最常见的误用发生在数据去重。假设订单表里同一个 order_id 因为数据同步问题出现了多行你想保留每个订单的一条记录。用GROUP BY去重SELECT order_id, MAX(create_time) AS create_time FROM orders GROUP BY order_id;这样能去重但问题很明显想保留的其他字段必须一个个用聚合函数包起来比如MAX(create_time)只是“碰巧”取到了最后一条的时间如果还想保留金额、用户ID就得写成MAX(amount)、MAX(user_id)如果这些字段逻辑上应该来自同一行那MAX组合出的结果可能根本不是同一行的数据。用ROW_NUMBER()去重才是正确姿势SELECT order_id, user_id, amount, create_time FROM ( SELECT orders.*, ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;这段 SQL 的逻辑是先把所有行按 order_id 分区在分区内按创建时间倒序编号时间最新的那条编号为 1然后外层只取 rn1 的行。这样拿到的完完整整就是最新那一条记录的所有字段不会出现字段来自不同行的问题。我实际开发里遇到一个真实案例一个日新增用户表因为上游重复推送同一个用户一天出现了三条记录。用GROUP BY user_id去重后发现给用户发的券金额来自第一条而券类型来自第三条完全错乱。后来改成ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY update_time DESC)之后才拿到真正的“最新一条”。热词里提到的“sql语句去重”“sql语句去重查询”十有八九指的都是这个场景。记住一个口诀要去重且要保留完整明细字段用 ROW_NUMBER要去重且只需要做聚合计算用 GROUP BY。2.2 分组取 TopNROW_NUMBER的绝对主场“每个部门工资最高的前 3 名”“每个商品品类销量最高的 5 个店铺”“每天登录时长最长的用户”这类需求用GROUP BY只能做到一件事算出每个组里最高值是多少但拿不到“哪一行是这个最高值”。GROUP BY的极限是SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department;结果只有部门和最高工资数。谁是那个挣最多的人名字、工号、入职时间统统拿不到。ROW_NUMBER()可以一次拿到完整行SELECT department, emp_name, salary, rn FROM ( SELECT employees.*, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 3;注意这里用了rn 3就是取前三名。窗口公式只有 1 和 3 的区别。倒序ORDER BY salary DESC控制“谁排在前面”。有个细节需要注意并列排名的问题。如果两个人的工资完全一样ROW_NUMBER()会随机分配 1 和 2这会造成不公平。如果需要并列名次应该换成RANK()或DENSE_RANK()。这是另一个经典的 SQL 面试考点和本主题连在一起考。2.3 纯聚合统计GROUP BY的领域无人能替代如果你想算每个品类的总销售额、总订单数、平均单价这就是GROUP BY的看家本领SELECT category, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM orders GROUP BY category;这种写法任何窗口函数都替代不了。窗口函数不会折叠行它给 100 行数据编号后还是 100 行无法产出 30 个品类的统计行。想用OVER(PARTITION BY)模拟聚合可以算出每个分区里 amount 的总和并附加在每一行上但结果还是 100 行报表层面没法直接用。所以两者的关系是互补而不是替代一个要折叠出汇总一个要留着明细编号。3. 实操过程完整 SQL 示例与执行逻辑推演光讲概念不够拿一套完整的测试数据和 SQL 走一遍把每一步的中间结果展示出来。3.1 准备测试数据用 MySQL 8.0 举例建表和插入语句如下。我用的是 Navicat 直接执行 SQL 脚本你也可以用命令行source执行或者复制到任意 SQL 客户端里跑。CREATE TABLE employees ( id INT PRIMARY KEY, emp_name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10,2) ); INSERT INTO employees VALUES (1, 张三, 技术部, 15000), (2, 李四, 技术部, 12000), (3, 王五, 技术部, 18000), (4, 赵六, 市场部, 11000), (5, 钱七, 市场部, 14000), (6, 孙八, 市场部, 14000), (7, 周九, 人事部, 9500), (8, 吴十, 人事部, 10000), (9, 郑一, 人事部, 8000);3.2 核心 SQL 演示三个场景现场跑通场景一查询每个部门工资最高的员工完整信息。SELECT department, emp_name, salary FROM ( SELECT employees.*, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 1;执行过程拆解先扫描全表 9 行PARTITION BY department把数据切成了三个窗口分区技术部 3 行、市场部 3 行、人事部 3 行每个分区内部按工资降序编号。技术部王五 18000 编号 1市场部钱七、孙八都是 14000但ROW_NUMBER()必须给出唯一序号所以两人分到 1 和 2人事部吴十 10000 编号 1。外层过滤 rn1返回 3 行。场景二对比一下用GROUP BY写同样的需求。SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department;返回 3 行但每行只有部门和最高薪资数字没有员工姓名。这就是前面说的“知道最高工资是多少但不知道是谁”。场景三全局编号和分组编号的区别。SELECT department, emp_name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS global_rn, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rn FROM employees;结果里会出现两列编号global_rn是全表按工资降序从 1 到 9dept_rn是部门内部编号。这个例子特别能说明窗口是独立计算的每一行同时挂在两个窗口里互不干扰两套编号可以共存。3.3 从 SQL 执行顺序看两者差异理解执行顺序是理解 SQL 语义的钥匙。一个完整查询的逻辑执行顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → 窗口函数在 SELECT 阶段计算关键点GROUP BY发生在聚合阶段它先折叠行而窗口函数在SELECT阶段才计算此时行已经定型或者是聚合后的分组行或者是 FROM/WHERE 过滤后的明细行。这就是为什么WHERE子句里不能用ROW_NUMBER()因为计算窗口函数时WHERE已经执行完了。同理GROUP BY的结果可以再配合窗口函数做“聚合后的排名”常见的写法是SELECT department, total_salary, RANK() OVER(ORDER BY total_salary DESC) AS dept_rank FROM ( SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ) t;这里子查询先完成GROUP BY聚合外层再对聚合结果做窗口排名。两个工具协同工作先折叠后编号是先有鸡还是先有蛋的答案先有GROUP BY折叠再有窗口函数编号。3.4 关于GROUP BY多个字段热词里有“group by 多个字段”。这个操作在语义上就是“按多个维度联合分组”。比如按部门和入职年份统计人数SELECT department, YEAR(hire_date) AS hire_year, COUNT(*) AS cnt FROM employees GROUP BY department, YEAR(hire_date);联合分组的逻辑是把(department, hire_year)作为一个组合键同组才折叠。这在PARTITION BY里同样适用PARTITION BY department, YEAR(hire_date)就是以同样组合键划分窗口。两者在“多字段分组”上的语法是对称的理解一个就能理解另一个。4. 常见问题与排查技巧实录写 SQL 写得越多踩的坑越有代表性。这里把最常遇到的几类问题整理成速查表。4.1ROW_NUMBER()排序不稳定导致编号结果漂移这是一个非常隐蔽的问题。当ORDER BY后面的字段有重复值时比如两个人的工资都是 14000ROW_NUMBER()给谁分 1 谁分 2 是没有保证的数据库每次执行可能给出不同结果因为它觉得“两个值相等顺序无所谓”。更麻烦的是如果分区列本身有 NULL 值不同数据库对 NULL 的排序位置不统一。MySQL 默认 NULL 最小Oracle 默认 NULL 最大跨库移植时结果可能不一样。排查技巧在ORDER BY里加一个唯一字段作为决胜条件比如ORDER BY salary DESC, id ASC。这样每个窗口内的排序是完全确定的编号也会稳定下来。4.2 误把PARTITION BY当GROUP BY用有人写SELECT department, emp_name, ROW_NUMBER() OVER(PARTITION BY department) AS rn FROM employees;这个语法本身合法但理解上容易犯一个错误以为结果会被折叠成每个部门一行。实际上PARTITION BY department不会折叠它只是把同部门的人都归到同一个窗口里各自编号。最终结果依然返回 9 行。要判断一个写法是不是在折叠行最直接的办法是数结果行数GROUP BY后行数明显变少窗口函数后行数不变。这是最快的自检方式。4.3 性能问题为什么加了窗口函数后查询变慢了窗口函数通常需要在内存或临时表里对每个分区排序数据量大时性能开销很高。热词里有“慢sql优化”“并行sql优化”窗口函数往往是重点嫌疑对象。优化思路有三条第一尽量缩小参与计算的数据集在子查询里先用WHERE过滤掉不需要的分区和行不要对全表做窗口计算。第二为PARTITION BY和ORDER BY涉及的字段建立合适的联合索引。窗口计算需要按分区字段分组、按排序字段排序联合索引(department, salary DESC)能减少排序成本。第三如果确实要取分组 TopN有些数据库如 MySQL 8.0 以上可以用 LATERAL JOIN 或相关子查询的方式改写有时比窗口函数更快但可读性会差一些需要根据执行计划选择。4.4 一个典型面试连环坑面试官经常这样出题表里有用户 ID、登录日期让你找出每个用户最近一次登录的日期并且要带上那天的登录时长。有人第一反应写SELECT user_id, MAX(login_date) AS last_date FROM user_login GROUP BY user_id;这只拿到了日期丢掉了登录时长。如果要时长要么用子查询回表关联SELECT a.* FROM user_login a INNER JOIN ( SELECT user_id, MAX(login_date) AS last_date FROM user_login GROUP BY user_id ) b ON a.user_id b.user_id AND a.login_date b.last_date;这种写法逻辑上可行但要注意如果同一个用户同一天有多条登录记录连接会产生重复行。用窗口函数则完全没有这个问题SELECT user_id, login_date, duration FROM ( SELECT user_login.*, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date DESC) AS rn FROM user_login ) t WHERE rn 1;不用自连接不用考虑重复行问题所有明细字段天然就在同一行上。这也是为什么窗口函数在面试题里越来越常见的原因。4.5 在 SQL Server、Oracle 里的兼容性差异标题和热词里都出现了 SQL Server。SQL Server、Oracle、PostgreSQL、MySQL 8.0 都支持标准窗口函数语法ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)语法基本一致。几个细微差别值得注意MySQL 8.0 以下版本不支持窗口函数只能用变量模拟或者改写这是老项目升级时经常遇到的坑。SQL Server 中ORDER BY在窗口函数内是必须写的不能省略否则报错。而 MySQL 8.0 允许省略表示不排序随机编号。Oracle 里窗口函数的别名不能在外层 WHERE 直接引用需要嵌套子查询。这和 MySQL 的行为一致是通用的标准限制。5. 从执行计划层面追溯到根因为什么两者看起来像“都能分组”前面说了这么多其实还有一个更深层的问题没有回答为什么初学者会把这两个东西搞混因为从 SQL 的字面上看PARTITION BY department和GROUP BY department好像都是一回事。要彻底解开这个心结得从数据库执行引擎的视角看一眼。GROUP BY在物理执行上是一个HashAggregate 或 SortAggregate 算子。这个算子会扫描输入行按照分组键计算哈希值把相同哈希值的行丢进同一个桶里然后对每个桶执行聚合函数输出一行。一旦进桶原始行的物理身份就消失了输出行里只能有分组键和聚合结果。ROW_NUMBER() OVER(PARTITION BY ...)在物理执行上是一个WindowAgg 算子。这个算子按分区键把输入行组织成多个窗口然后在窗口内执行排序并计算序号每一行在算子内部依然保持独立的行身份编号只是附加的列。从算子的角度说一个是“多进一出”另一个是“多进多出但加标签”。这就是为什么GROUP BY之后你没法再引用明细字段而ROW_NUMBER()之后明细字段都还在。另外一个很容易被忽略的点GROUP BY的结果可以直接给HAVING用因为HAVING就是专门在分组聚合后做过滤的而窗口函数计算发生在SELECT阶段所以不能在同一层用WHERE过滤 rn1必须嵌套一层子查询。这个限制也常常导致新手写出来的 SQL 报错SELECT department, emp_name, salary, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn FROM employees WHERE rn 1;这段会直接报错因为WHERE执行时 rn 还不存在。正确做法是把带别名的查询包一层再过滤。理解了这个执行顺序问题你就能解释为什么窗口函数永远是“先查出来再过滤编号”。6. 面试与实战中的几种经典考法还有几个容易翻车的细节窗口函数在 SQL 面试题里的出现频率非常高。热词里就有“sql面试题”这里把围绕ROW_NUMBER()和GROUP BY的几种考法一次性梳理清楚。6.1 连续登录/连续签到问题题目通常是找出连续登录 3 天以上的用户。这题的经典解法是用窗口函数给每个用户的登录日期编号然后用日期减去编号差相同的日期落在同一组里。SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn FROM user_login ) t GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY) HAVING COUNT(*) 3;这里就出现了ROW_NUMBER()和GROUP BY的经典配合先用窗口函数编号再用GROUP BY折叠出连续区间最后用HAVING过滤。两者不是互斥关系是流水线的关系。6.2 分组内相邻记录比较比如要算每个用户当前登录时间和上一次登录时间的间隔。GROUP BY在这里完全无能为力因为显然需要保留每一行。正确解法是SELECT user_id, login_date, DATEDIFF(login_date, LAG(login_date) OVER(PARTITION BY user_id ORDER BY login_date) ) AS day_gap FROM user_login;LAG()和ROW_NUMBER()同属窗口函数家族语法结构一模一样。理解了PARTITION BY的本质其他窗口函数如LAG()、LEAD()、SUM() OVER()、RANK()都能很快上手。6.3 保留分组内最新一条记录这是一个非常真实的业务需求。比如商品价格历史表要取每个商品当前最新的价格记录SELECT product_id, price, effective_date FROM ( SELECT product_id, price, effective_date, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY effective_date DESC) AS rn FROM product_price_history ) t WHERE rn 1;这里需要强调一个细节ORDER BY effective_date DESC的字段必须是能区分新旧的时间戳。如果时间戳精确到天且同一天有多条变更记录排序不稳定取出来的可能不是真正最新的。更好的做法是让排序字段唯一或者加上自增 ID 倒序作为第二排序条件。6.4 和COUNT(*) OVER()一起使用窗口函数不只有ROW_NUMBER()。当你在明细报表里想同时看到总数和明细时可以写SELECT department, emp_name, salary, COUNT(*) OVER(PARTITION BY department) AS dept_total_cnt, SUM(salary) OVER(PARTITION BY department) AS dept_total_salary FROM employees;这个查询返回 9 行每一行旁边都带着所属部门的总人数和总工资。同样是“按部门分组”GROUP BY只能折叠成 3 行而窗口版保留了 9 行明细这就是报表场景里常说的“明细与汇总共存”。7. 慢 SQL 排查视角窗口函数何时会成为性能陷阱热词里“慢sql优化”是高频检索词。在实际生产库里窗口函数引发慢查询的几率比GROUP BY高原因主要在于排序。一个 1000 万行的大表GROUP BY department走 HashAggregate 时如果内存足够只需要一遍扫描加哈希计算窗口函数则需要按部门分区再在分区内排序排序开销远高于哈希聚合。如果PARTITION BY列上有大量重复值每个分区又很大排序临时文件可能被打到磁盘IO 飙升。排查这类问题的方式是用EXPLAIN看执行计划EXPLAIN SELECT department, emp_name, salary, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS rn FROM employees;看到Extra列出现Using filesort时就要警惕。优化方向前面提过缩小数据集、建联合索引、必要时改写语句。还有一个实操技巧如果分区字段有索引但排序字段没有可以尝试把排序字段嵌入联合索引让 B 树的物理顺序天然有序省掉 filesort。需要说明的是并不是窗口函数一定比GROUP BY慢。当分组数量极大、每组行数很少时窗口函数的执行效率可能优于GROUP BY 自连接的写法。任何脱离数据量谈性能都是耍流氓。8. 我的实操习惯写分组和编号 SQL 时的几个检查点写多了之后我每次写完相关 SQL 都会按固定顺序自查一遍这里分享出来。这套检查点在面试写题时尤其有用因为白板 SQL 没有执行机会只能靠逻辑自查。第一先问自己“结果要几行”。如果需求是“每个组要一行汇总”走向GROUP BY如果需求是“每一行都要在只是要分组排名或编号”走向ROW_NUMBER()。行数是第一判断标准。第二看 SELECT 列表里有没有非聚合字段。如果查询目标是明细字段而不是统计值那基本注定要窗口函数或者子查询关联。第三检查PARTITION BY和ORDER BY是否成对出现。ROW_NUMBER()里PARTITION BY可以省略这时候就是全局编号但ORDER BY强烈建议写完整且稳定否则编号随机。SQL Server 里甚至强制要求写ORDER BY。第四确认外层过滤是否用对了。取每组第一条用WHERE rn 1取前三条用WHERE rn 3这里注意必须是子查询包裹后在外层过滤不能在同一层过滤。第五如果和COUNT(*) OVER()这类聚合窗口混合用注意输出行数可能远大于预期别拿 9 行明细数据当成 3 行分组汇总去展示。这五个检查点是我从实际项目里拆出来的今天一并写在这里。如果你能把这套思路内化再遇到类似的“分组 vs 编号”问题就不会纠结语法了。