如何在不使用游标的情况下计算 SQL 中的运行总数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1153879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I calculate a running total in SQL without using a cursor?
提问by Chris McCall
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.
为简洁起见，我省略了所有光标设置和临时表中的 SELECT。基本上，此代码计算每笔交易的所有交易的运行余额。
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?
是否可以？
采纳答案by jandersson
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-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..
优于所有其他方法，但对保证行顺序有一些疑问。虽然临时表被索引时似乎工作正常..
- Nested sub-query 9300 ms
- Self join 6100 ms
- Cursor 400 ms
- Update to local variable 140 ms
- 嵌套子查询 9300 毫秒
- 自加入 6100 毫秒
- 光标 400 毫秒
- 更新到局部变量 140 毫秒
回答by Aaron Alton
SQL cancreate 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 verybright TSQL coder.
编辑：这是他在该系列中的最新文章(SQL CLR)。鉴于您可以通过购买一个月的在线月票来访问整个系列 - 不到 6 美元 - 如果您有兴趣从各个角度看待问题，那么值得一试。Itzik 是 Microsoft MVP 和非常聪明的 TSQL 编码员。
回答by Quassnoi
In Oracleand PostgreSQL 8.4you 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，这是一个以游标为首选解决方案的任务的罕见示例。
回答by JamesM
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 位于同一日期。一旦 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 需要是同一天。此 WHERE 条件是通过使用 CONVERT 函数将 OrderDate 截断为 MM/DD/YYYY 格式来实现的。
回答by ErikE
In SQL Server 2012 and up you can just use the Sumwindowing function directly against the original table:
在 SQL Server 2012 及更高版本中，您可以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 ROWSversion when possible; the RANGEversion may perform less well.
请注意，您应该尽可能使用该ROWS版本；该RANGE版本的性能可能较差。
回答by Charles Bretana
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
回答by MicSim
On SQLTeam there's also an articleabout 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)
- using a CROSS JOIN
- 使用游标
- 使用子选择（根据 SQLMenace 的帖子）
- 使用交叉连接
Cursors outperform by far the other solutions, but if you must not use cursors, there's at least an alternative.
到目前为止，游标的性能优于其他解决方案，但如果您一定不能使用游标，那么至少还有一个替代方案。
回答by SQLMenace
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
回答by Gary McGill
That that SELECT @nvcConcatonatedbit is only returning a singleconcatenated value. (Although it's computing the intermediate values on a per-row basis, you're only able to retrieve the final value).
该SELECT @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'.
我不是说你做不到，我只是说你不能用这个“技巧”来做。
回答by user3440326
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 SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @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.
使用其他选项之一（游标、窗口函数或嵌套查询）通常是获得可靠结果的最安全选择。
