年份 月份 水量
1999 1 1
2000 2 1.5
2000 3 0.1
2000 4 1
2000 5 1
结果:年份 某年前N个月份 水量
1999 1 1
1999 2 2.5
1999 3 2.6
....
1999 1 1
2000 2 1.5
2000 3 0.1
2000 4 1
2000 5 1
结果:年份 某年前N个月份 水量
1999 1 1
1999 2 2.5
1999 3 2.6
....
(
select 1999 y , 1 m, 1 cnt FROM DUAL UNION ALL
select 1999 y , 2 m, 1.5 cnt FROM DUAL UNION ALL
select 1999 y , 3 m, 0.1 cnt FROM DUAL UNION ALL
select 1999 y , 4 m, 1 cnt FROM DUAL UNION ALL
select 1999 y , 5 m, 1 cnt FROM DUAL
)
select t.* , (select sum(n.cnt) from tb n where to_char(n.y) + lpad(to_char(n.m),2,'0') <= to_char(t.y) + lpad(to_char(t.m),2,'0')) cnt from tb t/*
Y M CNT CNT
---------- ---------- ---------- ----------
1999 1 1 1
1999 2 1.5 2.5
1999 3 .1 2.6
1999 4 1 3.6
1999 5 1 4.65 rows selected.
*/
(
select 1999 y , 1 m, 1 cnt FROM DUAL UNION ALL
select 1999 y , 2 m, 1.5 cnt FROM DUAL UNION ALL
select 1999 y , 3 m, 0.1 cnt FROM DUAL UNION ALL
select 1999 y , 4 m, 1 cnt FROM DUAL UNION ALL
select 1999 y , 5 m, 1 cnt FROM DUAL
)
select y,m,sum(cnt) over (partition by y order by m) cnt_sum
from tb
order by y,m;