php - SQL SUM 通过 SUM 进行分组

Question

我有这个结构表:

USER_ID | POINTS_BALANCE

10     |  180          
10     |  20               
10     |  100                 
10     |  120  

如何将所有这些用户的 points_balance 字段更新为 500:

select user_id, sum(points_balance)
from `user_points_table`
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499

或者我可以为user_id插入一个新的行，sum(points_balance)在400到499之间吗？

编辑:

例如:

select user_id, sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499

结果:

USER_ID | POINTS_BALANCE
74      |  434 <-- How can I update this result to 500?

Answer 1

由于您想向表中添加新行，一种选择是使用 insert into select:

insert into user_points_table 
select user_id, 500-sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499;

• SQL Fiddle Demo