本文介绍了SQL Server - 遇到 0 时重置的累积和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想对一列进行累积求和，但每当遇到 0 时重置聚合值
I would like to do a cumulative sum on a column, but reset the aggregated value whenever a 0 is encountered
这是我尝试做的一个例子:
Here is an example of what i try to do :
这个数据集:
pk price
1 10
2 15
3 0
4 10
5 5
给出这个:
pk price
1 10
2 25
3 0
4 10
5 15
推荐答案
在 SQL Server 2008 中，您受到严重限制，因为您无法使用分析函数.以下方法效率不高，但可以解决您的问题:
In SQL Server 2008, you are severely limited because you cannot use analytic functions. The following is not efficient, but it will solve your problem:
with tg as (
select t.*, g.grp
from t cross apply
(select count(*) as grp
from t t2
where t2.pk <= t.pk and t2.pk = 0
) g
)
select tg.*, p.running_price
from tg cross apply
(select sum(tg2.price) as running_price
from tg tg2
where tg2.grp = tg.grp and tg2.pk <= tg.pk
) p;
唉，在 SQL Server 2012 之前，最有效的解决方案可能涉及游标.在 SQL Server 2012+ 中，您只需执行以下操作:
Alas, prior to SQL Server 2012, the most efficient solution might involve cursors. In SQL Server 2012+, you simply do:
select t.*,
sum(price) over (partition by grp order by pk) as running_price
from (select t.*,
sum(case when price = 0 then 1 else 0 end) over (order by pk) as grp
from t
) t;
这篇关于SQL Server - 遇到 0 时重置的累积和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！