想实现如下累计求和,求指点:时间 值
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语句如何写???
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语句如何写???
FROM (SELECT T.A_TIME,T.A_VALUE,ROW_NUMBER() OVER (ORDER BY T.A_TIME) AS ROW FROM TEST T) T1
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',400select *,累计值=(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
*/
(
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 行受影响)
*/
(
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
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
(
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 行受影响)
*/
(
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
(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
(
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
*/
随着版本的进化,有了更多的方法来实现,而且更加简洁,希望sql server能支持更多的开窗函数,再增多30个开窗函数,达到oracle的功能。