SQL 累計加總

SQL 累計加總
原參考 https://www.1keydata.com/tw/sql/sql-running-totals.html

SELECT a1.id, a1.totalqty, SUM(a2.totalqty) Running_Total
FROM eipe.dbo.oqc_bodyspec a1 , eipe.dbo.oqc_bodyspec a2
WHERE a1.totalqty <= a2.totalqty OR (a1.totalqty=a2.totalqty AND a1.id = a2.id)
GROUP BY a1.id, a1.totalqty
ORDER BY a1.totalqty DESC, a1.id DESC;

但計算太久會影響校能，後來在看到　Over 加總後寫出以下的語法

select S.ID,ISNULL(S.totalqty,0),sum(ISNULL(S.totalqty,0)) over(ORDER BY S.ID) SUM_TOTALQTY
from  spec S
ORDER BY S.ID

參考:

以Over子句代替子查尋，SQL將更有效率

撰寫T-SQL來達到累加欄位值的效果，可利用OVER子句並加上SUM函數(SQL Server 2012以上支援)