首頁猿問如何求取累積和

如何求取累積和

MySQL SQL Server

DIEA 2019-06-25 17:15:26

如何求取累積和declare @t table ( id int, SomeNumt int )insert into @tselect 1,10unionselect 2,12unionselect 3,3unionselect 4,15unionselect 5,23select * from @t上面的選擇返回以下內(nèi)容。id SomeNumt1 102 123 34 155 23如何獲得以下信息id srome CumSrome1 10 102 12 223 3 254 15 405 23 63

查看完整描述

3 回答

茅侃侃

TA貢獻1842條經(jīng)驗獲得超22個贊

SQLServer(2012)的最新版本允許以下操作。

SELECT?
????RowID,?
????Col1,
????SUM(Col1)?OVER(ORDER?BY?RowId?ROWS?BETWEEN?UNBOUNDED?PRECEDING?AND?CURRENT?ROW)?AS?Col2FROM?tablehhORDER?BY?RowId

或

SELECT?
????GroupID,?
????RowID,?
????Col1,
????SUM(Col1)?OVER(PARTITION?BY?GroupID?ORDER?BY?RowId?ROWS?BETWEEN?UNBOUNDED?PRECEDING?AND?CURRENT?ROW)?AS?Col2FROM?tablehhORDER?BY?RowId

這更快。分區(qū)版本在34秒內(nèi)完成，超過500萬行。

反對回復(fù) 2019-06-25