按code分区实现calcId自动递增的SQL问题排查与解决

问题根源 #

原代码未针对code分组单独计算已有最大calcId，且未给同组内的新记录分配连续序号，导致所有新插入的同code记录calcId均为同一值。

修正方案（SQL Server 环境） #

方法一：关联子查询 + 窗口函数 #

INSERT INTO #tBookPrice (code, calcId, otherColumns)
SELECT 
    nd.code,
    -- 先取对应code的已有最大calcId，无数据则从0开始，再加上组内序号
    ISNULL(tbp.max_calcId, 0) + ROW_NUMBER() OVER (PARTITION BY nd.code ORDER BY (SELECT NULL)) AS calcId,
    nd.otherColumns
FROM NewDataTable nd
-- 预查询每个code的当前最大calcId
LEFT JOIN (
    SELECT code, MAX(calcId) AS max_calcId
    FROM #tBookPrice
    GROUP BY code
) tbp ON nd.code = tbp.code

方法二：MERGE 语句（适合更严谨的场景） #

如果需要避免插入过程中其他操作干扰（临时表场景一般无需考虑，但永久表可参考），可以用MERGE：

MERGE INTO #tBookPrice tbp
USING (
    -- 先给待插入数据按code分组编序号
    SELECT 
        code,
        otherColumns,
        ROW_NUMBER() OVER (PARTITION BY code ORDER BY (SELECT NULL)) AS rn
    FROM NewDataTable
) nd ON 1=0 -- 强制仅执行插入逻辑
WHEN NOT MATCHED THEN
INSERT (code, calcId, otherColumns)
VALUES (
    nd.code,
    -- 针对每条记录，单独取对应code的当前最大calcId并加上序号
    (SELECT ISNULL(MAX(calcId), 0) FROM #tBookPrice WHERE code = nd.code) + nd.rn,
    nd.otherColumns
);

说明 #

• ROW_NUMBER() OVER (PARTITION BY nd.code ORDER BY (SELECT NULL)) 用于在同一code分组内给新记录分配连续的序号（1、2、3...）
• ISNULL(tbp.max_calcId, 0) 处理该code无历史数据的情况，此时从1开始递增
• 如果需要按特定顺序分配calcId，可将ORDER BY (SELECT NULL)替换为实际排序字段（如create_time）

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