sql - 如何使用 Group by 和 Sum 更新表

Question

数据表

|id | sysid |

|1  |  938  |

|2  |  938  | 

|3  |   23  | 

这就是我想要的更新表

|id|amount|sum_amount|
|1 |200| 200+400     |
|2 |400|  400+200    |
|3 |150| 150         |

查询:-

UPDATE  update_table
SET sum_amount = SUM(amount)
WHERE data_table.id = update_table.id
GROUP BY data_table.sysid;

错误是“Group”处或附近的语法错误

Answer 1

一个选项是在子查询中连接那些包含 sum(amount) 聚合的表，并通过公共(public)表表达式将结果反射(reflect)到更新语句中:

with d as
( 
 select * from
  (
  select d.sysid, sum(u.amount)   
    from data_table d
    join update_table u on u.id = d.id
   group by d.sysid
  ) q1
  right join data_table q2
  on q1.sysid=q2.sysid
)
update update_table u
   set sum_amount = d.sum
  from d
 where u.id = d.id
returning u.*;

id  amount  sum_amount
1   200     600
2   400     600
3   150     150

Demo