如何得到累计和

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

当前回答

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

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

2012-11-23 10:12:13

其他回答

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

SQL小提琴示例

输出

| ID | SOMENUMT | SUM |
-----------------------
|  1 |       10 |  10 |
|  2 |       12 |  22 |
|  3 |        3 |  25 |
|  4 |       15 |  40 |
|  5 |       23 |  63 |

编辑:这是一个通用的解决方案，可以在大多数数据库平台上工作。如果有更好的解决方案适用于你的特定平台(例如，gareth的平台)，那就使用它!

2010-01-22 21:25:19

试试这个

select 
    t.id,
    t.SomeNumt, 
    sum(t.SomeNumt) Over (Order by t.id asc Rows Between Unbounded Preceding and Current Row) as cum
from 
    @t t 
group by
    t.id,
    t.SomeNumt
order by
    t.id asc;

2013-06-28 02:12:16

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

使用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

2016-04-22 08:25:29

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

2012-11-11 12:25:37