mysql - 如何在同一个表上按 user_id 求和(金额)和分组

Question

我有一个表，每个 user_id 有很多行

我正在尝试按 user_id 对行进行分组并对它们的金额进行求和

这是表结构

Name    Type    Collation   Attributes  Null    Default Extra   Action
 1  user_id int(11)         No          None         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 2  amount  decimal(16,8)   No          None         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 3  aff int(11)             No             0         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 4  jackpot int(11)         No             0         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 5  paidout int(11)         No             0         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 6  type    int(11)         No             0         Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More
 7  created timestamp       No   CURRENT_TIMESTAMP   Change Change   Drop Drop   Browse distinct values Browse distinct values   Primary Primary     Unique Unique  Show more actions More

我正在尝试此查询但没有成功:

update trans SELECT * FROM trans group by user_id set amount = (select sum(amount) from trans

如有任何帮助，我们将不胜感激

Answer 1

你可以这样做:

UPDATE trans t
    INNER JOIN (
        select user_id, sum(amount) sumAmount
        from trans
        group by user_id
    ) subSum on subSum.user_id = t.user_id
SET t.amount = subSum.sumAmount

用户 ID 范围:

UPDATE trans t
    INNER JOIN (
        select user_id, sum(amount) sumAmount
        from trans
        where user_id BETWEEN 0 AND 1000 --Edited
        group by user_id
    ) subSum on subSum.user_id = t.user_id
SET t.amount = subSum.sumAmount
WHERE t.user_id BETWEEN 0 AND 1000 --HEre

使用临时表

使用临时表:

--Create table with user_id and sum amount
CREATE TABLE trans_temp_sum_amount 
SELECT user_id, sum(amount) sumAmount
FROM trans
GROUP BY user_id;

--Update
UPDATE trans t
    INNER JOIN trans_temp_sum_amount subSum 
        on subSum.user_id = t.user_id
SET t.amount = subSum.sumAmount;

--Drop temp table 
DROP TABLE trans_temp_sum_amount;