通过使用公用表表达式，可以使用以下查询。获取每个comp_id的存款总额。然后将此新表（称为total）联接到comp_id的存款。从总和中减去50。

WITH total as(
select  comp_id, 
        sum(deposit_amount) as total
from 
        deposit 
group by 
        comp_id
  )
select dep.dep_id, 
       ttl.total - 50 as deposit_amount, 
       dep.comp_id
from
    deposit dep
inner join
    total  ttl
on 
   dep.comp_id = ttl.comp_id

样品：
| 部门标识|存款_金额|组件标识|
| - -|- -|- -|
| 一个|二百五十|一个|
| 2个|二百五十|一个|
| 三个|二百五十|一个|

您应该在单独的查询中计算存款金额，然后根据匹配的“comp_id”值重新连接两个表

WITH cte AS (
    SELECT DISTINCT comp_id,
                    SUM(deposit_amount) OVER(PARTITION BY comp_id) AS amount
    FROM deposit
)
UPDATE deposit
INNER JOIN cte
        ON deposit.comp_id = cte.comp_id
SET deposit_amount = cte.amount - 50
WHERE deposit.comp_id = :comp_id;

在您的最终查询中，它应该如下所示：

query = em.createNativeQuery("WITH cte AS (SELECT DISTINCT comp_id, SUM(deposit_amount) OVER(PARTITION BY comp_id) AS amount FROM deposit) UPDATE deposit INNER JOIN cte ON deposit.comp_id = cte.comp_id SET deposit_amount = cte.amount - 50 WHERE deposit.comp_id = :comp_id");

query.setParameter("comp_id", comp_id);

检查演示here。