如何在SELECT语句中实现按id分组的Multi Auto Increment？

这问题我之前也踩过坑！你要的其实是分组内的行号自增——也就是每当id值变化时，计数就重新从1开始。给你两种实用的解决办法，适配不同的数据库版本：

方法1：用窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

现代数据库都支持窗口函数，用ROW_NUMBER()配合PARTITION BY就能轻松实现需求，这也是最简洁的写法：

SELECT 
  ROW_NUMBER() OVER (PARTITION BY id ORDER BY value) AS increment,
  id,
  value
FROM your_table;

原理说明： #

• PARTITION BY id：把表中的数据按id分组，相同id的行会被归为一组
• ROW_NUMBER()：在每个分组内从1开始依次给行编号
• ORDER BY value：指定分组内的排序规则（如果不需要特定顺序，也可以换成你的主键字段，确保编号稳定）

用这个语句查询后，就能得到你期望的结果：

increment | id | value
----------|----|-------
1         | 1  | foo
2         | 1  | bar
1         | 2  | foo
1         | 3  | bar
2         | 3  | foo

方法2：用变量实现（兼容MySQL 5.7及以下版本） #

如果你的数据库不支持窗口函数，可以用用户变量来模拟分组计数的逻辑：

SELECT 
  @cnt := CASE 
            WHEN @prev_id = id THEN @cnt + 1 
            ELSE 1 
          END AS increment,
  id,
  value,
  @prev_id := id
FROM your_table,
     (SELECT @prev_id := NULL, @cnt := 0) AS vars
ORDER BY id, value;

原理说明： #

1. 先初始化两个变量：@prev_id用来记录上一行的id，@cnt用来记录当前分组的计数
2. 每一行判断当前id和@prev_id是否相同：
   • 如果相同，计数@cnt加1
   • 如果不同，重置计数为1
3. 最后更新@prev_id为当前行的id，供下一行判断使用
4. 必须加ORDER BY id：确保相同id的行连续排列，否则分组判断会出错

为什么你之前的写法不行？ #

你之前用的(@cnt := @cnt + 1)是全局递增的计数，没有判断id的变化，所以不会在新的id组里重置计数。上面的写法通过CASE WHEN增加了分组判断，就能实现你要的效果。

内容的提问来源于stack exchange，提问作者Mathias