sql?server?累計(jì)求和實(shí)現(xiàn)代碼
看了一眼自關(guān)聯(lián),沒搞懂,試了一下也沒成功。
over方式一下結(jié)果就出來了,好用。
/* 需求:累計(jì)求和六種算法效率比較 作者:felix 日期:2020-06-23 */ --第一步,準(zhǔn)備測(cè)試數(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以上版本測(cè)試通過,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以上版本測(cè)試通過,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測(cè)試通過,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以上版本測(cè)試通過,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)子查詢<遞歸查詢
補(bǔ)充:下面看下SQL server 累加求和
SQL server 累加求和
1.
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.
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 PeriodPreAmt from jr_creditUserAcct a
到此這篇關(guān)于sql server 累計(jì)求和實(shí)現(xiàn)代碼的文章就介紹到這了,更多相關(guān)sql server 累計(jì)求和內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
sql 存儲(chǔ)過程批量刪除數(shù)據(jù)的語句
sql 存儲(chǔ)過程批量刪除數(shù)據(jù)的語句,需要的朋友可以參考下2012-08-08sqlserver主鍵設(shè)計(jì)的注意點(diǎn)
在數(shù)據(jù)庫設(shè)計(jì)中,主鍵用于惟一地標(biāo)識(shí)表中的某一條記錄2012-07-07分享:在存儲(chǔ)過程中使用另一個(gè)存儲(chǔ)過程返回的查詢結(jié)果集的方法
本篇文章介紹了,在存儲(chǔ)過程中使用另一個(gè)存儲(chǔ)過程返回的查詢結(jié)果集的方法。需要的朋友參考下2013-04-04如何在navicat中利用sql語句建表+添加數(shù)據(jù)
這篇文章主要給大家介紹了關(guān)于如何在navicat中利用sql語句建表+添加數(shù)據(jù)的相關(guān)資料,Navicat是一套快速,專為簡化數(shù)據(jù)庫的管理及降低系統(tǒng)管理成本而設(shè),它的設(shè)計(jì)符合數(shù)據(jù)庫管理員、開發(fā)人員及中小企業(yè)的需要,需要的朋友可以參考下2023-10-10SQL中l(wèi)imit的用法小結(jié)(一個(gè)參數(shù),兩個(gè)參數(shù))分頁查詢
Limit通常用于SELECT語句的末尾,用于限制查詢結(jié)果的數(shù)量,這篇文章主要介紹了SQL中l(wèi)imit的用法(一個(gè)參數(shù),兩個(gè)參數(shù)),分頁查詢,需要的朋友可以參考下2023-12-12sqlserver exists,not exists的用法
exists,not exists的使用方法示例,需要的朋友可以參考下。2009-12-12SQL?Server中的XML數(shù)據(jù)類型詳解
本文詳細(xì)講解了SQL?Server中的XML數(shù)據(jù)類型,文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-05-05