PostgreSQL按ID聚合求和更新表并删除重复行问题求助

咱们先从你测试表A的问题说起，你之前写的update a set amount=sum(amount) from a group by id;之所以报错，是因为SQL语法不允许直接在UPDATE的SET子句里使用聚合函数，同时GROUP BY也不能直接这样关联原表——聚合计算得先单独处理好，再关联更新。

下面分场景给你正确的实现方法，先讲测试表的处理，再延伸到你的业务表ht：

一、测试表A的处理（按id求和amount并去重） #

方法1：直接生成新表（最简便，适合允许新建表的场景） #

如果不需要保留原表的结构历史，直接用聚合查询生成结果表：

SELECT id, SUM(amount) AS amount
INTO new_table_A  -- 生成新表
FROM A
GROUP BY id;

执行后new_table_A就是你要的结果：每个id一行，amount为对应总和。

方法2：更新原表并删除重复行（需保留原表时用） #

如果必须在原表上修改，分两步：先把总和更新到每组的其中一行，再删除其他重复行。

PostgreSQL版本：

-- 第一步：计算每个id的总和，同时标记每组要保留的行（这里选每组第一行，按id排序）
WITH sum_cte AS (
    SELECT id, SUM(amount) AS total_amount
    FROM A
    GROUP BY id
),
rank_cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
    FROM A
)
-- 更新保留行的amount为总和
UPDATE rank_cte
SET amount = (SELECT total_amount FROM sum_cte WHERE sum_cte.id = rank_cte.id)
WHERE rn = 1;

-- 第二步：删除重复行（只保留rn=1的行）
DELETE FROM A
WHERE id IN (
    SELECT id
    FROM rank_cte
    WHERE rn > 1
);

MySQL版本：

MySQL不支持直接在UPDATE里用窗口函数，用临时表来处理更稳妥：

-- 1. 临时表存每个id的总和
CREATE TEMPORARY TABLE temp_sum AS
SELECT id, SUM(amount) AS amount
FROM A
GROUP BY id;

-- 2. 清空原表
TRUNCATE TABLE A;

-- 3. 把汇总数据插回原表
INSERT INTO A (id, amount)
SELECT id, amount FROM temp_sum;

-- 可选：删除临时表
DROP TEMPORARY TABLE temp_sum;

二、业务表ht的处理（按subject_id求和hostay并去重） #

你的业务表有row_id（应该是唯一标识）、subject_id、hostay，需求是按subject_id求和hostay，保留每组一行并更新，删除其他重复行。

PostgreSQL版本： #

WITH sum_cte AS (
    -- 计算每个subject_id的hostay总和
    SELECT subject_id, SUM(hostay) AS total_hostay
    FROM ht
    GROUP BY subject_id
),
rank_cte AS (
    -- 给每组subject_id的行排序，标记要保留的行（这里选row_id最小的行）
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY subject_id ORDER BY row_id) AS rn
    FROM ht
)
-- 更新保留行的hostay为总和
UPDATE rank_cte
SET hostay = (SELECT total_hostay FROM sum_cte WHERE sum_cte.subject_id = rank_cte.subject_id)
WHERE rn = 1;

-- 删除重复行（只保留rn=1的行）
DELETE FROM ht
WHERE row_id IN (
    SELECT row_id
    FROM rank_cte
    WHERE rn > 1
);

MySQL版本： #

-- 1. 临时表存每个subject_id的总和
CREATE TEMPORARY TABLE temp_ht_sum AS
SELECT subject_id, SUM(hostay) AS total_hostay
FROM ht
GROUP BY subject_id;

-- 2. 临时表标记每组要保留的行（row_id最小的）
CREATE TEMPORARY TABLE temp_keep_rows AS
SELECT MIN(row_id) AS keep_row_id, subject_id
FROM ht
GROUP BY subject_id;

-- 3. 更新保留行的hostay为总和
UPDATE ht
JOIN temp_ht_sum ON ht.subject_id = temp_ht_sum.subject_id
JOIN temp_keep_rows ON ht.row_id = temp_keep_rows.keep_row_id
SET ht.hostay = temp_ht_sum.total_hostay;

-- 4. 删除不保留的行
DELETE ht
FROM ht
LEFT JOIN temp_keep_rows ON ht.row_id = temp_keep_rows.keep_row_id
WHERE temp_keep_rows.keep_row_id IS NULL;

-- 可选：删除临时表
DROP TEMPORARY TABLE temp_ht_sum;
DROP TEMPORARY TABLE temp_keep_rows;

补充说明 #

你之前尝试的WITH cte AS (DELETE FROM ht ret...)应该是想用到PostgreSQL的RETURNING语法，但这种方式更适合删除后返回数据，而你的需求是先更新再删除，所以得先计算总和并更新保留行，再清理重复行。

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