如何在MySQL分组内为每行分配连续序号

要在每个分组内生成从1开始的连续序号，你需要修改变量逻辑，跟踪当前分组并在分组切换时重置计数器。以下是两种可行方案：

方案1：使用用户变量（兼容MySQL 5.x及以上） #

修改你的UPDATE语句，引入两个用户变量分别跟踪当前分组和分组内序号：

UPDATE TempData
INNER JOIN (
    SELECT 
        GroupName,
        ItemName,
        @row_order := CASE 
            WHEN @current_group = GroupName THEN @row_order + 1 
            ELSE 1 
        END AS NewValue,
        @current_group := GroupName
    FROM TempData
    CROSS JOIN (SELECT @current_group := '', @row_order := 0) AS vars
    -- 可选：若需分组内固定排序，添加 ORDER BY GroupName, ItemName
) AS RowSequencing 
ON TempData.GroupName = RowSequencing.GroupName 
AND TempData.ItemName = RowSequencing.ItemName
SET TempData.Sequence = RowSequencing.NewValue;

逻辑说明 #

• @current_group：记录当前正在处理的分组，初始为空字符串
• @row_order：记录分组内的序号，初始为0
• CASE判断：如果当前行的分组与@current_group一致，序号自增；否则重置为1
• 每次处理完一行后，更新@current_group为当前行的分组，确保下一行判断准确

方案2：使用窗口函数（MySQL 8.0+ 推荐） #

如果你使用的是MySQL 8.0或更高版本，窗口函数ROW_NUMBER()可以更简洁地实现需求：

UPDATE TempData t
JOIN (
    SELECT 
        GroupName,
        ItemName,
        ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY (SELECT 0)) AS NewValue
    FROM TempData
) AS seq 
ON t.GroupName = seq.GroupName AND t.ItemName = seq.ItemName
SET t.Sequence = seq.NewValue;

逻辑说明 #

• PARTITION BY GroupName：按分组拆分数据集
• ORDER BY (SELECT 0)：表示按数据存储顺序生成序号（若需特定排序，替换为实际字段，如ORDER BY ItemName）
• ROW_NUMBER()：自动在每个分组内生成从1开始的连续唯一序号

验证结果 #

执行上述任一方案后，查询表数据：

SELECT * FROM TempData;

将得到预期结果：

GroupName | ItemName | Sequence
----------|----------|---------
Fruit     | Apple    | 1
Fruit     | Orange   | 2
Fruit     | Banana   | 3
Animal    | Dog      | 1
Animal    | Cat      | 2
Animal    | Horse    | 3

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