Update,如果您运行的是SQL Server 2012,请参阅:https://stackoverflow.com/a/10309947
问题是Over子句的SQL Server实现是somewhat limited.
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并没有为这个问题提供干净的解决方案.我的直觉告诉我,这是光标最快的罕见 case 之一,尽管我必须对重大结果进行一些基准测试.
更新技巧很方便,但我觉得它相当脆弱.如果您正在更新一个完整的表,那么它将按照主键的顺序进行.所以,如果你把日期设为主键,你就安全了.但您依赖的是一个未记录的SQL Server实现细节(如果查询最终由两个过程执行,我想知道会发生什么,请参阅: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
你要求提供一个基准,这是事实真相.
最快的安全方法是使用光标,它比交叉连接的相关子查询快一个数量级.
最快的方法就是更新技巧.我唯一担心的是,我不确定在所有情况下,更新都会以线性方式进行.查询中没有明确说明这一点.
总之,对于生产代码,我将使用光标.
测试数据:
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