看了一眼自关联，没搞懂，试了一下也没成功。
over方式一下结果就出来了，好用。
|
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
|
/*需求：累计求和六种算法效率比较作者：felix日期：2020-06-23*/--第一步，准备测试数据--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.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);--第四种，相关子查询，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 --内部查询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 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;('游标_临时表更新', @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子句<游标临时表更新<游标变量叠加更新<自连接<相关子查询<递归查询 |
补充：下面看下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 |
到此这篇关于sql server 累计求和实现代码的文章就介绍到这了,更多相关sql server 累计求和内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://www.cnblogs.com/luna-hehe/p/15935342.html
