数据库是DB2
有一张表
year month num cancelnum
2009 11 25 3
2009 12 45 5
2010 03 20 10
2010 07 80 100
2011 05 20 5如何写SQL能计算num和cancelnum的累加值,预期结果:
year month num cancelnum
2009 11 25 3
2009 12 70 8
2010 03 90 18
2010 07 170 118
2011 05 190 123谢谢
有一张表
year month num cancelnum
2009 11 25 3
2009 12 45 5
2010 03 20 10
2010 07 80 100
2011 05 20 5如何写SQL能计算num和cancelnum的累加值,预期结果:
year month num cancelnum
2009 11 25 3
2009 12 70 8
2010 03 90 18
2010 07 170 118
2011 05 190 123谢谢
with t as (
select '2009' year,'11' month,'25' num,'3' cancelnum from dual
union all
select '2009' year,'12' month,'45' num,'5' cancelnum from dual
union all
select '2010' year,'03' month,'20' num,'10' cancelnum from dual
union all
select '2010' year,'07' month,'80' num,'100' cancelnum from dual
union all
select '2011' year,'05' month,'20' num,'5' cancelnum from dual
)
select t.year,t.month,t.num,t.cancelnum,sum(num) over(order by t.year,t.month),sum(t.cancelnum) over(order by t.year,t.month)
from t
with t1 as (
select 2009 year,11 month,25 num,3 cancelnum from dual
union all
select 2009 year,12 month,45 num,5 cancelnum from dual
union all
select 2010 year,03 month,20 num,10 cancelnum from dual
union all
select 2010 year,07 month,80 num,100 cancelnum from dual
union all
select 2011 year,05 month,20 num,5 cancelnum from dual
)select year,
lpad(month,2,0) month,
sum(num) over(order by year,month) s_num,
sum(cancelnum) over(order by year,month) s_cancelnum
from t1 year month s_num s_cancelnum
---------------------------------------
1 2009 11 25 3
2 2009 12 70 8
3 2010 03 90 18
4 2010 07 170 118
5 2011 05 190 123
oracle分析函数
WITH t AS(
SELECT '2009'YEAR,'11'MONTH,25 num,3 cancelnum FROM dual
UNION ALL
SELECT '2009','12',45,5 FROM dual
UNION ALL
SELECT '2010','03',20,10 FROM dual
UNION ALL
SELECT '2010','07',80,100 FROM dual
UNION ALL
SELECT '2011','05',20,5 FROM dual
)
SELECT
YEAR,
MONTH,
sum(num) over (ORDER BY YEAR,month)num,
Sum(cancelnum) over (ORDER BY YEAR,MONTH)cancelnum
FROM t