写报表统计的时候，我经常遇到一类需求：按部门统计员工人数，顺手把每个人在部门内的序号带出来，或者把每组前几名单独列一列。过去我习惯性写窗口函数，ROW_NUMBER() OVER(PARTITION BY ...)一行搞定，干净利落。直到接手了一个跑在 MySQL 5.7 上的老系统，窗口函数完全不支持，只能另想办法。折腾一圈发现，用GROUP BY配合各种小技巧，完全可以达到和窗口函数一模一样的效果，而且不止“能用”，理解底层逻辑之后写出来的 SQL 反而更稳、更好维护。这篇文章就围绕“group by 组内分组统计数量”这个需求，把实现思路、完整 SQL、适用场景和踩坑记录都拆开讲清楚，希望能帮到正在被老库兼容性问题折磨的朋友。
1. 核心需求拆解：为什么有人会拿 GROUP BY 硬碰窗口函数
1.1 一个看似矛盾的需求
先明确一个概念。窗口函数和GROUP BY在工作方式上有本质区别：
- 窗口函数（如
ROW_NUMBER() OVER (PARTITION BY ...)）不会压缩行数，它只是在每一行旁边追加一个计算列，组内每条明细记录都保留着。 GROUP BY是聚合操作，它的默认行为是把一个组压缩成一行。比如SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id返回的是部门维度的人数，看不到组内每个人的姓名。
所以标题里“group by 组内分组统计数量，达到和窗口函数一样的效果”，听起来就很矛盾：我既要分组的统计结果，又要保留组内的明细行和序号。但实际业务场景中，这种需求非常普遍。比如：
- 给每个部门的员工按入职时间排号，输出
部门ID、姓名、部门内序号； - 统计每个客户的订单数，同时输出每个订单在该客户下的“第几单”；
- 求每组薪资排名前 3 的员工，还要保留排名数字本身；
- 分页场景下，需要按分类输出每条记录在类目内的位置。
这些需求在 MySQL 5.7 及以下版本、或者某些不支持窗口函数的数仓方言里，就必须用GROUP BY做“曲线救国”。
1.2 适用的真实场景
我遇到过几类典型环境，窗口函数用不了，只能走GROUP BY方案：
老版本 MySQL 数据库
很多金融、传统行业的核心系统，数据库还停在 5.7 甚至 5.6。这些版本的官方文档里明确没有窗口函数，升级成本又高（涉及主从复制、存储过程、历史 SQL 兼容性），只能靠业务 SQL 自己绕。
对接多套异构数据库
有一次做数据同步项目，同一个报表脚本要跑在 MySQL 和某国产数据库上。那边方言对窗口函数的支持非常有限，PARTITION BY写了报错。为了维护一套逻辑，我直接用GROUP BY + 关联子查询重写，两边都能跑。
大数据量下的分组排序内存压力
窗口函数在分布式引擎里是按分区并行执行的，但单机数据库里，OVER (PARTITION BY 大字段)往往会把整个分区的数据加载到内存排序。如果分区数量少、单个分区行数特别多，很容易撑爆内存。改用GROUP BY + 自连接，可以通过索引下推减少扫描量，反而更稳。
1.3 两种“分组统计数量”要区分清楚
这里必须先把概念掰开。标题里的“group by 组内分组统计数量”，实际上存在两种理解方式：
分组后统计每组的总数量比如按部门统计人数，结果只保留部门编号和人数字段，明细行不保留。这种是
SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id，最基础，不需要窗口函数也能做。分组后统计每组的总数量，同时把组内每条记录的“序号”或“累计计数”带出来比如每个部门 5 个人，我要看到第 1 条、第 2 条……第 5 条记录各自的序号。这个才需要窗口函数，也只有这个才值得大费周章用
GROUP BY去模拟。
我们这篇文章重点说的是第二种。后面所有方案，都以“明细行不丢、组内有序号、数量不重不漏”为目标。
2. 实现方案选型：三条可行路径对比，别只盯着 JOIN
在 MySQL 5.7 环境下，想模拟窗口函数的效果，我实际测试过三种主流写法。先看整体对比，后面逐一拆解。
| 方案 | 核心思路 | 输出内容 | 适用场景 | 主要风险 |
|---|---|---|---|---|
| 方案A | GROUP BY+ 自连接 | 组内序号、明细行 | 中等数据量，分组字段有索引 | 关联条件写错会导致数据膨胀 |
| 方案B | GROUP BY+GROUP_CONCAT截断 | 每组前 N 行拼接结果 | 只取每组前几条，不需要完整序号列表 | 字符串长度受group_concat_max_len限制 |
| 方案C | 用户变量模拟行号 | 组内序号、明细行 | 老库、单表扫描、数据量几万到几十万 | 变量赋值顺序和排序顺序必须严格配合 |
三个方案里，方案A最容易理解，也最接近 SQL 标准思路；方案B写法最简单但内聚度高，不适合二次加工；方案C执行效率最高，但坑最多。下面详细说。
2.1 方案A：GROUP BY+ 自连接统计序号
这个方案的思路是：要算某条记录在它所在组内的排名，就先找出所有“排在它前面或者和它并列”的记录，数一数有多少条，这个数量恰好就是它的序号。
操作步骤拆开是这样的：
- 用
GROUP BY对组字段（比如dept_id）分组。 - 自连接同一个表，关联条件是“分组字段相同，并且排序字段满足一定顺序关系”。
- 对明细表的每条记录，统计关联后符合条件的行数，得到组内序号。
写出 SQL 就是：
SELECT e.dept_id, e.name, COUNT(*) AS rn FROM emp e JOIN emp e2 ON e.dept_id = e2.dept_id AND e2.salary >= e.salary GROUP BY e.dept_id, e.id, e.name ORDER BY e.dept_id, rn ASC;这个 SQL 的原理细看很有意思。e2.salary >= e.salary这个条件，表示“找同部门里薪资不低于我的人”。因为自己和自己比较也是相等的，所以每个员工至少会关联上自己一条记录。COUNT(*)统计出来的行数，就是“同部门里工资比我高或和我相同的人数”，从 1 开始递增，恰好等于ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC)的效果。
如果想要的序号是倒过来的，比如按薪资从低到高排，只需要把关联条件改成e2.salary <= e.salary。
这个方案最大的优势是纯标准 SQL，不需要变量，不需要GROUP_CONCAT，在任何数据库上都能跑。缺点也很明显：如果部门里人很多，自连接会产生 N×N 的中间结果，部门 100 人就要比较 10000 次。所以这个方案更适合分组字段上有索引、且单组数据量控制在几百行以内的场景。
2.2 方案B：GROUP_CONCAT截断法
如果需求只是“把每组前 N 条数据取出来”，不需要把序号数字存下来，那么用GROUP_CONCAT是最省事的。
举个例子：要求每个部门里薪资最高的前 3 名，把名字拼成一个字段输出。SQL 可以写成：
SELECT dept_id, SUBSTRING_INDEX( GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ','), ',', 3 ) AS top3_names FROM emp GROUP BY dept_id;GROUP_CONCAT先把每个部门的所有姓名按薪资降序拼接成一个字符串，然后SUBSTRING_INDEX从左边截取前 3 个逗号分隔的片段。
它的优点是：只扫描一次表，性能不错，代码非常短。缺点也很直接：
- 拿不到序号数字，只有拼接结果，如果要输出
第1名、第2名、第3名还得二次解析； - 默认
group_concat_max_len只有 1024 字节，如果组内记录多、字段长，会被静默截断； - 这种方法只适合“取前 N 条”，不适合“给每组每行都编序号”。
所以方案B适合快速临时看数，不适合作为正式报表的数据源。
2.3 方案C：用户变量模拟行号
如果一定要在明细行旁边输出组内序号，同时又对性能有要求，那我通常选变量法。思路是：先按组字段和排序字段排好序，然后用一个用户变量记录当前组，另一个变量记录组内行号。
SELECT dept_id, name, rn FROM ( SELECT dept_id, name, @rn := IF(@cur_dept = dept_id, @rn + 1, 1) AS rn, @cur_dept := dept_id FROM emp, (SELECT @rn := 0, @cur_dept := NULL) var ORDER BY dept_id, salary DESC ) t;重点全在子查询里那两行赋值语句：
@rn := IF(@cur_dept = dept_id, @rn + 1, 1)：如果当前行和上一行属于同一个部门，序号加 1；否则重置为 1。@cur_dept := dept_id：把当前部门记录下来，供下一行比较。
这两条语句的执行顺序不能反。MySQL 对SELECT表达式的求值顺序是从左到右，所以必须先算@rn，再更新@cur_dept。如果写反了，就会出现第一行就判定@cur_dept = dept_id为真、直接序号加 1 的 bug。
变量法的性能非常好，只需要一次全表扫描加一次排序，没有自连接那种笛卡尔积爆炸的风险。在几十万行的表上测试，变量法通常比自连接快一个数量级。但它的坑藏在执行计划里，后面单开一节讲怎么防。
3. 实操过程与核心环节实现：从需求到 SQL 的完整走查
3.1 构造模拟数据并明确查询目标
为了验证方案对不对、效果是否一致，我先造一张员工表，模拟某公司的员工数据。表和数据的 DDL 如下：
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), dept_id INT, salary DECIMAL(10,2), entry_date DATE ); INSERT INTO emp (name, dept_id, salary, entry_date) VALUES ('张三', 1, 8000.00, '2020-03-01'), ('李四', 1, 9500.00, '2019-07-15'), ('王五', 1, 9500.00, '2021-01-20'), ('赵六', 1, 7200.00, '2022-05-10'), ('孙七', 2, 11000.00, '2018-11-02'), ('周八', 2, 6800.00, '2023-02-18'), ('吴九', 2, 8900.00, '2020-09-30'), ('郑十', 3, 15000.00, '2017-06-01'), ('冯十一', 3, 12000.00, '2021-12-08');现在目标很明确：按部门分组，统计每个部门的人数，同时给每个人输出一个部门内序号。序号规则按薪资降序，薪资一样的人并列不影响后续处理（这里只是验证序号唯一性）。
期望的输出是：
| dept_id | name | 组内人数 | rn |
|---|---|---|---|
| 1 | 李四 | 4 | 1 |
| 1 | 王五 | 4 | 2 |
| 1 | 张三 | 4 | 3 |
| 1 | 赵六 | 4 | 4 |
| 2 | 孙七 | 3 | 1 |
| 2 | 吴九 | 3 | 2 |
| 2 | 周八 | 3 | 3 |
| 3 | 郑十 | 2 | 1 |
| 3 | 冯十一 | 2 | 2 |
这里“组内人数”是每个部门的总数，“rn”是组内序号。窗口函数可以直接一行算出两个字段，GROUP BY方案能不能也同时输出，下面操作给你看。
3.2 先写“标准答案”窗口函数版本
新数据库上，用窗口函数实现上述目标很简单：
SELECT dept_id, name, COUNT(*) OVER (PARTITION BY dept_id) AS dept_cnt, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ORDER BY dept_id, rn;COUNT(*) OVER (PARTITION BY dept_id)就是组内人数，它不会因为聚合压缩行数；ROW_NUMBER()给组内每条记录编序号。这个版本逻辑清晰、可读性最高，也理应是我们验证其他方案的参照基准。
但问题在于：老版本的 MySQL 执行这段 SQL 会直接报语法错误。所以必须换一种实现，让结果和它完全一致。
3.3 用GROUP BY实现同样效果：完整版本
第一种写法，自连接 +GROUP BY。不仅算出序号，还把组内总人数一并算出来。SQL 如下：
SELECT e.dept_id, e.name, cnt.dept_cnt, COUNT(*) AS rn FROM emp e JOIN ( SELECT dept_id, COUNT(*) AS dept_cnt FROM emp GROUP BY dept_id ) cnt ON e.dept_id = cnt.dept_id JOIN emp e2 ON e.dept_id = e2.dept_id AND e2.salary >= e.salary GROUP BY e.dept_id, e.id, e.name, cnt.dept_cnt ORDER BY e.dept_id, rn ASC;拆开看这部分：
- 子查询
cnt先按部门GROUP BY，统计出每个部门的总人数，这是“分组统计数量”的部分； - 主查询再把员工表和这个统计结果通过
dept_id关联，明细行不丢； - 第二次自连接
e2配合e2.salary >= e.salary，用COUNT(*)生成组内序号。
这三段各干一件事，逻辑非常清晰。跑出来的结果和窗口函数版本完全一致。
第二种写法，变量法。同样能同时输出组内序号和组内总人数，但总人数要用子查询先算好，再关联回去：
SELECT t.dept_id, t.name, cnt.dept_cnt, t.rn FROM ( SELECT dept_id, name, @rn := IF(@cur_dept = dept_id, @rn + 1, 1) AS rn, @cur_dept := dept_id FROM emp, (SELECT @rn := 0, @cur_dept := NULL) var ORDER BY dept_id, salary DESC ) t JOIN ( SELECT dept_id, COUNT(*) AS dept_cnt FROM emp GROUP BY dept_id ) cnt ON t.dept_id = cnt.dept_id ORDER BY t.dept_id, t.rn;这种写法在数据量大时性能优势明显，因为它只需要一次排序，而自连接方案会产生组内两两比较。数据量在 10 万行以上时，变量法一般比自连接快 3 倍以上。
第三种用法，如果你只需要组内人数、不需要序号，那就更简单了：
SELECT dept_id, COUNT(*) AS dept_cnt FROM emp GROUP BY dept_id;这个看起来简单，但很多时候业务方是先要这个总数，后来又加了一句“能不能把每个人也列出来”，才引出我们前面两种复杂写法。
3.4 为什么能得到相同的数字：底层逻辑说明
我刚开始用自连接方案时，一直有个疑问：为什么COUNT(*)算出来的值恰好就是序号？
想通这个问题，SQL 水平能上一个台阶。核心在于关联条件e2.salary >= e.salary定义了一个“集合”：对员工张三来说，同部门里所有薪资大于等于他的人都在这个集合里。由于包含自己，集合非空；集合的大小就是序号。
如果两个员工薪资相同（李四和王五都是 9500），那么：
- 李四统计时，符合条件的包括李四自己和王五，
COUNT(*)得到 2； - 王五统计时，符合条件的也是李四和王五，
COUNT(*)也得到 2。
所以这个方法天然会产生“并列排名”。如果你要的是ROW_NUMBER()那种强制唯一名次，而不是RANK()那种并列名次，需要额外加一个唯一字段作为排序条件的补充，比如：
AND ( e2.salary > e.salary OR (e2.salary = e.salary AND e2.id <= e.id) )加了这个条件后，薪资相同的人会按id进一步区分排名，COUNT(*)的结果就是唯一的行号，不会出现两个人同名次。这个细节在实际报表里很重要，比如按评分排名，分数一样但名次必须分先后。
4. 常见问题与排查技巧实录：这些坑我全踩过
4.1 分组后“明细行消失”的误解
很多朋友刚接触GROUP BY时，一听到“分组统计数量”，下意识就写：
SELECT dept_id, name, COUNT(*) FROM emp GROUP BY dept_id;这条 SQL 在 MySQL 5.7 里甚至能跑通，但name返回的是组内随机一条，逻辑上不严谨；在ONLY_FULL_GROUP_BY模式下直接报错。它的本质问题是：GROUP BY dept_id把部门压缩成一行，没有再装下所有明细行的空间。
出现这个问题，不是GROUP BY本身不能保留明细行，而是写法不对。只要把明细条件分成两段——一段统计数量，一段关联明细，就能解决。也就是方案A和方案C的路子。
记住一句话：GROUP BY负责产生组维度信息，关联/子查询负责把明细行带回来，两者互补，而不是二选一。
4.2 自连接数据膨胀，统计数量翻倍
自连接方案最常见的问题，就是统计出来的数量比实际大。我见过一个案例，统计每个订单里的商品件数，结果数量翻了一倍。原因出在关联条件上：
JOIN order_item e2 ON e.order_id = e2.order_id这个条件只关联了订单 ID，没有限定“组内排名的顺序关系”。这样一来，一条明细会和自己以及所有同类记录都匹配上，COUNT(*)自然失控。
排查技巧：先去掉GROUP BY，单看某一条记录的关联结果是多少行。比如查订单 1001 的第一条商品：
SELECT COUNT(*) FROM order_item e JOIN order_item e2 ON e.order_id = e2.order_id WHERE e.id = 100101;如果结果是 5 而不是 1，说明关联条件漏了排序字段。正确的条件必须包含“范围约束”，比如e2.salary >= e.salary或者e2.id <= e.id，这是把“所有关联行”变成“排在我前面的行”的关键。
4.3 变量法排序顺序不稳定，序号错乱
变量法的折磨点在于 MySQL 的优化器。子查询里的ORDER BY有时候会被优化器“提前”处理，导致变量赋值的顺序和你预期不一致。最典型的表现是：序号正常，但同组内的行顺序是乱的。
我个人的实测结论是，在 MySQL 5.7 某些版本上，如果派生表数据量小、可以被优化器合并，ORDER BY可能失效。一个稳妥的加固办法是给派生表加LIMIT，强制物化：
SELECT dept_id, name, rn FROM ( SELECT dept_id, name, @rn := IF(@cur_dept = dept_id, @rn + 1, 1) AS rn, @cur_dept := dept_id FROM emp, (SELECT @rn := 0, @cur_dept := NULL) var ORDER BY dept_id, salary DESC LIMIT 18446744073709551615 ) t;LIMIT后面跟一个极大的数，是为了避免 MySQL 把派生表合并进外层查询，强制它物化成临时表，排序结果得以保留。这个技巧我在生产环境验证过，非常管用。唯一的代价是可能多一次临时表落盘，数据量极大时要评估磁盘空间。
另外要注意，变量法依赖“记录按组连续排列”。如果ORDER BY dept_id, salary DESC里的dept_id不唯一、顺序不稳定，分组判定就会错乱。所以在排序字段里，不要只写ORDER BY dept_id，尽量把排序字段写完整，避免并列情况造成顺序漂移。
4.4GROUP_CONCAT被截断，结果静默丢失
方案B看起来简单，但有一个隐蔽的坑：group_concat_max_len默认值是 1024 字节。如果组内记录多、姓名或者拼接字段长，超过 1024 字节后，GROUP_CONCAT会静默截断，后面的数据直接丢掉，而 SQL 不报错。
解决方法是先确认会话变量大小，再按需调大：
SHOW VARIABLES LIKE 'group_concat_max_len'; SET SESSION group_concat_max_len = 102400;这个变量是会话级的，每次重连都会重置。如果通过中间件连接数据库，最好在初始化 SQL 里统一设置，否则很容易出现“开发环境正常、生产环境数据缺一段”的诡异问题。
4.5 性能对比：什么规模下该选哪种方案
我在一张 50 万行的订单表上做过对比测试，三个方案在单机 MySQL 5.7 下的表现差异很大。简单整理一下个人结论：
| 数据量 | 自连接方案 | GROUP_CONCAT方案 | 变量法方案 |
|---|---|---|---|
| 1 万行以下 | 秒回 | 秒回 | 秒回 |
| 10 万行 | 400~600 ms | 200 ms | 80 ms |
| 50 万行 | 4~8 秒 | 1 秒 | 350 ms |
| 组内行数特别大 | 中间结果膨胀 | 字符串长度受限 | 排序开销可控 |
自连接方案最怕“组内行数多”，因为复杂度是组内行数的平方。如果表上有 1000 人的大部门，这个部门内部就要比较 100 万次，非常吃力。变量法虽然写起来别扭，但性能最稳定，也最接近窗口函数的执行效率。所以我现在的选型原则是：
- 数据量小（几千行以内）：优先自连接方案，可读性好，同事接手容易懂；
- 数据量中等（几万到几十万行）：用变量法，配合
EXPLAIN检查执行计划； - 只取每组前 N 条：用
GROUP_CONCAT，但要先调大group_concat_max_len。
4.6 空值和 NULL 分组导致的记录丢失
还有一个细节。如果分组字段里有NULL，自连接方案和变量法都可能出问题。
先看自连接：ON e.dept_id = e2.dept_id这个条件对NULL不成立。如果某个员工部门字段是NULL，他和自己关联不上，COUNT(*)会统计不到，导致这个员工直接消失。
再看变量法：@cur_dept = dept_id比较时，NULL = NULL的结果是NULL，不是真值，会错误触发“组变更”，导致每组第一行序号错乱。
解决办法取决于业务语义：如果部门字段不应该为空，就在写入层规避；如果确实允许为空，可以单独抽出一个“未分组”的桶，或者用IFNULL把空值替换成一个固定值：
IFNULL(dept_id, -1) AS dept_id在排序和分组字段上都套一层，保持一致性，才能避免 NULL 导致的行丢失或序号错误。
5. 写在最后的个人体会
老系统上写 SQL，最忌讳的是一味追求“高级写法”。窗口函数确实好，但环境不支持时，GROUP BY照样能把事情做得漂亮。我个人这几年的体会是：能写标准 SQL 就别依赖方言特性，能在明细行保留信息就别轻易丢行，能用简单关联解决就别上复杂变量。自连接方案的可读性最好，变量法的性能上限最高，两者互补，基本覆盖了大部分老库分组统计场景。如果哪天莫名其妙出现“序号错乱”“数据翻倍”这类问题，别怀疑数据，先查关联条件和排序顺序，九成问题出在那里。
