如何在SQL中基于Col1的序列模式批量更新Col2列值？

核心思路是利用自增id的有序性，以col1=1作为每组的起始标记，通过累计计数生成递增的组号col2。以下是针对不同数据库的高效实现方案，均无需固定脚本，可直接处理2万+条数据：

支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等） #

这类数据库可以通过窗口函数的累计求和功能，一次性计算出所有行的组号：

MySQL 8.0+ #

UPDATE test t
JOIN (
    SELECT 
        id,
        SUM(CASE WHEN col1 = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_num
    FROM test
) AS sub ON t.id = sub.id
SET t.col2 = sub.group_num;

PostgreSQL #

UPDATE test
SET col2 = sub.group_num
FROM (
    SELECT 
        id,
        SUM(CASE WHEN col1 = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_num
    FROM test
) AS sub
WHERE test.id = sub.id;

SQL Server #

WITH sub AS (
    SELECT 
        id,
        SUM(CASE WHEN col1 = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS group_num
    FROM test
)
UPDATE test
SET col2 = sub.group_num
FROM test
JOIN sub ON test.id = sub.id;

逻辑说明：通过SUM() OVER (ORDER BY id)按自增id顺序累计计数，每遇到col1=1就将累计值加1，后续行继承当前累计值，自动生成连续递增的组号。这种集合式操作效率极高，适合大规模数据。

不支持窗口函数的数据库（如MySQL 5.x） #

可通过用户变量跟踪组号，实现分组标记：

SET @group_num = 0;
SET @prev_col1 = 0;

UPDATE test
SET col2 = CASE 
    WHEN col1 = 1 THEN @group_num := @group_num + 1
    ELSE @group_num
END
ORDER BY id;

逻辑说明：初始化组号变量，按id排序遍历数据，遇到col1=1时递增组号，否则沿用当前组号，确保同一序列组的col2值一致。

验证建议 #

执行更新前，先通过SELECT语句验证组号是否正确：

SELECT id, col1, 
       SUM(CASE WHEN col1 = 1 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_num
FROM test
ORDER BY id;

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