SQL2005 执行脚本 --对表中数据逐行累加

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数量是已知，但可以是无序存储的=============


--------------------------
新闻：盖茨接班人称微软将大力推进网络战略
网站导航: 博客园首页 新闻 .NET频道 社区 博问 闪存 找找看