PostgreSQL/Python实现：如何遍历表记录生成序列编号？

核心是利用窗口函数按FieldA和FieldB的组合分组，为每组内的记录生成递增序号，再拼接固定前缀"05"并格式化为四位带前导零的字符串。

MySQL（8.0+ 版本） #

如果表有主键（比如id），优先用主键关联确保更新准确：

WITH ranked_records AS (
    SELECT 
        id,
        CONCAT('05', LPAD(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY id), 2, '0')) AS new_unique_id
    FROM your_table_name
)
UPDATE your_table_name t
JOIN ranked_records r ON t.id = r.id
SET t.UniqueId = r.new_unique_id;

若无主键，可通过字段组合关联（仅当组合内无完全重复记录时使用）：

WITH ranked_records AS (
    SELECT 
        FieldA,
        FieldB,
        CONCAT('05', LPAD(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY (SELECT NULL)), 2, '0')) AS new_unique_id
    FROM your_table_name
)
UPDATE your_table_name t
JOIN ranked_records r ON t.FieldA = r.FieldA AND t.FieldB = r.FieldB
SET t.UniqueId = r.new_unique_id;

SQL Server #

使用CTE直接更新，FORMAT函数简化格式处理：

WITH ranked_records AS (
    SELECT 
        UniqueId,
        CONCAT('05', FORMAT(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY id), '00')) AS new_unique_id
    FROM your_table_name
)
UPDATE ranked_records
SET UniqueId = new_unique_id;

若版本不支持FORMAT，可用RIGHT函数补零：

WITH ranked_records AS (
    SELECT 
        UniqueId,
        '05' + RIGHT('00' + CAST(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY id) AS VARCHAR(2)), 2) AS new_unique_id
    FROM your_table_name
)
UPDATE ranked_records
SET UniqueId = new_unique_id;

PostgreSQL #

通过主键关联更新，用LPAD或TO_CHAR实现格式转换：

WITH ranked_records AS (
    SELECT 
        id,
        CONCAT('05', LPAD(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY id)::TEXT, 2, '0')) AS new_unique_id
    FROM your_table_name
)
UPDATE your_table_name t
SET UniqueId = r.new_unique_id
FROM ranked_records r
WHERE t.id = r.id;

或用TO_CHAR更简洁：

WITH ranked_records AS (
    SELECT 
        id,
        '05' || TO_CHAR(ROW_NUMBER() OVER (PARTITION BY FieldA, FieldB ORDER BY id), 'FM00') AS new_unique_id
    FROM your_table_name
)
UPDATE your_table_name t
SET UniqueId = r.new_unique_id
FROM ranked_records r
WHERE t.id = r.id;

注意事项 #

• 字段类型：确保UniqueId是字符串类型（如VARCHAR(4)），否则前导零会被自动去除。
• 排序稳定性：ORDER BY尽量使用主键、创建时间等有序字段，避免(SELECT NULL)导致序号顺序不确定。
• 大数据量：如果表中数据量极大，建议分批次更新，避免长时间锁表影响业务。

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