本文整理自网络,侵删。
看了一眼自关联,没搞懂,试了一下也没成功。
over方式一下结果就出来了,好用。
/* 需求:累计求和六种算法效率比较 作者: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.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子句<游标临时表更新<游标变量叠加更新<自连接<相关子查询<递归查询
补充:下面看下SQL server 累加求和
相关阅读 >>
sql server--怎样用ado在sql server中建库,建表
mysql通过show status查看、explain分析优化数据库性能
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。