【求助】SQL 累计求和问题

[灵雨飘零]

想实现如下累计求和，求指点：

时间 值
20120101 200
20121121 212
20120104 100
20120411 210
20120501 100
20120401 200
20120104 400

实现如下效果：（只考虑时间排序，不考虑值排序）
时间 值 累计值
20120101 200 200
20120104 100 300
20120104 400 700
20120401 200 900
20120411 210 1110
20120501 100 1210
20121121 212 1422

请问SQL语句如何写？？？

[灵雨飘零]

自己解决了，嘎嘎！！！！！！

[tcmakebest]

问题的解决方案很多，这个功能用高级语言解决是最容易的。

[灵雨飘零]

参考答案：

SELECT T1.A_TIME,T1.A_VALUE,(SELECT SUM(T2.A_VALUE) FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T2 WHERE T2.ROW<=T1.ROW ) AS LJ
FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T1

[Andy__Huang]

create table #tb(时间 varchar(10),值 int)
insert into #tb
select '20120101',200
union all select '20121121',212
union all select '20120104',100
union all select '20120411',210
union all select '20120501',100
union all select '20120401',200
union all select '20120104',400

select *,累计值=(select sum(值) from (select *,rn=row_number() over(order by 时间) from #tb) b where b.rn<=a.rn)
from 
(
select *,rn=row_number() over(order by 时间) from #tb
)a
order by 时间

/*
时间   值  累计值
-----------------------
20120101	200	200
20120104	100	300
20120104	400	700
20120401	200	900
20120411	210	1110
20120501	100	1210
20121121	212	1422
*/

[feisheng512]

lz 还是简单点好，想太复杂了你。。。

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM a WHERE tim<=b.tim) AS lj FROM a b 
ORDER BY b.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         700
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/

[feisheng512]

改进了下，之前疏忽

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
),b AS
(
	SELECT *,ROW_NUMBER() OVER( ORDER BY tim) AS rownum FROM a
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM b WHERE rownum<=c.rownum) AS lj FROM b c 
ORDER BY c.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         300
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/

[北极海hein]

drop table #tb
select 时间,值,null as total into #tb from tablename order by 时间

declare @Total int
set @Total=0
update #tb set total=@Total,@Total=@Total+值

select 时间,值,total from #tb order by 时间

[灵雨飘零]

引用 5 楼 feisheng512 的回复:

lz 还是简单点好，想太复杂了你。。。

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)
SELECT tim,val,(SELECT SUM(ISNULL(val,0)) FROM a WHERE tim<=b.tim) AS lj FROM a b 
ORDER BY b.tim
/*
tim         val         lj
----------- ----------- -----------
20120101    200         200
20120104    100         700
20120104    400         700
20120401    200         900
20120411    210         1110
20120501    100         1210
20121121    212         1422

(7 行受影响)
*/

你的结果不对哦！是你想的太简单了。

[ai_li7758521]

WITH TB AS
(
SELECT 20120101 AS [Time],200 AS [Value] UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
)


SELECT a.[Time],a.[Value],SUM(b.[Value]) AS [SUM] 
FROM TB a JOIN TB b ON a.[Time]>=b.[Time]
GROUP BY a.[Time],a.[Value]
ORDER BY a.[Time]

--Time	Value	SUM
--20120101	200	200
--20120104	100	700
--20120104	400	700
--20120401	200	900
--20120411	210	1110
--20120501	100	1210

[ai_li7758521]

引用 7 楼 ai_li7758521 的回复:

WITH TB AS ( SELECT 20120101 AS [Time],200 AS [Value] UNION ALL SELECT 20121121, 212 UNION ALL SELECT 20120104 , 100 UNION ALL SELECT 20120411 , 210 UNION ALL SELECT 20120501 , 100 UNION ALL SELECT 20120401 , 200 UNION ALL SELECT 20120104 , 400 ) ...

有误， WITH T AS ( SELECT 20120101 AS [Time],200 AS [Value] UNION ALL SELECT 20121121, 212 UNION ALL SELECT 20120104 , 100 UNION ALL SELECT 20120411 , 210 UNION ALL SELECT 20120501 , 100 UNION ALL SELECT 20120401 , 200 UNION ALL SELECT 20120104 , 400 ),TB AS ( SELECT *,rn=ROW_NUMBER() OVER(ORDER BY [Time]) FROM T )

SELECT a.[Time],a.[Value],SUM(b.[Value]) AS [SUM] 
FROM TB a JOIN TB b ON a.rn>=b.rn
GROUP BY a.[Time],a.[Value]
ORDER BY a.[Time]

--Time	Value	SUM
--20120101	200	200
--20120104	100	300
--20120104	400	700
--20120401	200	900
--20120411	210	1110
--20120501	100	1210
--20121121	212	1422

[Cloud_Hero]

楼主的意思，是逐行插入数据，同时新增一列作为累计值？ 还是已经存在一张表，直接输出到查询？

[雾岛心情]

都特码高手啊

[hunkwangshz]

SQL2012 with t as ( select 1 as col,timestr,val from myTable1 ) SELECT timestr,val, SUM(val) OVER(PARTITION BY col ORDER BY timestr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM t

[唐诗三百首]

引用 3 楼 kingboy2008 的回复:

参考答案：

SELECT T1.A_TIME,T1.A_VALUE,(SELECT SUM(T2.A_VALUE) FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T2 WHERE T2.ROW<=T1.ROW ) AS LJ
FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T1

应该可以简化如下,

with t as
(SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW 
 FROM TEST T)
SELECT T1.A_TIME,T1.A_VALUE,
      (SELECT SUM(T2.A_VALUE) FROM t T2 WHERE T2.ROW<=T1.ROW) AS LJ
FROM t T1

[奔四在望]

学习了，很好。。

[LongRui888]

方法很多：

WITH a AS
(
SELECT 20120101 AS tim,200 AS val UNION ALL
SELECT 20121121, 212 UNION ALL
SELECT 20120104 ,   100 UNION ALL
SELECT 20120411  ,  210 UNION ALL
SELECT 20120501  ,  100 UNION ALL
SELECT 20120401 ,   200 UNION ALL
SELECT 20120104 ,   400
),

aa
as
(
select a.*,
       ROW_NUMBER() over(order by tim) as rownum
from a
)

select a1.tim,
       sum(a2.val) as cume_val
from aa a1
inner join aa a2
        on a1.rownum >= a2.rownum
group by a1.tim,
         a1.rownumtim	cume_val

/*
20120101	200
20120104	300
20120104	700
20120401	900
20120411	1110
20120501	1210
20121121	1422
*/

[LongRui888]

引用 14 楼 hunkwangshz 的回复:

SQL2012 with t as ( select 1 as col,timestr,val from myTable1 ) SELECT timestr,val, SUM(val) OVER(PARTITION BY col ORDER BY timestr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM t

随着版本的进化，有了更多的方法来实现，而且更加简洁，希望sql server能支持更多的开窗函数，再增多30个开窗函数，达到oracle的功能。