PostgreSQL中合并相同item_id的行并删除重复行的单事务实现方案咨询

嗨，我来帮你梳理下这个问题～你现在需要处理的是单表内item_id重复的行，要把相同item_id的qty求和后保留一行、删除其他行，还希望所有操作在单个事务里完成，对吧？

先明确你的场景：

• 表结构包含id、item_id、qty三列
• 目标：对每个item_id求和qty，更新其中一行的数量为总和，删除同item_id的其他行
• 需求：所有操作原子性完成（单事务），你试过MERGE但没找到合适的用法，还考虑过封装成过程/函数

原始数据集：

你之前参考的分步操作大概是这样的（来自外部方案）：

UPDATE test t
SET qty = s.qty
...

直接的单事务实现方案 #

其实不用复杂的存储过程，用PostgreSQL的CTE（公共表表达式）配合事务块就能搞定，所有操作都在一个事务里，确保要么全部成功要么全部回滚。核心思路是先算每个item_id的总数量，更新到你想保留的那一行（比如保留id最小的行），再删除其他重复行：

BEGIN;

-- 第一步：计算每个item_id的总qty，更新到对应保留行（这里选id最小的行）
WITH item_totals AS (
    SELECT item_id, SUM(qty) AS total_qty
    FROM test
    GROUP BY item_id
)
UPDATE test t
SET qty = it.total_qty
FROM item_totals it
WHERE t.item_id = it.item_id
AND t.id = (SELECT MIN(id) FROM test WHERE item_id = it.item_id);

-- 第二步：删除每个item_id中除保留行外的其他行
DELETE FROM test t
WHERE EXISTS (
    SELECT 1
    FROM test t2
    WHERE t2.item_id = t.item_id
    AND t2.id < t.id -- 这里的逻辑是保留id最小的行，如果你想保留最大id的话改成t2.id > t.id即可
);

COMMIT;

关于MERGE的小说明 #

你提到的MERGE语法确实更适合跨表的合并场景（比如把A表的数据合并到B表，做更新或插入），单表内的这种合并删除用上面的方式会更直接，没必要硬套MERGE。

可选：封装成存储过程（方便复用） #

如果这个逻辑需要重复执行，把它封装成存储过程会更方便，调用起来更简单，而且过程里的操作默认也是在一个事务里执行的：

CREATE OR REPLACE FUNCTION merge_duplicate_items()
RETURNS VOID AS $$
BEGIN
    WITH item_totals AS (
        SELECT item_id, SUM(qty) AS total_qty
        FROM test
        GROUP BY item_id
    )
    UPDATE test t
    SET qty = it.total_qty
    FROM item_totals it
    WHERE t.item_id = it.item_id
    AND t.id = (SELECT MIN(id) FROM test WHERE item_id = it.item_id);

    DELETE FROM test t
    WHERE EXISTS (
        SELECT 1
        FROM test t2
        WHERE t2.item_id = t.item_id
        AND t2.id < t.id
    );
END;
$$ LANGUAGE plpgsql;

调用的时候只需要执行SELECT merge_duplicate_items();就可以啦。

备注：内容来源于stack exchange，提问作者cxp cxd