TestTable）：id somedate somevalue
-- -------- ---------
45 01/Jan/09 3
23 08/Jan/09 5
12 02/Feb/09 0
77 14/Feb/09 7
39 20/Feb/09 34
33 02/Mar/09 6
我想要一个按日期顺序返回运行总计的查询，例如：
id somedate somevalue runningtotal
-- -------- --------- ------------
45 01/Jan/09 3 3
23 08/Jan/09 5 8
12 02/Feb/09 0 8
77 14/Feb/09 7 15
39 20/Feb/09 34 49
33 02/Mar/09 6 55
我知道SQL Server 2000/2005/2008中有多种方法可以做到这一点。
我对这种使用聚合集的方法特别感兴趣-statement技巧：
INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal)
SELECT id, somedate, somevalue, null
FROM TestTable
ORDER BY somedate
DECLARE @RunningTotal int
SET @RunningTotal = 0
UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl
...这是非常有效的，但是我听说周围存在一些问题，因为您不一定能保证
UPDATE语句能够处理以正确的顺序排列行。也许我们可以获得有关该问题的明确答案。但是也许人们可以提出其他建议呢？
编辑：现在使用带有设置和'上面的“更新技巧”示例
#1 楼
如果正在运行SQL Server 2012，请进行更新，请参见：https://stackoverflow.com/a/10309947问题是Over子句的SQL Server实现受到一定限制。
Oracle（和ANSI-SQL）使您可以执行以下操作：
SELECT somedate, somevalue,
SUM(somevalue) OVER(ORDER BY somedate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Table
SQL Server无法为您解决此问题。我的直觉告诉我，这是极少数情况下光标最快的情况之一，尽管我必须对大结果进行一些基准测试。
更新技巧很方便，但我觉得它相当脆弱。看来，如果要更新完整表，则它将按主键的顺序进行。因此，如果将日期设置为升序的主键，则将是安全的。但是，您所依赖的是未记录的SQL Server实现细节（如果查询最终还是由两个proc执行，我想知道会发生什么，请参见：MAXDOP）：
完整的工作示例：
drop table #t
create table #t ( ord int primary key, total int, running_total int)
insert #t(ord,total) values (2,20)
-- notice the malicious re-ordering
insert #t(ord,total) values (1,10)
insert #t(ord,total) values (3,10)
insert #t(ord,total) values (4,1)
declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total
select * from #t
order by ord
ord total running_total
----------- ----------- -------------
1 10 10
2 20 30
3 10 40
4 1 41
您要求提供基准，这是最低点。
最快的SAFE方法是Cursor，它比交叉联接的相关子查询快一个数量级。
绝对最快的方法是UPDATE技巧。我唯一关心的是，我不确定在所有情况下更新都会以线性方式进行。查询中没有明确说明的内容。
底线，对于生产代码，我将使用光标。
测试数据：
create table #t ( ord int primary key, total int, running_total int)
set nocount on
declare @i int
set @i = 0
begin tran
while @i < 10000
begin
insert #t (ord, total) values (@i, rand() * 100)
set @i = @i +1
end
commit
测试1：
SELECT ord,total,
(SELECT SUM(total)
FROM #t b
WHERE b.ord <= a.ord) AS b
FROM #t a
-- CPU 11731, Reads 154934, Duration 11135
测试2 ：
SELECT a.ord, a.total, SUM(b.total) AS RunningTotal
FROM #t a CROSS JOIN #t b
WHERE (b.ord <= a.ord)
GROUP BY a.ord,a.total
ORDER BY a.ord
-- CPU 16053, Reads 154935, Duration 4647
测试3：
DECLARE @TotalTable table(ord int primary key, total int, running_total int)
DECLARE forward_cursor CURSOR FAST_FORWARD
FOR
SELECT ord, total
FROM #t
ORDER BY ord
OPEN forward_cursor
DECLARE @running_total int,
@ord int,
@total int
SET @running_total = 0
FETCH NEXT FROM forward_cursor INTO @ord, @total
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @running_total = @running_total + @total
INSERT @TotalTable VALUES(@ord, @total, @running_total)
FETCH NEXT FROM forward_cursor INTO @ord, @total
END
CLOSE forward_cursor
DEALLOCATE forward_cursor
SELECT * FROM @TotalTable
-- CPU 359, Reads 30392, Duration 496
测试4：
declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total
select * from #t
-- CPU 0, Reads 58, Duration 139
评论
谢谢。因此，我想您的代码示例将演示它将按主键的顺序求和。知道游标是否仍然比连接较大数据集的连接更有效是很有趣的。
– codeulike
09年5月14日在8:37
@Martin Denali将为此msdn.microsoft.com/zh-cn/library/ms189461(v=SQL.110).aspx提供一个非常好的解决方案
– Sam Saffron
2011年9月19日下午2:18
对于此答案中的所有工作，+ 1；我喜欢UPDATE选项；分区可以内置到此UPDATE脚本中吗？例如，如果还有一个附加字段“ Car Colour”，此脚本可以返回每个“ Car Colour”分区内的运行总计吗？
–为什么
2012年8月10日14:02
初始（Oracle（和ANSI-SQL））答案现在可以在SQL Server 2017中使用。谢谢，非常优雅！
–DaniDev
19-09-24在17:36
#2 楼
在SQL Server 2012中，可以将SUM（）与OVER（）子句一起使用。select id,
somedate,
somevalue,
sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable
SQL Fiddle
#3 楼
尽管Sam Saffron在此方面做了大量工作，但他仍然没有为该问题提供递归的公用表表达式代码。对于使用SQL Server 2008 R2而不是Denali的我们来说，这仍然是最快的总运行方式，它比我的工作计算机上的游标要快10倍，可读取100000行，而且它也是内联查询。所以，就是这里（我假设表中有一个
ord列，并且它是无间隙的连续数字，为了快速处理，对该数字也应该有唯一的约束）：sql小提琴演示
更新
我也对具有可变或古怪更新的更新感到好奇。因此通常它可以正常工作，但是我们如何确定每次都能正常工作呢？好吧，这是一个小技巧（在此处找到它-http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258）-您只需检查当前
ord和以前的1/0并使用ord_id分配即可（如果它们不同）根据您的期望：;with
CTE_RunningTotal
as
(
select T.ord, T.total, T.total as running_total
from #t as T
where T.ord = 0
union all
select T.ord, T.total, T.total + C.running_total as running_total
from CTE_RunningTotal as C
inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)
-- CPU 140, Reads 110014, Duration 132
从我所见，如果您的表上具有正确的聚集索引/主键（在我们的情况下，它将由q4312079q进行索引） ）更新将一直以线性方式进行（永远不会被零除）。就是说，由您决定是否要在生产代码中使用它：)
更新2我正在链接此答案，因为它包含一些有关不可靠更新的有用信息- nvarchar串联/索引/ nvarchar（max）无法解释的行为。
评论
这个答案值得更多的认可（或者也许有一些我看不到的缺陷？）
–user1068352
2013年6月19日下午6:28
应该有一个序号，以便您可以加入ord = ord + 1，有时还需要更多工作。但是无论如何，在SQL 2008 R2上，我正在使用此解决方案
–罗马·佩卡尔
2013年6月24日10:21
+1在SQLServer2008R2上，我也更喜欢使用递归CTE的方法。仅供参考，为了找到表的值（允许出现空白），我使用了相关子查询。它向查询sqlfiddle.com/#!3/d41d8/18967添加了两个附加的搜索操作。
–亚历山大·费多连科（Aleksandr Fedorenko）
13年8月18日在14:08
对于已经有数据序数并且您正在寻找基于SQL 2008 R2的简洁（非游标）集解决方案的情况，这似乎是完美的。
– Nick.McDermaid
2014年2月25日在1:03
并非每个正在运行的总计查询都具有连续的序数字段。有时，日期时间字段就是您所拥有的，或者记录已从排序的中间删除。这可能就是为什么它不经常使用的原因。
–鲁本
14-10-29在4:17
#4 楼
SQL 2005和更高版本中的APPLY运算符可用于此目的：select
t.id ,
t.somedate ,
t.somevalue ,
rt.runningTotal
from TestTable t
cross apply (select sum(somevalue) as runningTotal
from TestTable
where somedate <= t.somedate
) as rt
order by t.somedate
评论
对于较小的数据集，效果很好。缺点是，内部和外部查询上必须具有相同的where子句。
–父亲
2012-09-12 14:29
由于我的某些日期完全相同（不到一秒的时间），因此我必须在内部和外部表中添加：row_number（）over（按txndate排序），并添加一些复合索引以使其运行。光滑/简单的解决方案。顺便说一句，经过测试的交叉适用于子查询...它稍微快一点。
– pghcpa
2015年1月5日在4:39
这非常干净，并且在使用较小的数据集时效果很好；比递归CTE更快
– jtate
16年6月23日在15:41
对于小型数据集，这也是一个不错的解决方案，但是您还必须注意，这意味着somedate列是唯一的
–罗马·佩卡尔
16-9-29在8:04
#5 楼
SELECT TOP 25 amount,
(SELECT SUM(amount)
FROM time_detail b
WHERE b.time_detail_id <= a.time_detail_id) AS Total FROM time_detail a
还可以使用ROW_NUMBER（）函数和临时表来创建一个任意列，以用于内部SELECT语句的比较。
评论
这确实效率很低...但是在SQL Server中再没有真正干净的方法可以做到这一点
– Sam Saffron
09年5月14日，0：36
绝对是没有效率的-但是它确实可以完成工作，而且毫无疑问，是按照正确的顺序还是错误的顺序执行某些操作。
–山姆斧头
09年5月14日下午6:35
谢谢，它有助于获得替代答案，也有助于进行有效的批评
– codeulike
09年5月14日晚上8:30
#6 楼
使用相关的子查询。很简单，请按以下步骤操作：SELECT
somedate,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
GROUP BY somedate
ORDER BY somedate
代码可能并不完全正确，但是我确定这个想法是正确的。 GROUP BY是为了防止日期多次出现，您只希望在结果集中看到一次。
如果您不希望看到重复的日期，或者希望看到原始值和ID，则需要以下内容：
SELECT
id,
somedate,
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate
评论
谢谢...简单很棒。有一个要添加的性能索引，但它很简单（采用了数据库引擎优化顾问的建议之一；），然后运行起来就像一个镜头。
–Doug_Ivison
15年4月28日在22:09
#7 楼
您还可以取消规范化-将运行总计存储在同一表中：http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-en-enforce-business-rules -running-totals.aspx
选择的工作速度比任何其他解决方案都要快，但修改可能会更慢
#8 楼
假设窗口可以像在其他地方（我已经尝试过）一样在SQL Server 2008上运行，那么就去做吧：（也许还有2005年？），但是我没有实例可以尝试。
编辑：好吧，显然SQL Server不允许窗口规范（“ OVER（。 。）”），而无需指定“ PARTITION BY”（将结果分成几组，但不完全按照GROUP BY的方式进行汇总）。令人讨厌的是-MSDN语法参考建议使用它为可选，但此刻我只有SqlServer 2000实例。
我给出的查询在Oracle 10.2.0.3.0和PostgreSQL 8.4-beta中均有效。所以告诉MS赶上;）
评论
在这种情况下，将OVER与SUM一起使用将无法获得总计。与SUM一起使用时，OVER子句不接受ORDER BY。您必须使用PARTITION BY，它将无法用于运行总计。
–山姆斧头
09年5月14日，0：23
谢谢，它实际上对了解为什么这行不通很有用。 araqnid也许您可以编辑答案以解释为什么它不是一个选择
– codeulike
09年5月14日在8:33
显然会出现在SQL Server 2011中
–马丁·史密斯
2011年5月18日在21:08
这实际上对我有用，因为我需要进行分区-因此，即使这不是最受欢迎的答案，它也是解决SQL RT问题的最简单解决方案。
–威廉M-B
13年13月13日在16:32
我没有MSSQL 2008，但我认为您可能可以按（选择null）进行分区，然后解决分区问题。或使用1个partitionme进行子选择，并以此划分。此外，在现实生活中进行报告时可能需要按分区。
– Nurettin
17年7月19日在14:30
#9 楼
如果您使用的是上面的Sql Server 2008 R2。然后，这是最短的方法；Select id
,somedate
,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable
LAG用于获取前一行的值。您可以通过google获取更多信息。
[1]：
评论
我认为LAG仅存在于SQL Server 2012及更高版本中（不存在于2008年）
– AaA
17-10-23在4:35
使用LAG（）不能改善SUM（somevalue）OVER（...），对我来说似乎更干净
–Used_By_Already
17-10-23在6:45
#10 楼
我相信可以使用下面的简单INNER JOIN操作来实现运行总计。SELECT
ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID
,rt.*
INTO
#tmp
FROM
(
SELECT 45 AS ID, CAST('01-01-2009' AS DATETIME) AS SomeDate, 3 AS SomeValue
UNION ALL
SELECT 23, CAST('01-08-2009' AS DATETIME), 5
UNION ALL
SELECT 12, CAST('02-02-2009' AS DATETIME), 0
UNION ALL
SELECT 77, CAST('02-14-2009' AS DATETIME), 7
UNION ALL
SELECT 39, CAST('02-20-2009' AS DATETIME), 34
UNION ALL
SELECT 33, CAST('03-02-2009' AS DATETIME), 6
) rt
SELECT
t1.ID
,t1.SomeDate
,t1.SomeValue
,SUM(t2.SomeValue) AS RunningTotal
FROM
#tmp t1
JOIN #tmp t2
ON t2.OrderID <= t1.OrderID
GROUP BY
t1.OrderID
,t1.ID
,t1.SomeDate
,t1.SomeValue
ORDER BY
t1.OrderID
DROP TABLE #tmp
评论
是的，我认为这相当于Sam Saffron回答中的“测试3”。
– codeulike
2011年2月4日在18:57
#11 楼
以下将产生所需的结果。SELECT a.SomeDate,
a.SomeValue,
SUM(b.SomeValue) AS RunningTotal
FROM TestTable a
CROSS JOIN TestTable b
WHERE (b.SomeDate <= a.SomeDate)
GROUP BY a.SomeDate,a.SomeValue
ORDER BY a.SomeDate,a.SomeValue
SomeDate上具有聚簇索引将大大提高性能。
评论
@Dave我认为这个问题试图找到一种有效的方法，对于大集合来说，交叉连接将非常缓慢
– Sam Saffron
09年5月14日在0:44
谢谢，它有助于获得替代答案，也有助于进行有效的批评
– codeulike
09年5月14日晚上8:30
#12 楼
使用连接另一种变化是使用连接。现在查询看起来像：
SELECT a.id, a.value, SUM(b.Value)FROM RunTotalTestData a,
RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value
ORDER BY a.id;
更多信息，您可以访问此链接
http://askme.indianyouth.info/details/calculating-simple -running-totals-in-sql-server-12
#13 楼
尽管最好的方法是使用窗口函数，但也可以使用简单的相关子查询来实现。Select id, someday, somevalue, (select sum(somevalue)
from testtable as t2
where t2.id = t1.id
and t2.someday <= t1.someday) as runningtotal
from testtable as t1
order by id,someday;
#14 楼
BEGIN TRAN
CREATE TABLE #Table (_Id INT IDENTITY(1,1) ,id INT , somedate VARCHAR(100) , somevalue INT)
INSERT INTO #Table ( id , somedate , somevalue )
SELECT 45 , '01/Jan/09', 3 UNION ALL
SELECT 23 , '08/Jan/09', 5 UNION ALL
SELECT 12 , '02/Feb/09', 0 UNION ALL
SELECT 77 , '14/Feb/09', 7 UNION ALL
SELECT 39 , '20/Feb/09', 34 UNION ALL
SELECT 33 , '02/Mar/09', 6
;WITH CTE ( _Id, id , _somedate , _somevalue ,_totvalue ) AS
(
SELECT _Id , id , somedate , somevalue ,somevalue
FROM #Table WHERE _id = 1
UNION ALL
SELECT #Table._Id , #Table.id , somedate , somevalue , somevalue + _totvalue
FROM #Table,CTE
WHERE #Table._id > 1 AND CTE._Id = ( #Table._id-1 )
)
SELECT * FROM CTE
ROLLBACK TRAN
评论
您可能应该提供一些有关您在这里做什么的信息，并注意此特定方法的任何优点/缺点。
– TT。
16年11月4日在7:17
#15 楼
以下是两种用于计算运行总额的简单方法：方法1：如果您的DBMS支持分析函数，则可以这样编写：
SELECT id
,somedate
,somevalue
,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM TestTable
方法2：如果您的数据库版本/ DBMS本身不支持分析功能，则可以使用OUTER APPLY。
SELECT T.id
,T.somedate
,T.somevalue
,runningtotal = OA.runningtotal
FROM TestTable T
OUTER APPLY (
SELECT runningtotal = SUM(TI.somevalue)
FROM TestTable TI
WHERE TI.somedate <= S.somedate
) OA;
注：-如果必须计算运行总计对于不同的分区，可以按照此处的说明进行操作：计算跨行的运行总计并按ID分组
评论
blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx在更新中添加订单...集，您将得到保证。但是Order by不能应用于UPDATE语句...可以吗？
另请参见sqlperformance.com/2012/07/t-sql-queries/running-totals，尤其是在使用SQL Server 2012的情况下。