用前行将SQL中的值求和 [英] sum values in sql with previous rows

问题描述

我有sp返回当前结构

rowno --- value1-- value2-value3-总计

我希望总数为当前行值+上一行总值
&我需要性能声明，因为我返回了约3000行
我尝试使用sql对其进行循环，但需要太多时间

例如

RowNO值1值2值3总计
1 1 2 3 6
2 4 -6 8 12
3 5 6 78 101
4 8 9 0 118
5 4 77 8 207

等待任何帮助
关于问候

i have sp that return the current structure

rowno --- value1-- value2 -- value3 -- total

i want the total to be the current row value +the previous row total value
& i need performance statement becuase i return about 3000 rows
i try to loop on it with sql but it take too much time

for example

RowNO Value1 Value2 Value3 Total
1 1 2 3 6
2 4 -6 8 12
3 5 6 78 101
4 8 9 0 118
5 4 77 8 207

waiting for any help
with regards

推荐答案

这是我即时进行的操作，因此其中可能有一些小错误，但理论是合理的.基本上，您需要使用游标.现在，如果您完全熟悉SQL，那么您将不知所措，但是在这种情况下，使用游标才是可行的方法.进行循环或自连接的速度会稍慢一些.如果您不存储Value1，Value2和Value3值，也可以使该语句更加优化，但是我认为我将向您展示完整的查询.您可以从这里选择如何执行操作.

Here is something I did on the fly, so there might be a couple minor errors in it, but the theory is sound. Basically, you need to use a cursor. Now, if you are familiar with SQL at all, you are going to shudder at that but in this case, using a cursor is the way to go. Doing a loop or a self-join will be an order of magnatude slower. You could also probably make this statement even more optimized if you didn''t store the Value1, Value2, and Value3 values but I figured I would show you the full query. How you choose to do it from here is up to you.

DECLARE @MyTbl TABLE (id int, Value1 int, Value2 int, Value3 int, Total int)
 
DECLARE @id int,
        @Value1 int,
        @Value2 int,
        @Value3 int,
        @RowTotal int,
        @RunningTotal int

SET @RunningTotal = 0

DECLARE runningTotal_cursor CURSOR
FOR
SELECT RowNo, Value1, Value2, Value3, (Value1 + Value2 + Value3) AS Total
FROM MyData
ORDER BY RowNo

OPEN runningTotal_cursor

FETCH NEXT FROM rt_cursor INTO @id, @Value1, @Value2, @Value3, @RowTotal

WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @RunningTotal = @RunningTotal + @RowTotal
 INSERT @MyTbl VALUES (@id, @Value1, @Value2, @Value3, @RunningTotal)
 FETCH NEXT FROM rt_cursor INTO @id, @Value1, @Value2, @Value3, @RowTotal
END

CLOSE runningTotal_cursor
 
DEALLOCATE runningTotal_cursor

SELECT * FROM @MyTbl

这篇关于用前行将SQL中的值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！