为现有SQL表添加分组重置的Seq#列，按MyCoIt#变化自动重置计数

你原先使用全局变量的方式只能实现全局连续计数，无法识别分组变化重置计数。你可以使用SQL标准的窗口函数ROW_NUMBER()配合PARTITION BY分组语法实现需求，无需额外关联商品主表就能完成，如有排序规则需要用到主表字段可自行关联。

实现代码（T-SQL） #

WITH numbered_cte AS (
    SELECT
        [MyCoIt#],
        [CustsXRefItem#],
        [seqn],
        -- 按我司商品编号分组，每组内按客户侧商品编号排序生成序号，格式化为3位补前导零
        FORMAT(ROW_NUMBER() OVER (PARTITION BY [MyCoIt#] ORDER BY [CustsXRefItem#]), '000') AS calculated_seq
    FROM [stg].[ct_ItemAlias_counter]
)
UPDATE numbered_cte
SET [seqn] = calculated_seq;

兼容老版本SQL的写法 #

如果你的数据库版本不支持FORMAT函数，可以改用字符串拼接补零的方式实现：

WITH numbered_cte AS (
    SELECT
        [MyCoIt#],
        [CustsXRefItem#],
        [seqn],
        RIGHT('000' + CAST(ROW_NUMBER() OVER (PARTITION BY [MyCoIt#] ORDER BY [CustsXRefItem#]) AS VARCHAR(3)), 3) AS calculated_seq
    FROM [stg].[ct_ItemAlias_counter]
)
UPDATE numbered_cte
SET [seqn] = calculated_seq;

规则调整说明 #

• 如果你需要同个我司商品编号下的序号按其他规则排序（比如数据入库时间、客户ID等），替换ORDER BY后的字段即可
• 如果需要关联商品主表获取排序字段，直接在CTE的FROM后加JOIN关联对应主表即可
• 该写法为标准SQL语法，在PostgreSQL、MySQL 8.0+、SQL Server等主流数据库都可以适配，只需微调格式函数即可

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