如何在SQL中更新产品表PLU_code空缺值（填补间隙且保留原值）

以下针对不同数据库提供仅更新操作的实现方案，完全保留现有非0的PLU_code值，自动填补序列空缺：

MySQL 8.0+（支持CTE） #

WITH RECURSIVE plu_range AS (
    -- 生成从1到当前最大PLU的所有整数
    SELECT 1 AS plu
    UNION ALL
    SELECT plu + 1 FROM plu_range
    WHERE plu < (SELECT MAX(PLU_code) FROM 产品表)
),
missing_plu AS (
    -- 筛选出未被使用的空缺PLU
    SELECT pr.plu
    FROM plu_range pr
    LEFT JOIN 产品表 p ON pr.plu = p.PLU_code
    WHERE p.PLU_code IS NULL
    ORDER BY pr.plu
),
zero_plu AS (
    -- 给PLU为0的记录按ID排序并编号
    SELECT ID,
           ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM 产品表
    WHERE PLU_code = 0
)
-- 按顺序分配空缺PLU到目标记录
UPDATE 产品表 p
JOIN (
    SELECT zp.ID, mp.plu
    FROM zero_plu zp
    JOIN (
        SELECT plu, ROW_NUMBER() OVER (ORDER BY plu) AS rn
        FROM missing_plu
    ) mp ON zp.rn = mp.rn
) upd ON p.ID = upd.ID
SET p.PLU_code = upd.plu;

MySQL 5.x（不支持CTE） #

-- 创建临时表存储空缺PLU及序号
CREATE TEMPORARY TABLE temp_missing_plu (
    plu INT,
    rn INT AUTO_INCREMENT PRIMARY KEY
);

-- 插入1到当前最大PLU范围内的所有空缺值
INSERT INTO temp_missing_plu (plu)
SELECT pr.plu
FROM (
    -- 生成足够覆盖当前最大PLU的数值范围（这里覆盖到200，可按需调整）
    SELECT 1 + a.i + b.i*10 + c.i*100 AS plu
    FROM (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1) c
) pr
LEFT JOIN 产品表 p ON pr.plu = p.PLU_code
WHERE p.PLU_code IS NULL
AND pr.plu <= (SELECT MAX(PLU_code) FROM 产品表)
ORDER BY pr.plu;

-- 创建临时表存储PLU为0的记录及序号
CREATE TEMPORARY TABLE temp_zero_plu (
    ID INT,
    rn INT AUTO_INCREMENT PRIMARY KEY
);

INSERT INTO temp_zero_plu (ID)
SELECT ID FROM 产品表 WHERE PLU_code = 0 ORDER BY ID;

-- 执行更新分配
UPDATE 产品表 p
JOIN temp_zero_plu zp ON p.ID = zp.ID
JOIN temp_missing_plu mp ON zp.rn = mp.rn
SET p.PLU_code = mp.plu;

-- 清理临时表（可选）
DROP TEMPORARY TABLE temp_missing_plu;
DROP TEMPORARY TABLE temp_zero_plu;

SQL Server #

WITH plu_range AS (
    SELECT 1 AS plu
    UNION ALL
    SELECT plu + 1 FROM plu_range
    WHERE plu < (SELECT MAX(PLU_code) FROM 产品表)
),
missing_plu AS (
    SELECT pr.plu,
           ROW_NUMBER() OVER (ORDER BY pr.plu) AS rn
    FROM plu_range pr
    LEFT JOIN 产品表 p ON pr.plu = p.PLU_code
    WHERE p.PLU_code IS NULL
),
zero_plu AS (
    SELECT ID,
           ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM 产品表
    WHERE PLU_code = 0
)
UPDATE p
SET p.PLU_code = mp.plu
FROM 产品表 p
JOIN zero_plu zp ON p.ID = zp.ID
JOIN missing_plu mp ON zp.rn = mp.rn;

PostgreSQL #

WITH plu_range AS (
    -- 直接生成连续数值范围
    SELECT generate_series(1, (SELECT MAX(PLU_code) FROM 产品表)) AS plu
),
missing_plu AS (
    SELECT plu,
           ROW_NUMBER() OVER (ORDER BY plu) AS rn
    FROM plu_range pr
    LEFT JOIN 产品表 p ON pr.plu = p.PLU_code
    WHERE p.PLU_code IS NULL
),
zero_plu AS (
    SELECT ID,
           ROW_NUMBER() OVER (ORDER BY ID) AS rn
    FROM 产品表
    WHERE PLU_code = 0
)
UPDATE 产品表 p
SET PLU_code = mp.plu
FROM zero_plu zp
JOIN missing_plu mp ON zp.rn = mp.rn
WHERE p.ID = zp.ID;

注意事项 #

• 所有方案仅执行更新操作，不会修改或删除现有非0的PLU_code值，也无需重建表。
• 分配逻辑：空缺PLU按从小到大排序，PLU为0的记录按ID升序排序后一一对应分配。若空缺PLU数量不足（超出当前最大PLU的情况），可扩展plu_range的上限（比如设为MAX(PLU_code) + (SELECT COUNT(*) FROM 产品表 WHERE PLU_code=0)）。
• 建议先单独运行CTE/子查询部分（如missing_plu与zero_plu的关联查询），确认分配的PLU值符合预期后，再执行更新语句。

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