weixin_45319463的博客

-- 创建临时表
CREATE TABLE t (
    productcode VARCHAR(50),
    seq VARCHAR(50)
);

-- 假设已有一些数据
INSERT INTO t (productcode, seq)
VALUES ('A', '2'), ('A', '3'), ('B', '4'), ('B', '5'), ('B', '6'), ('C', '7');

-- 使用 ROW_NUMBER() 函数来设置序列号
WITH cte AS (
    SELECT
        productcode,
        seq,
        ROW_NUMBER() OVER (PARTITION BY productcode ORDER BY productcode) AS row_num
    FROM
        t
)

-- 使用自连接来更新 seq 字段
UPDATE cte
SET seq = '1'
FROM cte
LEFT JOIN cte AS prev ON cte.productcode = prev.productcode AND cte.row_num = prev.row_num + 1
WHERE prev.productcode IS NULL OR cte.productcode <> prev.productcode;

-- 查询更新后的结果
SELECT * FROM t;