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)
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.
我想知道SQL是否有能力以同样的方式对数字进行求和，如果你理解了我的意思的话。也就是说，在不使用游标的情况下，为每一行创建一个“运行平衡”。
Is it possible?
是可能的吗?
11 个解决方案
#1
20
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
您可能想看看本地变量解决方案的更新:http://geekswithblogs.net/rhames/archive/2008/10/28/calculating-running- total-sql -server- 2005--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..
优于所有其他方法，但对保证行顺序有一些疑问。当temp表被索引时，似乎可以正常工作。
- Nested sub-query 9300 ms
- 嵌套的子查询9300毫秒
- Self join 6100 ms
- 自我加入6100毫秒
- Cursor 400 ms
- 光标400毫秒
- Update to local variable 140 ms
- 更新到本地变量140毫秒
#2
10
SQL can create running totals without using cursors, but it's one of the few cases where a cursor is actually more performant than a set-based solution (given the operators currently available in SQL Server). Alternatively, a CLR function can sometimes shine well. Itzik Ben-Gan did an excellent series in SQL Server Magazine on running aggregates. The series concluded last month, but you can get access to all of the articles if you have an online subscription.
SQL可以在不使用游标的情况下创建运行总数，但它是少数几个游标实际上比基于集合的解决方案性能更好的情况之一(考虑到SQL Server中现有的操作符)。另外，CLR函数有时也能很好地发挥作用。Itzik Ben-Gan在《SQL Server》杂志上发表了关于运行聚合的优秀系列文章。这个系列上个月结束，但是如果你有在线订阅，你可以访问所有的文章。
Edit: here's his latest article in the series (SQL CLR). Given that you can access the whole series by purchasing an online monthly pass for one month - less than 6 bucks - it's worth your while if you're interested in looking at the problem from all angles. Itzik is a Microsoft MVP and a very bright TSQL coder.
编辑:这是他最新的系列文章(SQL CLR)。考虑到你可以通过购买一个月的在线月票(少于6美元)来访问整个系列，如果你有兴趣从各个角度来研究这个问题，这是值得的。Itzik是微软的MVP，也是一个非常聪明的TSQL程序员。
#3
8
In Oracle and PostgreSQL 8.4 you can use window functions:
在Oracle和PostgreSQL 8.4中，您可以使用窗口函数:
SELECT SUM(value) OVER (ORDER BY id)
FROM mytable
In MySQL, you can use a session variable for the same purpose:
在MySQL中，您可以将会话变量用于相同的目的:
SELECT @sum := @sum + value
FROM (
SELECT @sum := 0
) vars, mytable
ORDER BY
id
In SQL Server, it's a rare example of a task for which a cursor is a preferred solution.
在SQL Server中，游标是首选解决方案的任务很少出现。
#4
4
An example of calculating a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day: (assume the table structure and data)
计算每个记录的运行总数的一个示例，但仅当记录的OrderDate是在相同的日期时。一旦订单日期为不同的日期，则将开始新的运行总数，并在新的一天中累积(假设表结构和数据)
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using sample Orders Table:
下面是使用示例订单表返回的查询结果:
OrderId Order Date OrderAmt Running Total
----------- ---------- ---------- ---------------
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14 47.14
7 10/13/2003 10.08 57.22
8 10/13/2003 7.50 64.72
9 10/13/2003 9.50 74.22
Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
注意，“运行总数”一开始的值是10.50，然后变成了22.00，最后变成了OrderID 3的23.25，因为所有这些记录都有相同的OrderDate(10/11/2003)。但是当OrderID 4显示运行总数被重置时，运行总数又重新开始。这是因为OrderID 4的OrderDate有不同的日期，然后是OrderID 1、2和3。计算每个唯一日期的运行总数再次通过使用相关子查询完成，尽管需要一个额外的WHERE条件，该条件确定不同记录上的OrderDate必须是同一天。这里的条件是通过使用CONVERT函数将OrderDate截断为MM/DD/YYYY格式。
#5
3
In SQL Server 2012 and up you can just use the Sum windowing function directly against the original table:
在SQL Server 2012和up中，可以直接对原始表使用Sum窗口函数:
SELECT
artranid,
trandate,
type,
checkNumber,
refNumber,
custid,
amount,
taxAmount,
Balance = Sum(amount) OVER (ORDER BY trandate ROWS UNBOUNDED PRECEDING),
postedflag,
modifieddate
FROM
dbo.Sales
;
This will perform very well compared to all solutions and will not have the potential for errors as found in the "quirky update".
与所有的解决方案相比，这将表现得很好，并且不会像“古怪的更新”那样有可能出现错误。
Note that you should use the ROWS version when possible; the RANGE version may perform less well.
注意，您应该尽可能使用行版本;范围版本可能表现不太好。
#6
2
You can just include a correlated subquery in the select clause. (This will perform poorly for very large result sets) but
您可以在select子句中包含一个相关的子查询。(对于非常大的结果集，这将表现得很差)但是
Select <other stuff>,
(Select Sum(ColumnVal) From Table
Where OrderColumn <= T.OrderColumn) As RunningTotal
From Table T
Order By OrderColumn
#7
1
You can do a running count, here is an example, keep in mind that this is actually not that fast since it has to scan the table for every row, if your table is large this can be quite time consuming and costly
你可以做一个运行计数，这是一个例子，请记住，这实际上并不快，因为它必须扫描每一行的表，如果你的表很大，这可能会非常耗时和昂贵
create table #Test (id int, Value decimal(16,4))
insert #Test values(1,100)
insert #Test values(2,100)
insert #Test values(3,100)
insert #Test values(4,200)
insert #Test values(5,200)
insert #Test values(6,200)
insert #Test values(7,200)
select *,(select sum(Value) from #Test t2 where t2.id <=t1.id) as SumValues
from #test t1
id Value SumValues
1 100.0000 100.0000
2 100.0000 200.0000
3 100.0000 300.0000
4 200.0000 500.0000
5 200.0000 700.0000
6 200.0000 900.0000
7 200.0000 1100.0000
#8
1
On SQLTeam there's also an article about calculating running totals. There is a comparison of 3 ways to do it, along with some performance measuring:
在SQLTeam上还有一篇关于计算运行总数的文章。有3种方法进行比较，以及一些性能测量:
- using cursors
- 使用游标
- using a subselect (as per SQLMenace's post)
- 使用子选择(根据sqlthreat的帖子)
- using a CROSS JOIN
- 使用交叉连接
Cursors outperform by far the other solutions, but if you must not use cursors, there's at least an alternative.
游标优于其他解决方案，但如果您必须不使用游标，至少还有一种替代方案。
#9
0
That that SELECT @nvcConcatonated bit is only returning a single concatenated value. (Although it's computing the intermediate values on a per-row basis, you're only able to retrieve the final value).
选择@nvcConcatonated位的只返回一个连接值。(虽然它是基于每行计算中间值，但您只能检索最终值)。
So, I think the answer is no. If you wanted a single final sum value you would of course just use SUM.
所以，我认为答案是否定的。如果你想要一个最终的和值你当然会用sum。
I'm not saying you can't do it, I'm just saying you can't do it using this 'trick'.
我不是说你不能这么做，我只是说你不能用这个“把戏”来做。
#10
0
Note that using a variable to accomplish this such as in the following may fail in a multiprocessor system because separate rows could get calculated on different processors and may end up using the same starting value. My understanding is that a query hint could be used to force it to use a single thread, but I do not have that information handy.
请注意，在多处理器系统中使用变量来实现这一点(如下所示)可能会失败，因为不同的处理器可能会计算不同的行，最终可能使用相同的起始值。我的理解是可以使用查询提示来强制它使用单个线程，但是我没有现成的信息。
UPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl
更新@SalesTbl集合@RunningTotal = RunningTotal = @RunningTotal + Sales @SalesTbl
Using one of the other options (a cursor, a window function, or nested queries) is typically going to be your safest bet for reliable results.
使用其他选项(游标、窗口函数或嵌套查询)通常是获得可靠结果的最安全的方法。
#11
0
select TransactionDate, amount, amount + (sum x.amount from transactions x where x.TransactionDate < Transactions) Runningtotal from Transactions
选择TransactionDate、amount、amount + (sum x)。交易x的金额。事务处理日期 <事务)从事务中运行总数< p>
where x.TransactionDate < Transactions could be any condition that will represent all the previous records aside from the current one
在x。TransactionDate <事务可以是任何条件，它将表示除当前记录之外的所有记录。< p>
#1
20
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
您可能想看看本地变量解决方案的更新:http://geekswithblogs.net/rhames/archive/2008/10/28/calculating-running- total-sql -server- 2005--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..
优于所有其他方法，但对保证行顺序有一些疑问。当temp表被索引时，似乎可以正常工作。
- Nested sub-query 9300 ms
- 嵌套的子查询9300毫秒
- Self join 6100 ms
- 自我加入6100毫秒
- Cursor 400 ms
- 光标400毫秒
- Update to local variable 140 ms
- 更新到本地变量140毫秒
#2
10
SQL can create running totals without using cursors, but it's one of the few cases where a cursor is actually more performant than a set-based solution (given the operators currently available in SQL Server). Alternatively, a CLR function can sometimes shine well. Itzik Ben-Gan did an excellent series in SQL Server Magazine on running aggregates. The series concluded last month, but you can get access to all of the articles if you have an online subscription.
SQL可以在不使用游标的情况下创建运行总数，但它是少数几个游标实际上比基于集合的解决方案性能更好的情况之一(考虑到SQL Server中现有的操作符)。另外，CLR函数有时也能很好地发挥作用。Itzik Ben-Gan在《SQL Server》杂志上发表了关于运行聚合的优秀系列文章。这个系列上个月结束，但是如果你有在线订阅，你可以访问所有的文章。
Edit: here's his latest article in the series (SQL CLR). Given that you can access the whole series by purchasing an online monthly pass for one month - less than 6 bucks - it's worth your while if you're interested in looking at the problem from all angles. Itzik is a Microsoft MVP and a very bright TSQL coder.
编辑:这是他最新的系列文章(SQL CLR)。考虑到你可以通过购买一个月的在线月票(少于6美元)来访问整个系列，如果你有兴趣从各个角度来研究这个问题，这是值得的。Itzik是微软的MVP，也是一个非常聪明的TSQL程序员。
#3
8
In Oracle and PostgreSQL 8.4 you can use window functions:
在Oracle和PostgreSQL 8.4中，您可以使用窗口函数:
SELECT SUM(value) OVER (ORDER BY id)
FROM mytable
In MySQL, you can use a session variable for the same purpose:
在MySQL中，您可以将会话变量用于相同的目的:
SELECT @sum := @sum + value
FROM (
SELECT @sum := 0
) vars, mytable
ORDER BY
id
In SQL Server, it's a rare example of a task for which a cursor is a preferred solution.
在SQL Server中，游标是首选解决方案的任务很少出现。
#4
4
An example of calculating a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day: (assume the table structure and data)
计算每个记录的运行总数的一个示例，但仅当记录的OrderDate是在相同的日期时。一旦订单日期为不同的日期，则将开始新的运行总数，并在新的一天中累积(假设表结构和数据)
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using sample Orders Table:
下面是使用示例订单表返回的查询结果:
OrderId Order Date OrderAmt Running Total
----------- ---------- ---------- ---------------
1 10/11/2003 10.50 10.50
2 10/11/2003 11.50 22.00
3 10/11/2003 1.25 23.25
4 10/12/2003 100.57 100.57
5 10/12/2003 19.99 120.56
6 10/13/2003 47.14 47.14
7 10/13/2003 10.08 57.22
8 10/13/2003 7.50 64.72
9 10/13/2003 9.50 74.22
Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
注意，“运行总数”一开始的值是10.50，然后变成了22.00，最后变成了OrderID 3的23.25，因为所有这些记录都有相同的OrderDate(10/11/2003)。但是当OrderID 4显示运行总数被重置时，运行总数又重新开始。这是因为OrderID 4的OrderDate有不同的日期，然后是OrderID 1、2和3。计算每个唯一日期的运行总数再次通过使用相关子查询完成，尽管需要一个额外的WHERE条件，该条件确定不同记录上的OrderDate必须是同一天。这里的条件是通过使用CONVERT函数将OrderDate截断为MM/DD/YYYY格式。
#5
3
In SQL Server 2012 and up you can just use the Sum windowing function directly against the original table:
在SQL Server 2012和up中，可以直接对原始表使用Sum窗口函数:
SELECT
artranid,
trandate,
type,
checkNumber,
refNumber,
custid,
amount,
taxAmount,
Balance = Sum(amount) OVER (ORDER BY trandate ROWS UNBOUNDED PRECEDING),
postedflag,
modifieddate
FROM
dbo.Sales
;
This will perform very well compared to all solutions and will not have the potential for errors as found in the "quirky update".
与所有的解决方案相比，这将表现得很好，并且不会像“古怪的更新”那样有可能出现错误。
Note that you should use the ROWS version when possible; the RANGE version may perform less well.
注意，您应该尽可能使用行版本;范围版本可能表现不太好。
#6
2
You can just include a correlated subquery in the select clause. (This will perform poorly for very large result sets) but
您可以在select子句中包含一个相关的子查询。(对于非常大的结果集，这将表现得很差)但是
Select <other stuff>,
(Select Sum(ColumnVal) From Table
Where OrderColumn <= T.OrderColumn) As RunningTotal
From Table T
Order By OrderColumn
#7
1
You can do a running count, here is an example, keep in mind that this is actually not that fast since it has to scan the table for every row, if your table is large this can be quite time consuming and costly
你可以做一个运行计数，这是一个例子，请记住，这实际上并不快，因为它必须扫描每一行的表，如果你的表很大，这可能会非常耗时和昂贵
create table #Test (id int, Value decimal(16,4))
insert #Test values(1,100)
insert #Test values(2,100)
insert #Test values(3,100)
insert #Test values(4,200)
insert #Test values(5,200)
insert #Test values(6,200)
insert #Test values(7,200)
select *,(select sum(Value) from #Test t2 where t2.id <=t1.id) as SumValues
from #test t1
id Value SumValues
1 100.0000 100.0000
2 100.0000 200.0000
3 100.0000 300.0000
4 200.0000 500.0000
5 200.0000 700.0000
6 200.0000 900.0000
7 200.0000 1100.0000
#8
1
On SQLTeam there's also an article about calculating running totals. There is a comparison of 3 ways to do it, along with some performance measuring:
在SQLTeam上还有一篇关于计算运行总数的文章。有3种方法进行比较，以及一些性能测量:
- using cursors
- 使用游标
- using a subselect (as per SQLMenace's post)
- 使用子选择(根据sqlthreat的帖子)
- using a CROSS JOIN
- 使用交叉连接
Cursors outperform by far the other solutions, but if you must not use cursors, there's at least an alternative.
游标优于其他解决方案，但如果您必须不使用游标，至少还有一种替代方案。
#9
0
That that SELECT @nvcConcatonated bit is only returning a single concatenated value. (Although it's computing the intermediate values on a per-row basis, you're only able to retrieve the final value).
选择@nvcConcatonated位的只返回一个连接值。(虽然它是基于每行计算中间值，但您只能检索最终值)。
So, I think the answer is no. If you wanted a single final sum value you would of course just use SUM.
所以，我认为答案是否定的。如果你想要一个最终的和值你当然会用sum。
I'm not saying you can't do it, I'm just saying you can't do it using this 'trick'.
我不是说你不能这么做，我只是说你不能用这个“把戏”来做。
#10
0
Note that using a variable to accomplish this such as in the following may fail in a multiprocessor system because separate rows could get calculated on different processors and may end up using the same starting value. My understanding is that a query hint could be used to force it to use a single thread, but I do not have that information handy.
请注意，在多处理器系统中使用变量来实现这一点(如下所示)可能会失败，因为不同的处理器可能会计算不同的行，最终可能使用相同的起始值。我的理解是可以使用查询提示来强制它使用单个线程，但是我没有现成的信息。
UPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl
更新@SalesTbl集合@RunningTotal = RunningTotal = @RunningTotal + Sales @SalesTbl
Using one of the other options (a cursor, a window function, or nested queries) is typically going to be your safest bet for reliable results.
使用其他选项(游标、窗口函数或嵌套查询)通常是获得可靠结果的最安全的方法。
#11
0
select TransactionDate, amount, amount + (sum x.amount from transactions x where x.TransactionDate < Transactions) Runningtotal from Transactions
选择TransactionDate、amount、amount + (sum x)。交易x的金额。事务处理日期 <事务)从事务中运行总数< p>
where x.TransactionDate < Transactions could be any condition that will represent all the previous records aside from the current one
在x。TransactionDate <事务可以是任何条件，它将表示除当前记录之外的所有记录。< p>