sql - 在更新语句中使用 over(partition by order by) 计算运行总和

Question

我正在尝试在给定升序折扣的情况下创建产品子组的总收入。

我使用了以下查询

Update Z_Discount_Tool1 Set Cum_Net_Revenue_Discount = 
(Select Sum([Umsatz_Netto]) Over (Partition by [WUGR_act] Order by [Discount] From Z_Discount_Tool1)

但是，我收到以下错误信息

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我在我的代码中找不到错误。所以任何帮助表示赞赏。提前致谢!

Answer 1

这看起来像 SQL Server。您可以使用可更新的 CTE:

with toupdate as (
      select dt.*,
             Sum([Umsatz_Netto]) Over (Partition by [WUGR_act] Order by [Discount]) as new_Cum_Net_Revenue_Discount
      from Z_Discount_Tool1 dt
     )
Update toupdate
     Set Cum_Net_Revenue_Discount = new_Cum_Net_Revenue_Discount;