如何在不使用游标的情况下计算 SQL 中的运行总数? [英] How do I calculate a running total in SQL without using a cursor?

问题描述

为简洁起见，我省略了所有游标设置和临时表中的 SELECT.基本上，此代码计算每笔交易的所有交易的运行余额.

I'm leaving out all the cursor setup and the SELECT from the temp table for brevity. Basically, this code computes a running balance for all transactions per transaction.

WHILE @@fetch_status = 0
BEGIN

    set @balance = @balance+@amount

    insert into @tblArTran values ( --from artran table
                @artranid, @trandate, @type, 
                @checkNumber, @refNumber,@custid,
                @amount, @taxAmount, @balance, @postedflag, @modifieddate )


    FETCH NEXT FROM artranCursor into 
            @artranid, @trandate, @type, @checkNumber, @refNumber,
            @amount, @taxAmount,@postedFlag,@custid, @modifieddate

END

受此代码的启发，来自另一个问题的答案，

Inspired by this code from an answer to another question,

SELECT @nvcConcatenated = @nvcConcatenated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

我想知道 SQL 是否能够以与连接字符串相同的方式对数字求和，如果你明白我的意思.也就是说，在不使用游标的情况下，为每行创建一个运行余额".

I was wondering if SQL had the ability to sum numbers in the same way it's concatonating strings, if you get my meaning. That is, to create a "running balance" per row, without using a cursor.

有可能吗?

推荐答案

您可能想在此处查看局部变量解决方案的更新:http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

You might want to take a look at the update to local variable solution here: 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

优于所有其他方法，但对保证行顺序有一些疑问.虽然临时表被索引时似乎工作正常..

Outperforms all other methods, but has some doubts about guaranteed row order. Seems to work fine when temp table is indexed though..

• 嵌套子查询 9300 毫秒
• 自加入 6100 毫秒
• 光标 400 毫秒
• 更新到局部变量 140 毫秒

这篇关于如何在不使用游标的情况下计算 SQL 中的运行总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！