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

当前回答

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

其他回答

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

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

例如:如果你有一个有两列的表,一列是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线程上发表了评论。

在不使用任何类型的JOIN的情况下,通过使用follow查询获取一个人的累计工资:

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

一旦创建了表-

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