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

当前回答

在不使用任何类型的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

其他回答

在这篇优秀的文章中有一个更快的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 
    *, 
    (Select Sum(SOMENUMT) 
     From @t S 
     Where S.id <= M.id)
From @t M

对于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)

试试这个:

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

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

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