sql - 用累积结果更新表记录

Question

假设我有一个表 Tbl(代表针对不同客户所做工作的简单时间日志)

五列编号:整数时间使用: float IdCustomer:整数创建时间:日期时间时间计算器: float

我在这个表中有很多条记录，(TimeCalc初始化为value = 0)

我希望我的 SQL 执行的操作是:

当特定客户的所有上述记录的 TimeUse 累积到一个值 < 10 时，TimeCalc 中的值应为 0

当针对特定客户的所有上述记录的 TimeUse 累积到一个值 >= 10 时，TimeCalc 中的值应为记录的 TimeUse...

我搞砸了带有子查询的 Case 例程，但无法让它工作。

BEFORE
    Id        TimeUse       IdCustomer        Created        TimeCalc
    1         2             1                 14/09/09       0
    2         5             2                 14/09/10       0
    3         2             1                 14/09/11       0
    4         5             2                 14/09/12       0
    5         4             1                 14/09/13       0
    6         2             2                 14/09/14       0
    7         4             1                 14/09/15       0
    8         1             1                 14/09/16       0
    9         3             2                 14/09/17       0
    10        2             1                 14/09/18       0
    11        4             2                 14/09/19       0



AFTER
    Id        TimeUse       IdCustomer        Created        TimeCalc
    1         2             1                 14/09/09       0
    2         5             2                 14/09/10       0
    3         2             1                 14/09/11       0
    4         5             2                 14/09/12       0
    5         4             1                 14/09/13       0
    6         2             2                 14/09/14       2
    7         4             1                 14/09/15       0
    8         1             1                 14/09/16       1
    9         3             2                 14/09/17       3 
    10        2             1                 14/09/18       2
    11        4             2                 14/09/19       4

这可以在 SQL 更新中解决吗？

Answer 1

在 SQL Server 2012+ 中，您可以使用累计和来执行此操作:

select Id, TimeUse, IdCustomer, Created,
       (case when sum(timeuse) over (partition by idcustomer order by id) < 10 then 0
             else timeuse
        end) as timecalc
from table t;

您可以在早期版本中使用 outer apply 或子查询来做同样的事情。

如果您想要更新，只需使用 CTE:

with toupdate as (
      select t.*,
             (case when sum(timeuse) over (partition by idcustomer order by id) < 10 then 0
                   else timeuse
              end) as new_timecalc
      from table t
     )
update toupdate
    set timecalc = new_timecalc;

编辑:

以下内容适用于任何版本的 SQL Server:

with toupdate as (
      select t.*,
             (case when (select sum(t2.timeuse)
                         from table t2
                         where t2.idcustomer = t.idcustomer and
                               t2.id <= t.id
                        ) < 10 then 0
                   else timeuse
              end) as new_timecalc
      from table t
     )
update toupdate
    set timecalc = new_timecalc;