如何求累计和

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 Fiddle example

产量

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

编辑: 这是一个通用的解决方案,适用于大多数数据库平台。当您的特定平台(如Gareth)有更好的解决方案时,请使用它!

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

对于SQL Server 2012以后的版本,这可能很容易:

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

因为 ORDER BY 条款 SUM 默认方式 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 窗框(“一般说明”见 https://msdn.microsoft.com/en-us/library/ms189461.aspx )

让我们首先使用虚拟数据创建表-->

Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)

**Now let put some data in the table**

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的某个值求和,得到ans

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

清除Dummytable

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

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

使用 CROSS APPLY 逻辑。

比 INNER JOIN 和; OVER Clause 当分析实际查询计划时…

/* 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

Select *, (Select SUM(SOMENUMT) From @t S Where S.id <= M.id) From @t M

在这篇优秀的文章中有一个更快的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.*

创建表后-

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

上面(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

试试这个

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;

试试这个:

CREATE TABLE #t(
 [name] varchar NULL,
 [val] [int] NULL,
 [ID] [int] NULL
) ON [PRIMARY]

insert into #t (id,name,val) values
 (1,'A',10), (2,'B',20), (3,'C',30)

select t1.id, t1.val, SUM(t2.val) as cumSum
 from #t t1 inner join #t t2 on t1.id >= t2.id
 group by t1.id, t1.val order by t1.id

sql解决方案将“前一行和当前行之间的行”和“和”结合起来,实现了我想要的目标。 非常感谢!

如果能帮上忙的话,这是我的案子。每当一个制造者被发现是“某个制造者”(示例)时,我想在一个列中累积+1。如果不是,则不显示增量,而是显示以前的增量结果。

所以这段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,但是一些maker的垂直计数在下一行之前被固定在2。 分区是按用户进行的,所以当我们更改用户时,累计计数会回到零。

我在工作,我不想在这个答案上有任何价值,只要说谢谢,并展示我的例子,以防有人在同样的情况下。我试着把sum和partition结合起来,但是令人惊奇的语法“rows between unbounded preceding and current row”完成了任务。

谢谢! 探索者

不使用任何类型的联接累积薪资,而是使用以下查询获取人员:

SELECT * , (
  SELECT SUM( salary ) 
  FROM  `abc` AS table1
  WHERE table1.ID <=  `abc`.ID
    AND table1.name =  `abc`.Name
) AS cum
FROM  `abc` 
ORDER BY Name