因业务需要查询实现第二行的数据为第一行加上第二行的值来处理,写下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数量是已知,但可以是无序存储的=============