如何简化SQL表中重复offer_id字段的批量更新修复逻辑

方案一：单条UPDATE结合CTE（与原逻辑完全对齐） #

原逻辑是按顺序检查每个offer字段是否重复前面的字段，一旦发现就从该位置开始移位覆盖。可以用CTE先定位每行第一个重复的位置，再一次性完成所有更新：

WITH row_dup_info AS (
    SELECT 
        *,
        CASE
            WHEN offer_id_02 = offer_id_01 THEN 2
            WHEN offer_id_03 IN (offer_id_01, offer_id_02) THEN 3
            WHEN offer_id_04 IN (offer_id_01, offer_id_02, offer_id_03) THEN 4
            WHEN offer_id_05 IN (offer_id_01, offer_id_02, offer_id_03, offer_id_04) THEN 5
            WHEN offer_id_06 IN (offer_id_01, offer_id_02, offer_id_03, offer_id_04, offer_id_05) THEN 6
            WHEN offer_id_07 IN (offer_id_01, offer_id_02, offer_id_03, offer_id_04, offer_id_05, offer_id_06) THEN 7
            WHEN offer_id_08 IN (offer_id_01, offer_id_02, offer_id_03, offer_id_04, offer_id_05, offer_id_06, offer_id_07) THEN 8
            ELSE 0
        END AS first_duplicate_pos
    FROM tble
)
UPDATE tble
SET
    offer_id_02 = CASE WHEN first_duplicate_pos <= 2 THEN offer_id_03 ELSE offer_id_02 END,
    offer_id_03 = CASE WHEN first_duplicate_pos <= 3 THEN offer_id_04 ELSE offer_id_03 END,
    offer_id_04 = CASE WHEN first_duplicate_pos <= 4 THEN offer_id_05 ELSE offer_id_04 END,
    offer_id_05 = CASE WHEN first_duplicate_pos <= 5 THEN offer_id_06 ELSE offer_id_05 END,
    offer_id_06 = CASE WHEN first_duplicate_pos <= 6 THEN offer_id_07 ELSE offer_id_06 END,
    offer_id_07 = CASE WHEN first_duplicate_pos <= 7 THEN offer_id_08 ELSE offer_id_07 END,
    offer_id_08 = CASE WHEN first_duplicate_pos <= 8 THEN NULL ELSE offer_id_08 END
FROM row_dup_info
WHERE row_dup_info.first_duplicate_pos > 0
  AND tble.customer_id = row_dup_info.customer_id; -- 替换为你的表主键字段，比如客户ID

说明：

• CTE先找出每行第一个出现重复的offer位置，无重复则标记为0
• UPDATE时根据该位置，对从该位置开始的字段执行移位覆盖，和原7条语句逻辑完全一致
• 仅需执行一次UPDATE，避免多次扫描表的性能开销

方案二：行列转换彻底去重重排（更简洁的业务实现） #

如果核心需求是去除所有重复offer、保留原始顺序、多余位置自动置空，可以用行列转换一次性完成，比原逻辑更彻底：

适用于SQL Server/Oracle（支持UNPIVOT/PIVOT）

WITH unpivoted_offers AS (
    SELECT 
        customer_id, -- 主键字段
        offer_num,
        offer_id
    FROM tble
    UNPIVOT (
        offer_id FOR offer_num IN (offer_id_01, offer_id_02, offer_id_03, offer_id_04, offer_id_05, offer_id_06, offer_id_07, offer_id_08)
    ) up
),
deduped AS (
    SELECT 
        customer_id,
        offer_num,
        offer_id,
        ROW_NUMBER() OVER (PARTITION BY customer_id, offer_id ORDER BY offer_num) AS occurrence
    FROM unpivoted_offers
),
reordered_offers AS (
    SELECT 
        customer_id,
        'offer_id_' + RIGHT('0' + CAST(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY offer_num) AS VARCHAR(2)), 2) AS new_offer_num,
        offer_id
    FROM deduped
    WHERE occurrence = 1 -- 只保留首次出现的offer
),
pivoted_back AS (
    SELECT 
        customer_id,
        [offer_id_01], [offer_id_02], [offer_id_03], [offer_id_04],
        [offer_id_05], [offer_id_06], [offer_id_07], [offer_id_08]
    FROM reordered_offers
    PIVOT (
        MAX(offer_id) FOR new_offer_num IN ([offer_id_01], [offer_id_02], [offer_id_03], [offer_id_04], [offer_id_05], [offer_id_06], [offer_id_07], [offer_id_08])
    ) pvt
)
UPDATE tble t
SET
    offer_id_01 = p.offer_id_01,
    offer_id_02 = p.offer_id_02,
    offer_id_03 = p.offer_id_03,
    offer_id_04 = p.offer_id_04,
    offer_id_05 = p.offer_id_05,
    offer_id_06 = p.offer_id_06,
    offer_id_07 = p.offer_id_07,
    offer_id_08 = p.offer_id_08
FROM pivoted_back p
WHERE t.customer_id = p.customer_id;

适用于PostgreSQL（用数组处理）

WITH offer_arrays AS (
    SELECT 
        customer_id,
        ARRAY[offer_id_01, offer_id_02, offer_id_03, offer_id_04, offer_id_05, offer_id_06, offer_id_07, offer_id_08] AS offers
    FROM tble
),
deduped_arrays AS (
    SELECT 
        customer_id,
        ARRAY(
            SELECT DISTINCT ON (offer) offer
            FROM UNNEST(offers) WITH ORDINALITY AS o(offer, pos)
            ORDER BY pos
        ) AS deduped_offers
    FROM offer_arrays
),
padded_arrays AS (
    SELECT 
        customer_id,
        deduped_offers || ARRAY[NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL]::text[8] AS padded_offers
    FROM deduped_arrays
)
UPDATE tble t
SET
    offer_id_01 = padded_offers[1],
    offer_id_02 = padded_offers[2],
    offer_id_03 = padded_offers[3],
    offer_id_04 = padded_offers[4],
    offer_id_05 = padded_offers[5],
    offer_id_06 = padded_offers[6],
    offer_id_07 = padded_offers[7],
    offer_id_08 = padded_offers[8]
FROM padded_arrays p
WHERE t.customer_id = p.customer_id;

说明：

• 先将列转行，去除重复offer（保留首次出现的），再重新按顺序转回列
• 自动处理所有重复情况，无需逐个字段判断
• 最终每个客户的offer唯一、按原始顺序排列，多余位置自动置空

注意事项 #

1. 所有方案需替换customer_id为你的表主键字段（用于关联原表和中间结果）
2. 执行前建议备份数据，或用SELECT验证中间结果是否符合预期
3. 不同数据库语法略有差异，可根据实际环境选择对应方案

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