使用“更新到本地变量"，可以使用计算分组运行总计 [英] Using "Update to a local variable" to calculate Grouped Running Totals

问题描述

由于它似乎是禁食方法，因此我试图使用更新为局部变量"方法来计算SQL中的运行总计.要为此添加一个额外的层，我对能够对运行总计进行分组很感兴趣，但是我无法理解在何处对查询进行调整以执行此操作(或者甚至可以使用此方法) .任何帮助都将不胜感激.

As it appears to be the fasted method, I am trying to use the "Update to a local variable" method to calculate running totals in SQL. To add an extra layer to this, I am interested in being able to group the running totals, but I can't make sense of where to make the tweaks to my query to do this (or if it is even possible with this method). Any and all help will be appreciated.

下面是我用来创建查询的代码，以及第一篇导致我使用此方法的文章.如何修改查询以使每个 daycount

Below is the code that I used to create my query, and the article that led me to this method in the first place. How can I modify the query to have the running total reset for every daycount

http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

谢谢

赖利

推荐答案

注释中的链接使我能够提出正确的编码.在上面的示例中，下面是如何将更新功能修改为按天分组.

The links in the comments allowed me to come up with the right coding. Below is how the update function needs to be modified to group by Day in the above example.

UPDATE @salestbl
SET @RunningTotal = RunningTotal = sales +
    CASE WHEN daycount=@lastday THEN @RunningTotal ELSE 0 END
   ,@lastday=daycount
FROM @salestbl

这篇关于使用“更新到本地变量"，可以使用计算分组运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！