sql - 不能使用 SQL 滞后总和

Question

我的查询是这样的

SELECT
    [day], [time],AvaliableTimes,
    CASE
       WHEN AvaliableTimes > 0 
          THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
          ELSE 0 
    END AS SumValue
FROM
    [AvailableTimes] 
WHERE
    [day] = 1 AND BranchAreaId = 1
ORDER BY
    [day], [time], AvaliableTimes

如果值为 null 或 0，我想从 0 开始求和。

结果：

标签： sqlsql-serversumlag

Answer 1

您可以使用递归 CTE 来执行此操作。执行 rcte 中的累积和，如果 AvailableTimes = 0，则重置它

; with
cte as
(
    select  *, rn = row_number() over (order by time)
    from    yourtable   
),
rcte as
(
    select  *, sumvalues = AvailableTimes
    from    cte
    where   rn = 1

    union all

    select  c.*, sumvalues = case when  c.AvailableTimes <> 0
                      then  r.sumvalues + c.AvailableTimes
                      else  c.AvailableTimes
                      end
    from    cte c
        inner join rcte r   on  c.rn    = r.rn + 1
)
select  day, time, AvailableTimes, sumvalues
from    rcte
order by time