看了一眼自關(guān)聯(lián)，沒搞懂，試了一下也沒成功。

over方式一下結(jié)果就出來了，好用。

*/
–第一步，準(zhǔn)備測試數(shù)據(jù)
–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;
—-第二步，創(chuàng)建記錄時(shí)間的表格
–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,–開始時(shí)間
— et DATETIME2,–結(jié)束時(shí)間
— idiff AS DATEDIFF(ms, bt, et)–所用的毫秒數(shù)
–第一種方法，自連接法，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);
–第四種，相關(guān)子查詢，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 –內(nèi)部查詢
FROM dbo.t AS outquery;
(‘相關(guān)子查詢’, @bt, @et);
–ORDER BY outquery.i; –外部查詢
–游標(biāo)方法，有兩種方法可以實(shí)現(xiàn)，一種是臨時(shí)表更新，一種是變量疊加更新，157255661.40
–先增加一個(gè)存儲(chǔ)累計(jì)和的列
–第5種，游標(biāo)_臨時(shí)表更新
–ALTER TABLE dbo.t ADD total_d MONEY DEFAULT (0);–只運(yùn)行一次
DECLARE @t TABLE –定義表變量，存儲(chǔ)累計(jì)求和臨時(shí)結(jié)果
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;
(‘游標(biāo)_臨時(shí)表更新’, @bt, @et);
–第6種，游標(biāo)_變量疊加更新
DECLARE c1 CURSOR FOR SELECT i, d FROM dbo.t; –ORDER BY i;
UPDATE dbo.t
SET total_d = @total_d
WHERE i = @i;
(‘游標(biāo)_變量疊加更新’, @bt, @et);
–執(zhí)行時(shí)間 over子句<游標(biāo)臨時(shí)表更新<游標(biāo)變量疊加更新<自連接<相關(guān)子查詢<遞歸查詢