如何用SQL按batch_id和tmr_id分组更新sequence列连续值

核心思路 #

通过窗口函数DENSE_RANK()按batch_id分组，基于每个tmr_id在对应批次中的首次出现顺序分配连续序号，再关联原表更新sequence列，确保同一batch_id下相同tmr_id的所有记录序号一致。

不同数据库实现示例 #

MySQL 8.0+

UPDATE your_table t
JOIN (
    SELECT 
        batch_id,
        tmr_id,
        DENSE_RANK() OVER (PARTITION BY batch_id ORDER BY MIN(created_at)) AS seq
    FROM your_table
    GROUP BY batch_id, tmr_id
) r ON t.batch_id = r.batch_id AND t.tmr_id = r.tmr_id
SET t.sequence = r.seq;

PostgreSQL

WITH ranked_tmrs AS (
    SELECT 
        batch_id,
        tmr_id,
        DENSE_RANK() OVER (PARTITION BY batch_id ORDER BY MIN(created_at)) AS seq
    FROM your_table
    GROUP BY batch_id, tmr_id
)
UPDATE your_table t
SET sequence = r.seq
FROM ranked_tmrs r
WHERE t.batch_id = r.batch_id AND t.tmr_id = r.tmr_id;

SQL Server

WITH ranked_tmrs AS (
    SELECT 
        batch_id,
        tmr_id,
        DENSE_RANK() OVER (PARTITION BY batch_id ORDER BY MIN(created_at)) AS seq
    FROM your_table
    GROUP BY batch_id, tmr_id
)
UPDATE t
SET t.sequence = r.seq
FROM your_table t
INNER JOIN ranked_tmrs r ON t.batch_id = r.batch_id AND t.tmr_id = r.tmr_id;

关键说明 #

• 替换your_table为实际表名，created_at为记录的创建时间字段；若没有时间字段，可用自增主键id的MIN(id)来判断tmr_id的首次出现顺序
• DENSE_RANK()保证序号连续递增，不会出现断号，同一batch_id下相同tmr_id的所有记录会被分配相同序号
• 若每个batch_id下的tmr_id种类不超过4，自动满足序号不超过4的要求

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