第一福利精品老司机导航,日韩在线视频网址,久色精品

看了一眼自關聯，沒搞懂，試了一下也沒成功。

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 累加求和

				?

									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);

				?

									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