欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

sql?server?累計(jì)求和實(shí)現(xiàn)代碼

 更新時(shí)間:2022年02月25日 11:44:53   作者:單純的桃子  
這篇文章主要介紹了sql?server?累計(jì)求和的實(shí)現(xiàn)代碼,代碼簡單易懂,對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

看了一眼自關(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)文章

最新評(píng)論