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

当前回答

对于SQL Server 2012以后,它可以很容易:

SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t

因为SUM的ORDER BY子句默认表示窗口框的前一行和当前行范围为无界(“一般备注”在https://msdn.microsoft.com/en-us/library/ms189461.aspx)

其他回答

一个CTE版本,只是为了好玩:

;
WITH  abcd
        AS ( SELECT id
                   ,SomeNumt
                   ,SomeNumt AS MySum
             FROM   @t
             WHERE  id = 1
             UNION ALL
             SELECT t.id
                   ,t.SomeNumt
                   ,t.SomeNumt + a.MySum AS MySum
             FROM   @t AS t
                    JOIN abcd AS a ON a.id = t.id - 1
           )
  SELECT  *  FROM    abcd
OPTION  ( MAXRECURSION 1000 ) -- limit recursion here, or 0 for no limit.

返回:

id          SomeNumt    MySum
----------- ----------- -----------
1           10          10
2           12          22
3           3           25
4           15          40
5           23          63

最新版本的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

or

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 Team线程上发表了评论。

SQL解决方案结合“无界前行和当前行之间的行”和“和”做的正是我想要实现的。 非常感谢!

如果这能帮到谁,这是我的案子。我想在一列中累积+1,每当发现一个maker为“Some maker”(示例)。如果不是,则不增加,但显示之前的增加结果。

这段SQL:

SUM( CASE [rmaker] WHEN 'Some Maker' THEN  1 ELSE 0 END) 
OVER 
(PARTITION BY UserID ORDER BY UserID,[rrank] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul_CNT

让我得到这样的东西:

User 1  Rank1   MakerA      0  
User 1  Rank2   MakerB      0  
User 1  Rank3   Some Maker  1  
User 1  Rank4   Some Maker  2  
User 1  Rank5   MakerC      2
User 1  Rank6   Some Maker  3  
User 2  Rank1   MakerA      0  
User 2  Rank2   SomeMaker   1  

上面的解释:它从0开始计数“some maker”,some maker被找到,我们做+1。对于用户1,MakerC被找到,所以我们不做+1,而是一些制造商的垂直计数被固定为2,直到下一行。 分区是按用户划分的,所以当我们改变用户时,累积计数返回零。

我在工作,我不希望这个答案有任何优点,只是说谢谢,并以身作则,以防有人处于同样的情况。我试图结合SUM和PARTITION,但惊人的语法“无界前行和当前行之间的行”完成了任务。

谢谢! Groaker

你可以使用这个简单的查询进行累进计算:

select 
   id
  ,SomeNumt
  ,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t

让我们先用虚拟数据创建一个表:

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的somvalue相加,我们就会得到答案

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