MySQL按user_id和symbol分组求和更新到另一表的SQL问题求解

问题原因 #

你的SQL无法运行的核心问题是：SUM()属于聚合函数，需要先对records表按user_id、symbol分组后才能计算每个分组的金额总和，无法直接在UPDATE的SET子句中未分组的场景下调用。

解决方案 #

通用写法（兼容MySQL、PostgreSQL、SQL Server等绝大多数数据库） #

通过关联子查询先计算对应分组的求和值再更新：

UPDATE calculation c
SET sumOfAmount = (
    SELECT SUM(amount) 
    FROM records r
    WHERE r.user_id = c.user_id 
      AND r.symbol = c.symbol
)
-- 可选：加下面的条件避免records中无匹配的记录被更新为NULL
WHERE EXISTS (
    SELECT 1 FROM records r 
    WHERE r.user_id = c.user_id AND r.symbol = c.symbol
);

MySQL专属优化写法 #

通过预聚合的临时表关联更新，执行效率更高：

UPDATE calculation c
INNER JOIN (
    SELECT user_id, symbol, SUM(amount) AS total_amount
    FROM records
    GROUP BY user_id, symbol
) r ON c.user_id = r.user_id AND c.symbol = r.symbol
SET c.sumOfAmount = r.total_amount;

新增+更新二合一写法（仅MySQL支持） #

如果calculation表可能不存在对应user_id+symbol的记录，需要同时处理新增和更新场景，先给calculation表的user_id和symbol字段加联合唯一索引，再用以下语句：

INSERT INTO calculation (user_id, symbol, sumOfAmount)
SELECT user_id, symbol, SUM(amount)
FROM records
GROUP BY user_id, symbol
ON DUPLICATE KEY UPDATE sumOfAmount = VALUES(sumOfAmount);

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