【求助】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语句如何写？？？
[解决办法]
问题的解决方案很多，这个功能用高级语言解决是最容易的。
[解决办法]
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 时间
/*
时间 值 累计值
-----------------------
20120101200200
20120104100300
20120104400700
20120401200900
201204112101110
201205011001210
201211212121422
*/
[解决办法]
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 行受影响)
*/
[解决办法]
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]
--TimeValueSUM
--20120101200200
--20120104100700
--20120104400700
--20120401200900
--201204112101110
--201205011001210
[解决办法]
有误，
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]
--TimeValueSUM
--20120101200200
--20120104100300
--20120104400700
--20120401200900
--201204112101110
--201205011001210
--201211212121422
[解决办法]
改进了下，之前疏忽
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 行受影响)
*/
[解决办法]
应该可以简化如下,
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
[解决办法]
方法很多：
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.rownumtimcume_val
/*
20120101200
20120104300
20120104700
20120401900
201204111110
201205011210
201211211422
*/