sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | column accumulation

;with t1234321 (v) as --於記憶體中建立t1234321資料表
(
select * from (values (1),(2),(3),(4),(3),(2),(1)) t(value) --一個單欄的table,包含七個值,1,2,3,4,3,2,1
),
sumFromFirstToCurrent (id, v, sumResult) as
(
select top (1) 0, 0, 0 from t1234321 t
union all
select c.id + 1, t.v, c.sumResult + t.v from
sumFromFirstToCurrent c inner join --自我相加
(select row_number() over (order by getdate()) - 1 id, t1.v v from t1234321 t1) t --於t1234321資料表中加入id
on c.id = t.id
)
select * from sumFromFirstToCurrent
OPTION (MAXRECURSION 32767)
id v sumResult
0 0 0
1 1 1
2 2 3
3 3 6
4 4 10
5 3 13
6 2 15
7 1 16
Comments