declare @t table ( id int, SomeNumt int ) insert into @t select 1,10 union select 2,12 union select 3,3 union select 4,15 union select 5,23 select * from @t
上述选择返回我以下。
id SomeNumt 1 10 2 12 3 3 4 15 5 23
我如何得到如下:
id srome CumSrome 1 10 10 2 12 22 3 3 25 4 15 40 5 23 63
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum from @t t1 inner join @t t2 on t1.id >= t2.id group by t1.id, t1.SomeNumt order by t1.id
输出
| ID | SOMENUMT | SUM | ----------------------- | 1 | 10 | 10 | | 2 | 12 | 22 | | 3 | 3 | 25 | | 4 | 15 | 40 | | 5 | 23 | 63 |
编辑:这是一个适用于大多数数据库平台的通用解决方案。如果有更好的解决方案可用于您的特定平台(例如,gareth’s),请使用它!
最新版本的 SQL Server (2012) 允许以下内容。
SELECT RowID, Col1, SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2 FROM tablehh ORDER BY RowId
或者
SELECT GroupID, RowID, Col1, SUM(Col1) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2 FROM tablehh ORDER BY RowId
这甚至更快。对我来说,分区版本在 34 秒内完成了超过 500 万行。