看了一眼自关联，没搞懂，试了一下也没成功。

over方式一下结果就出来了，好用。

*/
–第一步，准备测试数据
–IF OBJECT_ID(N’dbo.t’) IS NOT NULL
— DROP TABLE dbo.t;
–GO
–CREATE TABLE dbo.t
–(
— i BIGINT IDENTITY(1, 1) PRIMARY KEY,
— d MONEY
–);
–INSERT t
— d
–)
–SELECT TOP 31465
— ROUND(10000 * RAND(CHECKSUM(NEWID())), 2)
–FROM sys.all_objects AS a
— CROSS JOIN sys.all_objects;
—-第二步，创建记录时间的表格
–IF OBJECT_ID(N’dbo.record_time’) IS NOT NULL
— DROP TABLE dbo.record_time;
–CREATE TABLE dbo.record_time
— i INT IDENTITY PRIMARY KEY,
— 算法 NVARCHAR(10),
— bt DATETIME2,–开始时间
— et DATETIME2,–结束时间
— idiff AS DATEDIFF(ms, bt, et)–所用的毫秒数
–第一种方法，自连接法，sql server 2008以上版本测试通过，157255661.40
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DECLARE @bt DATETIME2 = GETDATE();
SELECT a.i,
a.d,
SUM(b.d) AS total_sum
FROM dbo.t AS a
INNER JOIN dbo.t AS b
ON b.i <= a.i
GROUP BY a.i,
a.d;
DECLARE @et DATETIME2 = GETDATE();
INSERT INTO dbo.record_time
(
算法,
bt,
et
)
VALUES
(‘自连接’, @bt, @et);
–ORDER BY a.i;
;
–第二种方法，递归，sql server 2008以上版本测试通过，157255661.40
WITH cte_total_sum
AS (SELECT i,
d,
d AS total_sum
FROM dbo.t
WHERE i = 1
UNION ALL
SELECT s.i,
s.d,
p.total_sum + s.d AS total_sum
FROM dbo.t AS s
INNER JOIN cte_total_sum AS p
ON s.i – 1 = p.i)
SELECT *
FROM cte_total_sum
OPTION (MAXRECURSION 0);
(‘递归’, @bt, @et);
–第三种方法，over 子句，sql server 2012测试通过，sql server 2008不支持，157255661.40
SELECT i,
d,
SUM(d) OVER (ORDER BY i) AS total_sum
FROM dbo.t;
(‘over子句’, @bt, @et);
–第四种，相关子查询，sql server 2008以上版本测试通过，156625045.22
SELECT outquery.i,
outquery.d,
(
SELECT SUM(innerq.d) FROM dbo.t AS innerq WHERE innerq.i <= outquery.i
) AS ct –内部查询
FROM dbo.t AS outquery;
(‘相关子查询’, @bt, @et);
–ORDER BY outquery.i; –外部查询
–游标方法，有两种方法可以实现，一种是临时表更新，一种是变量叠加更新，157255661.40
–先增加一个存储累计和的列
–第5种，游标_临时表更新
–ALTER TABLE dbo.t ADD total_d MONEY DEFAULT (0);–只运行一次
DECLARE @t TABLE –定义表变量，存储累计求和临时结果
i INT PRIMARY KEY IDENTITY,
d MONEY,
total_d MONEY
);
DECLARE @i INT = 0,
@d MONEY = 0,
@total_d MONEY = 0;
DECLARE c1 CURSOR FOR SELECT i, d FROM dbo.t ORDER BY i;
OPEN c1;
FETCH c1
INTO @i,
@d;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @total_d += @d;
INSERT INTO @t
(
d,
total_d
)
VALUES
(@d, @total_d);
FETCH c1
INTO @i,
@d;
END;
CLOSE c1;
DEALLOCATE c1;
UPDATE dbo.t
SET total_d = b.total_d
INNER JOIN @t AS b
ON a.i = b.i;
(‘游标_临时表更新’, @bt, @et);
–第6种，游标_变量叠加更新
DECLARE c1 CURSOR FOR SELECT i, d FROM dbo.t; –ORDER BY i;
UPDATE dbo.t
SET total_d = @total_d
WHERE i = @i;
(‘游标_变量叠加更新’, @bt, @et);
–执行时间 over子句<游标临时表更新<游标变量叠加更新<自连接<相关子查询<递归查询