看了一眼自關(guān)聯(lián)，沒搞懂，試了一下也沒成功。
over方式一下結(jié)果就出來了，好用。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
/*需求：累計(jì)求和六種算法效率比較作者：felix日期：2020-06-23*/--第一步，準(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.40SET STATISTICS TIME OFF;SET STATISTICS IO OFF;GODECLARE @bt DATETIME2 = GETDATE();SELECT a.i, a.d, SUM(b.d) AS total_sumFROM dbo.t AS a INNER JOIN dbo.t AS b ON b.i <= a.iGROUP 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.40WITH cte_total_sumAS (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_sumOPTION (MAXRECURSION 0);('遞歸', @bt, @et);--第三種方法，over 子句，sql server 2012測試通過，sql server 2008不支持，157255661.40SELECT i, d, SUM(d) OVER (ORDER BY i) AS total_sumFROM dbo.t;('over子句', @bt, @et);--第四種，相關(guān)子查詢，sql server 2008以上版本測試通過，156625045.22SELECT 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è)存儲累計(jì)和的列--第5種，游標(biāo)_臨時(shí)表更新--ALTER TABLE dbo.t ADD total_d MONEY DEFAULT (0);--只運(yùn)行一次DECLARE @t TABLE --定義表變量，存儲累計(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 c1INTO @i, @d;WHILE @@FETCH_STATUS = 0BEGIN 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.tSET 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)子查詢<遞歸查詢 |
補(bǔ)充：下面看下SQL server 累加求和
SQL server 累加求和
1.
|
1
2
3
4
5
6
7
8
|
SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg" ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count" ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min" ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); |
2.
|
1
2
3
|
select SchSno,convert(varchar(10),a.Dates,120) Dates,sum(Amt_avail) over(partition by SchSno order by convert(varchar(10),a.Dates,120)) as PeriodPreAmtfrom jr_creditUserAcct a |
到此這篇關(guān)于sql server 累計(jì)求和實(shí)現(xiàn)代碼的文章就介紹到這了,更多相關(guān)sql server 累計(jì)求和內(nèi)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://www.cnblogs.com/luna-hehe/p/15935342.html
