基于分组更新变量：为不同州的表记录分配递增整数值

嘿，我懂你要做的事儿——按州分组给每条记录的StateNum字段分配对应的整数值对吧？你给出的SQL片段写法不太对，GROUP BY在UPDATE语句里这么用没法实现需求。我给你几种靠谱的实现方式，适配不同的数据库：

方法1：用CTE生成州编号再更新（适用于SQL Server、PostgreSQL等） #

这种写法逻辑清晰，先给每个州生成唯一的整数编号，再关联原表完成更新：

WITH StateNumbers AS (
    SELECT 
        state,
        ROW_NUMBER() OVER (ORDER BY state) AS StateNum
    FROM your_table
    GROUP BY state
)
UPDATE t
SET t.StateNum = sn.StateNum
FROM your_table t
JOIN StateNumbers sn ON t.state = sn.state;

解释：ROW_NUMBER()函数会按州名的排序顺序，给每个州分配一个递增的整数（比如德州排第1，佛罗里达排第2，以此类推）。如果需要手动指定某些州的编号（比如强制德州为1、佛罗里达为2），可以用CASE语句调整：

WITH StateNumbers AS (
    SELECT 
        state,
        CASE state
            WHEN 'Texas' THEN 1
            WHEN 'Florida' THEN 2
            ELSE ROW_NUMBER() OVER (ORDER BY state) + 2 -- 其他州从3开始编号
        END AS StateNum
    FROM your_table
    GROUP BY state
)
UPDATE t
SET t.StateNum = sn.StateNum
FROM your_table t
JOIN StateNumbers sn ON t.state = sn.state;

方法2：MySQL专属更新写法 #

MySQL的UPDATE JOIN语法略有不同，直接用子查询生成编号再关联即可：

UPDATE your_table t
JOIN (
    SELECT 
        state,
        ROW_NUMBER() OVER (ORDER BY state) AS StateNum
    FROM your_table
    GROUP BY state
) sn ON t.state = sn.state
SET t.StateNum = sn.StateNum;

同样，如果要手动指定特定州的编号，加入CASE语句就行：

UPDATE your_table t
JOIN (
    SELECT 
        state,
        CASE state
            WHEN 'Texas' THEN 1
            WHEN 'Florida' THEN 2
            ELSE ROW_NUMBER() OVER (ORDER BY state) + 2
        END AS StateNum
    FROM your_table
    GROUP BY state
) sn ON t.state = sn.state
SET t.StateNum = sn.StateNum;

兼容老版本数据库的写法（比如不支持ROW_NUMBER()的MySQL） #

如果你的数据库版本比较老，不支持窗口函数，可以用变量来生成编号：

SET @count = 0;
UPDATE your_table t
JOIN (
    SELECT state, @count := @count + 1 AS StateNum
    FROM your_table
    GROUP BY state
    ORDER BY state
) sn ON t.state = sn.state
SET t.StateNum = sn.StateNum;

注意事项 #

• 记得把代码里的your_table替换成你实际使用的表名
• 如果州的名称有大小写差异，建议先统一格式（比如转成大写/小写）再分组，避免重复编号

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