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
上面的select返回给我以下内容。
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
发布于 2012-11-11 20:25:38
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万行。
感谢Peso,他在另一个答案中提到的SQL团队线程上发表了评论。
发布于 2015-09-01 04:08:52
对于SQL Server 2012及更高版本,这可能很简单:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t
因为默认情况下,SUM
的ORDER BY
子句表示窗口框架的RANGE UNBOUNDED PRECEDING AND CURRENT ROW
(https://msdn.microsoft.com/en-us/library/ms189461.aspx中的“一般备注”)
发布于 2012-11-23 18:12:13
让我们首先创建一个包含虚拟数据的表:
Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)
现在,让我们向表中插入一些数据;
Insert Into CUMULATIVESUM
Select 1, 10 union
Select 2, 2 union
Select 3, 6 union
Select 4, 10
这里我正在连接同一个表(自连接)
Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc
结果:
ID SomeValue SomeValue
-------------------------
1 10 10
2 2 10
2 2 2
3 6 10
3 6 2
3 6 6
4 10 10
4 10 2
4 10 6
4 10 10
现在,我们只需将t2的索姆值相加,我们就会得到答案:
Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Group By c1.ID, c1.SomeValue
Order By c1.id Asc
对于SQL Server 2012及更高版本(更好的性能):
Select
c1.ID, c1.SomeValue,
Sum (SomeValue) Over (Order By c1.ID )
From CumulativeSum c1
Order By c1.id Asc
期望的结果:
ID SomeValue CumlativeSumValue
---------------------------------
1 10 10
2 2 12
3 6 18
4 10 28
Drop Table CumulativeSum
https://stackoverflow.com/questions/2120544
复制相似问题