先说结论在 MySQL 5.7 以及更早版本里想用 group by 完成组内分组统计数量、组内排名、取每组 Top N 这类操作确实有办法。核心思路不是换一种聚合函数而是借助用户变量把“分组维度”和“行号”手工补出来效果上能对标 8.0 的窗口函数 ROW_NUMBER() OVER(PARTITION BY ...) 和 COUNT(*) OVER(PARTITION BY ...)。这个需求我太熟了。前两年做报表系统线上库还是 MySQL 5.7业务方提了一堆“每个用户最近三笔订单”“每个部门工资最高的员工”“按月统计每个产品的累计销量”这类需求。听起来不难但真正动手写 SQL 时才发现group by 会把明细行压成一行根本没法在保留原始行的同时做组内标记。后面我折腾出了一套相对稳定的写法也踩了不少坑这里完整记录一下。1. 为什么会有这种奇怪需求group by 和窗口函数的本质差异1.1 一个再常见不过的业务场景假设有一张订单表order_id订单号user_id用户 IDorder_amount订单金额order_time下单时间业务要的是每个用户最近一笔订单的完整信息甚至要“每个用户的订单序号”方便做数据分析时知道首单、二单、三单。用 MySQL 8.0 写就一行SELECT order_id, user_id, order_amount, order_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS user_order_seq FROM order_table;但 MySQL 5.7 不支持窗口函数。直接报语法错误ROW_NUMBER根本不认识。这时候很多人的第一反应是用 group by 啊。写出来是SELECT user_id, COUNT(*) FROM order_table GROUP BY user_id;这只能得到每个用户的订单总数。它给不了“最近一笔订单是哪一条”也给不了“第二笔是哪一条”。group by 把同一用户的多行压缩成了一行明细信息全部丢失。1.2 窗口函数到底干了什么事窗口函数和 group by 最大的区别可以用一句话概括group by 是“多行变一行”压缩明细保留分组统计。窗口函数是“每一行都保留但在每一行的旁边开一扇窗户”透过窗户能看到所在分组的信息。ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC)做的事情是把数据按 user_id 切开成很多小分组在每个分组内部再按 order_time 排序然后编上行号 1、2、3……这个行号会出现在每一行上原始行不会消失。COUNT(*) OVER (PARTITION BY user_id)则是每一行旁边都挂一个“当前用户的总订单数”相当于把 group by 的统计结果复制到了每个组内的每一行。窗口函数之所以好用就是因为它把“分组统计”和“明细展示”结合在了一起。1.3 老版本数据库的无奈事实上不只是 MySQL 5.7。很多数据库的早期版本或者某些基于开源内核二次开发的国产数据库对窗口函数的支持都不完整。有些建了表、导了数据才发现窗口函数用不了只能改 SQL。所以这套“用户变量模拟窗口函数”的思路短期内依然有实用价值。它还能帮你更深入地理解 group by、排序、分组这三件事的执行逻辑对 SQL 功底的打磨也有好处。2. GROUP BY 的固有盲区能聚合、能过滤就是不能标号2.1 GROUP BY 的三板斧group by 配合聚合函数可以做很多事情COUNT组内行数SUM组内求和AVG组内平均MAX/MIN组内极值HAVING对聚合结果做过滤比如只保留订单数大于 5 的用户SELECT user_id, COUNT(*) AS order_cnt, SUM(order_amount) AS total_amount FROM order_table GROUP BY user_id HAVING COUNT(*) 5;这套组合拳在“只关心整体统计不关心明细”时非常高效。但它的盲区也很明显2.2 盲区一无法把聚合结果“摊回”明细行window 函数能做的一件事group by 做不到也没有聚合函数能替代。那就是把“该组总行数”这样的统计值关联到组内每一行上。结果集应该是order_iduser_idorder_cnt1001A00131002A00131003A0013group by 做不到这种效果它一分组就把三行合并成一行了。2.3 盲区二无法给组内行编序号“组内编号”本质上是行级操作。group by 聚合完之后组内各行已经不存在了自然无从编号。要用 group by 勉强实现“组内 Top N”只能先聚合出分组再回表 join 原数据写法笨重且效率低下。比如“每个用户最近一笔订单”SELECT o.* FROM order_table o JOIN ( SELECT user_id, MAX(order_time) AS max_time FROM order_table GROUP BY user_id ) t ON o.user_id t.user_id AND o.order_time t.max_time;这个写法能拿到每个用户最近一笔订单但存在几个问题如果同一个用户在同一秒下了两单order_time相等会查出两笔甚至多笔。它只能取“极值行”取不了“第二笔”“第三笔”。数据量大时这种 join 很容易产生临时表性能不稳定。所以要想达到窗口函数“既能分组统计又能保留行”的效果必须换思路。3. 靠用户变量模拟 ROW_NUMBER核心原理与三个铁律3.1 用户变量为什么能模拟窗口函数用户变量是 MySQL 特有的机制用变量名表示在一条 SQL 语句执行过程中可以存储临时值。模拟分组的核心逻辑只有四句话先把数据按照分组字段和组内排序字段排好序。从第一行开始扫描。用变量记住当前行的分组键。如果下一行和当前行分组键相同序号加一如果分组键变了序号重置为 1。翻译成 SQL 就是SELECT order_id, user_id, row_num : IF(current_user user_id, row_num 1, 1) AS user_order_seq FROM ( SELECT order_id, user_id FROM order_table ORDER BY user_id, order_time DESC ) t CROSS JOIN (SELECT row_num : 0, current_user : NULL) vars;注意我这里在子查询里完成了排序外层再做变量累加。这个顺序极其重要。3.2 三个铁律缺一不可总结我踩过无数坑之后沉淀下来的三条规则铁律一排序必须放在子查询里不能和外层变量赋值混在一起。MySQL 5.7 执行时ORDER BY和SELECT列表的顺序并不绝对安全。如果你直接写SELECT order_id, user_id, row_num : IF(current_user user_id, row_num 1, 1) AS user_order_seq FROM order_table ORDER BY user_id, order_time DESC;有时候结果看起来是对的但只要数据量一大、索引一变行号就可能错乱。原因很简单变量是在SELECT阶段逐行计算的而排序发生在之后相当于你还没排好序就开始数数了。先排序、后计算这是模拟方案的地基。铁律二变量初始化要用独立的CROSS JOIN子查询完成。每条 SQL 开头都要给变量赋初值CROSS JOIN (SELECT row_num : 0, current_user : NULL) vars;不初始化的话同一个连接里上一次查询残留的变量值会污染本次结果行号可能从 8 开始、从 15 开始完全随机。铁律三分组键字段和排序字段要稳定。分组键必须保证相同的值在最终排序结果中是连续相邻的。也就是说ORDER BY的第一个字段必须就是分组键。如果你的分组键是数字排序字段是文本要特别注意隐式类型转换可能导致分组键被改写。3.3 一个直观的小例子假设order_table只有三个用户的数据order_iduser_idorder_amountorder_time1A1002024-01-01 10:00:002A2002024-01-02 10:00:003B1502024-01-01 09:00:004B502024-01-03 10:00:005C802024-01-02 08:00:00执行上面那段 SQL结果会是order_iduser_iduser_order_seq2A11A24B13B25C1这已经和窗口函数效果一样了每个用户独立编号组内按时间倒序。4. 一个完整实战按部门统计人数、再给组内每个人排名4.1 建表和测试数据下面用一个更完整的例子说明“组内分组统计数量”和“行号”同时要的效果。CREATE TABLE emp ( id INT PRIMARY KEY, dept_name VARCHAR(20), emp_name VARCHAR(20), salary DECIMAL(10,2) ); INSERT INTO emp VALUES (1, 技术部, 张三, 20000), (2, 技术部, 李四, 18000), (3, 技术部, 王五, 18000), (4, 市场部, 赵六, 15000), (5, 市场部, 钱七, 16000), (6, 运营部, 孙八, 12000);需求有两个每一行都显示所在部门的总人数。每一行都显示“本人在部门内按工资从高到低排第几”。这就是窗口函数里COUNT(*) OVER(PARTITION BY dept_name)加ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY salary DESC)的效果。4.2 老版本下的完整写法SELECT emp_id, emp_name, dept_name, salary, dept_total, rn AS dept_rank FROM ( SELECT emp_id, emp_name, dept_name, salary, row_num : IF(current_dept dept_name, row_num 1, 1) AS rn, current_dept : dept_name AS dummy_dept FROM ( SELECT id AS emp_id, emp_name, dept_name, salary FROM emp ORDER BY dept_name, salary DESC ) ordered_emp CROSS JOIN (SELECT row_num : 0, current_dept : NULL) vars ) t JOIN ( SELECT dept_name, COUNT(*) AS dept_total FROM emp GROUP BY dept_name ) d ON t.dept_name d.dept_name ORDER BY t.dept_name, t.rn;结果emp_idemp_namedept_namesalarydept_totaldept_rank1张三技术部20000312李四技术部18000323王五技术部18000335钱七市场部16000214赵六市场部15000226孙八运营部1200011这里有两个核心点“部门总人数”用的是常规GROUP BY dept_name先统计再 JOIN 回明细行。这就是把聚合结果“摊回”明细行。“组内排名”用的是用户变量。注意我专门把current_dept : dept_name赋值放在 SELECT 列表第二个位置确保它晚于row_num的计算。这个写法在 MySQL 5.7 中依赖 SELECT 列表从左到右的赋值顺序实测稳定但官方文档确实提过用户变量赋值顺序不受保证。所以我在外层又包了一层把辅助列dummy_dept过滤掉。4.3 并列排名怎么处理如果想让工资相同的员工并列排名比如技术部的李四和王五都是 18000期望都是第 2 名那用ROW_NUMBER模拟就不够用了。因为ROW_NUMBER本身对并列值也会强制区分先后。MySQL 5.7 要模拟DENSE_RANK得再加一个变量记录“上一行的 salary”dense_rank : IF( current_dept dept_name, IF(current_salary salary, dense_rank, row_num 1), 1 )这个写法需要同时维护部门变化和工资变化两个判断条件复杂度明显上升。这也是为什么我后来强烈建议如果只是报表分析需求能用新版本就用新版本别在旧版本上硬造轮子。5. 复合分组GROUP BY 多个字段时变量重置条件怎么改5.1 场景按“部门 职级”分别编号实际业务里分组键很少只有一个字段。常见的是“每个部门每个职级按薪资排名”“每个店铺每个品类按销量排名”。假设表格增加job_level字段需求变成在每个部门下再按职级切分组内重新编号。这时候只需要把“分组键”从单字段变成字段组合重置条件从IF(current_dept dept_name, ...)变成IF( current_dept dept_name AND current_level job_level, row_num 1, 1 )注意ORDER BY也要跟着扩展ORDER BY dept_name, job_level, salary DESC排序字段的顺序必须和分组键顺序一致否则同一个分组的数据会被其他组的数据隔开变量就没办法正确判断“边界”。5.2 用 CONCAT 会不会更简单有人喜欢把多个字段拼成一个字符串再和变量比较IF(current_group CONCAT(dept_name, -, job_level), row_num 1, 1)这个写法能用但我不推荐尤其是当字段本身就可能包含分隔符的时候。比如dept_name是 “技术-研发”job_level是 “高级”拼接出来是“技术-研发-高级”另一个组合dept_name是 “技术”job_level是 “研发-高级”拼出来一模一样。分组键被误判成同一个行号就会乱。老老实实写多个条件判断最多就是代码长一点但逻辑绝对清晰。真要拼也建议用CONCAT_WS加一个不太可能出现的分隔符并强制转换字段类型CONCAT_WS(::, CAST(dept_name AS CHAR), CAST(job_level AS CHAR))5.3 NULL 值的坑分组键字段一旦出现NULLIF(current_dept dept_name, ...)判断会出问题。因为NULL NULL的结果不是 true而是 unknown最终会导致每一行都重置序号。解决办法是把 NULL 转换成统一占位符IF( current_dept dept_name AND current_level job_level, row_num 1, 1 )MySQL 的是空安全等于运算符它能正确处理 NULL 和 NULL 的比较。这个细节太容易踩了尤其是数据里偶尔会有脏数据的场景。6. 与窗口函数逐项对比看似一样差距在哪6.1 功能对照表能力原生窗口函数用户变量模拟ROW_NUMBER 组内编号原生支持语法简洁可模拟逻辑直观COUNT(*) OVER(PARTITION BY) 组内总数原生支持需 JOIN 聚合子查询DENSE_RANK / RANK 并列排名原生支持要加变量复杂LAG / LEAD 取前后行原生支持很难模拟SUM(...) OVER(ORDER BY) 组内累计原生支持可模拟但结果不稳定多列分组直接写在 PARTITION BY 里需组合条件判断NULL 处理标准行为稳定需用 处理大数据量性能优化器统一调度依赖临时表排序可控性差表格可以看得很清楚用户变量能覆盖的场景主要是 ROW_NUMBER 和 COUNT OVER 这一类偏“行号”和“聚合值摊回”的需求。一旦涉及跨行取值、滑动窗口、组内累计模拟方案会越来越吃力。6.2 执行稳定性的差异窗口函数是 SQL 标准的一部分优化器会把它当成一个专门的计算 step 来执行执行计划是稳定、可预测的。用户变量模拟则有点像“边扫描边记账”它依赖于数据在磁盘上的访问顺序、排序是否真正完成、索引是否存在。一旦并发量上来或者查询计划因为统计信息变化而改变可能同样一条 SQL 今天行号是对的明天加了个索引就乱了。这种事我真实遇到过排查过程极其痛苦。6.3 什么时候仍然值得用用户变量方案线上数据库确实是老版本暂时没有升级计划。需求非常聚焦只需要组内编号这种单点能力。数据量不太大比如几十万行排序开销可以接受。迁移窗口函数的工作量大短平快用变量方案救急。如果满足这些条件用变量方案没问题。但心里要有数这是“补丁”不是“正解”。7. 踩坑实录六个别再犯的错7.1 坑一忘了初始化变量我用 Navicat 调试时经常遇到第一次执行结果正常第二次执行行号全乱了。原因就是同一个连接里上一次查询结束时row_num和current_dept还留着旧值。解决方式要么在每条 SQL 里显式CROSS JOIN (SELECT row_num : 0, current_dept : NULL) vars要么先单独执行一条 SET 语句。应用层连接池也要注意Java 应用通过连接池复用 MySQL 连接时变量是会话级的可能把上一次请求的残留数据带进来。每次 SQL 都带初始化子查询是最省心的做法。7.2 坑二排序字段类型不一致工资是DECIMAL(10,2)排序没问题。但如果你排序字段是 varchar比如order_no或者版本号那么ORDER BY version_no DESC它按字符串排序结果是 9 比 10 大2 比 15 大。这在模拟行号时会直接导致编号错位。正确做法是确保排序字段类型一致必要时显式 CASTORDER BY CAST(version_no AS UNSIGNED) DESC同时注意排序字段一旦隐式转换索引可能失效数据量一大性能就崩。7.3 坑三外层 ORDER BY 干扰变量计算很多人的写法是SELECT ..., row_num : IF(...) AS rn FROM emp ORDER BY dept_name, salary DESC;我刚学这个技巧时也这么写结果时对时错。后来我理解到SELECT列表中的变量赋值甚至可能在ORDER BY排序之前就被计算完了。所以外层只能负责最终展示顺序不能用外层ORDER BY来提供模拟所需的排序。一定要把排序放进子查询FROM ( SELECT ... FROM emp ORDER BY dept_name, salary DESC ) t然后在变量计算的外层可以再包一层查询把最终展示顺序重新排一次。7.4 坑四HAVING 和变量方案混用很多人习惯先写了GROUP BY再加HAVING过滤。但在用户变量模拟中如果外面套了 JOIN 或者 GROUP BY变量计算的时机可能变得不可控。比如你写SELECT dept_name, row_num : row_num 1 AS rn FROM emp GROUP BY dept_name这个row_num的计算发生在 group by 之后还是之前MySQL 版本的差异都会导致结果不同。不要试图把模拟方案和分组聚合混在一条 SQL 里先分别算好再 JOIN是最稳妥的。7.5 坑五分页数据参与重新编号如果要做分页比如先查 100 条再查 100 条绝不能直接在原 SQL 上加LIMIT。因为行号是在全部数据上算的一旦LIMIT提前截断变量扫描的数据源就不完整分页第二页的行号会从 1 重新开始。正确做法是把“编号查询”放到子查询里分页条件放在最外层SELECT * FROM ( SELECT emp_name, dept_name, salary, row_num : IF(current_dept dept_name, row_num 1, 1) AS rn, current_dept : dept_name AS dummy FROM (SELECT ... FROM emp ORDER BY dept_name, salary DESC) t CROSS JOIN (SELECT row_num : 0, current_dept : NULL) vars ) numbered LIMIT 100, 100;7.6 坑六连接池并发导致变量串数据最后说一个很隐蔽的问题。用户变量是“会话级”的不是“语句级”的。同一个数据库连接内部不同 SQL 之间变量会互相残留。如果应用层用连接池A 请求执行了带变量的 SQLB 请求复用同一个连接执行另一条变量 SQLB 的初始变量值就可能被 A 污染。解决方式除了每条 SQL 都显式初始化还可以在获取连接后单独调用SET row_num : 0之类语句不过最省心的还是在同一 SQL 里 CROSS JOIN 初始化。8. 写在最后一个更省心的习惯如果你手头还没有升级数据库的打算那我建议你把“窗口函数模拟”封装成标准 SQL 模板。每次用到就在项目里复制、改字段不要每次从零写。模板固定下来出错的概率会小很多。我个人的做法是把最常用的“单分组键行号”“复合分组键行号”“组内总数行号”三个模板存在团队的语雀文档里新人接手报表需求时直接拿来改而不是重新发明轮子。这样既保证了线上旧版本可用又为将来升级到 8.0 之后的迁移留了对照逻辑。最后再分享一个我的体会越是在旧版本数据库上写 SQL越要理解执行顺序和隐式类型转换。很多诡异的结果不是 SQL 语法错了而是变量计算时机和排序时机打架。把这两个问题想透你再看窗口函数会觉得它简直是数据库厂商送的礼物。如果你的数据库已经支持窗口函数真的别犹豫直接用它换掉变量模拟方案——省下的排查时间远比你想象的多。