MySQL如何为分组内的记录设置唯一连续序号

需求说明 #

为MyData表中每个State分组下的人员分配从1开始递增的StateID，切换State时序号重置，排序无需按字母顺序。

表结构与测试数据 #

CREATE TABLE MyData( UniqueID VARCHAR(64), State VARCHAR(64), PersonName VARCHAR(64), StateID INT );
INSERT INTO MyData VALUES 
( UUID(), 'CA', 'David', NULL ), 
( UUID(), 'CA', 'Ann', NULL ), 
( UUID(), 'CA', 'Bob', NULL ),
( UUID(), 'AL', 'Mary', NULL ), 
( UUID(), 'AL', 'Tom', NULL ), 
( UUID(), 'AL', 'Evelyn', NULL );

解决方案 #

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

利用ROW_NUMBER()窗口函数，通过PARTITION BY State实现分组内序号递增，ORDER BY可指定分组内的排序规则（示例用UniqueID，可按需替换为PersonName或其他字段）：

UPDATE MyData
JOIN (
    SELECT 
        UniqueID,
        ROW_NUMBER() OVER (PARTITION BY State ORDER BY UniqueID) AS NewStateID
    FROM MyData
) AS RankedData ON MyData.UniqueID = RankedData.UniqueID
SET MyData.StateID = RankedData.NewStateID;

方案2：MySQL 5.x版本（使用用户变量） #

通过用户变量跟踪当前State和序号，当State变化时重置序号。需先按State排序，再处理分组内计数：

UPDATE MyData
JOIN (
    SELECT 
        UniqueID,
        @row_num := CASE 
            WHEN @current_state = State THEN @row_num + 1 
            ELSE 1 
        END AS NewStateID,
        @current_state := State
    FROM MyData
    CROSS JOIN (SELECT @row_num := 0, @current_state := '') AS vars
    ORDER BY State, UniqueID -- 先按State分组，再指定组内排序规则
) AS RankedData ON MyData.UniqueID = RankedData.UniqueID
SET MyData.StateID = RankedData.NewStateID;

效果验证 #

执行完成后，可通过以下语句查看结果：

SELECT * FROM MyData ORDER BY State, StateID;

结果会显示每个State下StateID从1开始连续递增，跨State自动重置。

原问题分析 #

你之前的语句仅实现了全局序号递增，没有判断State的切换来重置计数器，因此无法满足分组内序号重置的需求。通过窗口函数或变量判断State变化的方式，即可解决该问题。

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