对表中数据逐行累加

最新推荐文章于 2025-02-09 19:24:43 发布

转载最新推荐文章于 2025-02-09 19:24:43 发布 · 157 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/furenjun/archive/2009/05/01/oneRowByOneRowAdd.html

文章标签：

#数据库

对表中数据逐行累加

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