SQL逐行累加实现

因业务需要查询实现第二行的数据为第一行加上第二行的值来处理，写下SQL语句，特记录如下，以备后用！

 

select a.id, sum(b.a) as b
      from   tt as a, tt as b
      where  a.id>=b.id
      group by a.id

 

说明tt为表名，id为排序名,a为数值

 

 IDENTITY(INT,1,1)为自增

网上找的资料如下:

--对表中数据逐行累加

declare  @tempTable table(SID   int,  SCORE   int)
insert   @tempTable
select   1,                 10   union   all
select   2,                 20   union   all
select   3,                 30   union   all
select   4,                 40   union   all
select   5,                 50
--查看添加的数据
select * from @tempTable
drop table temptable
select * into tempTable from @tempTable
--=====================================================
--1.使用子查询来计算累加和（非常好的一个方法)
--=====================================================

SELECT
TB1.SID,
SUM(TB2.SCORE) SCORE
FROM
tempTable TB1, (SELECT SID, SCORE
                FROM TempTable
                )TB2
WHERE
TB1.SID >= TB2.SID
GROUP BY TB1.SID

--======================================
SELECT SID,
SUM(SCORE) AS SCORE,
         (
            SELECT SUM(SCORE)
            FROM TempTable
            WHERE (SID <= A.SID)
        )
        AS [SUM_SCORE]
FROM TempTable AS A
GROUP BY SID
ORDER BY SID


--======================================
--2.通过更新的方法实现
--======================================
--声明变量
declare   @num   int,@SID   int
set   @num   =   0
--开始更新,注意SQL执行更新时，是一行行更新数据.
update   @tempTable
set   @num   =   case   when   @SID   <=   SID   then     @num   +   SCORE   else   SCORE   end,
      @SID   =   SID,
      SCORE   =   @num
--查看更新后的结果
select   *   from   @tempTable

--===========注意应用此方法时，SID是有序存储的===================


--======================================
--3.通过查询的方法实现
--======================================
select
sum (case  when sid<=1 then score end) as S1,
sum (case  when sid<=2 then score end) as S2,
sum (case  when sid<=3 then score end) as S3,
sum (case  when sid<=4 then score end) as S4,
sum (case  when sid<=5 then score end) as S5
from tempTable

--===========注意应用此方法时，SID数量是已知，但可以是无序存储的=============