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

当前回答

在上面(Pre-SQL12)我们看到了这样的例子:-

SELECT
    T1.id, SUM(T2.id) AS CumSum
FROM 
    #TMP T1
    JOIN #TMP T2 ON T2.id < = T1.id
GROUP BY
    T1.id

更高效的…

SELECT
    T1.id, SUM(T2.id) + T1.id AS CumSum
FROM 
    #TMP T1
    JOIN #TMP T2 ON T2.id < T1.id
GROUP BY
    T1.id

其他回答

例如:如果你有一个有两列的表,一列是ID,第二列是number,并且想要找出累积和。

SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T

最新版本的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线程上发表了评论。

一旦创建了表-

select 
    A.id, A.SomeNumt, SUM(B.SomeNumt) as sum
    from @t A, @t B where A.id >= B.id
    group by A.id, A.SomeNumt

order by A.id

回答晚了,但显示了另一种可能性…

使用CROSS APPLY逻辑可以更好地优化累积和生成。

在分析实际的查询计划时,比INNER JOIN & OVER子句更好…

/* Create table & populate data */
IF OBJECT_ID('tempdb..#TMP') IS NOT NULL
DROP TABLE #TMP 

SELECT * INTO #TMP 
FROM (
SELECT 1 AS id
UNION 
SELECT 2 AS id
UNION 
SELECT 3 AS id
UNION 
SELECT 4 AS id
UNION 
SELECT 5 AS id
) Tab


/* Using CROSS APPLY 
Query cost relative to the batch 17%
*/    
SELECT   T1.id, 
         T2.CumSum 
FROM     #TMP T1 
         CROSS APPLY ( 
         SELECT   SUM(T2.id) AS CumSum 
         FROM     #TMP T2 
         WHERE    T1.id >= T2.id
         ) T2

/* Using INNER JOIN 
Query cost relative to the batch 46%
*/
SELECT   T1.id, 
         SUM(T2.id) CumSum
FROM     #TMP T1
         INNER JOIN #TMP T2
                 ON T1.id > = T2.id
GROUP BY T1.id

/* Using OVER clause
Query cost relative to the batch 37%
*/
SELECT   T1.id, 
         SUM(T1.id) OVER( PARTITION BY id)
FROM     #TMP T1

Output:-
  id       CumSum
-------   ------- 
   1         1
   2         3
   3         6
   4         10
   5         15

在这篇优秀的文章中有一个更快的CTE实现: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

这个线程中的问题可以这样表达:

    DECLARE @RT INT
    SELECT @RT = 0

    ;
    WITH  abcd
            AS ( SELECT TOP 100 percent
                        id
                       ,SomeNumt
                       ,MySum
                       order by id
               )
      update abcd
      set @RT = MySum = @RT + SomeNumt
      output inserted.*