rt,谁用存储过程写过同比/累计的计算方法,原表如下:
id ym value
1 200801 10
2 200802 11
3 200803 12
4 200901 20
5 200902 22
6 200903 25
实现如下结果: 当前 当期值 同期值 年累计 同年累计
id ym dq tq nlj tnlj
1 200801 10 0 10 0
2 200802 11 0 21 0
3 200803 12 0 33 0
4 200901 20 10 20 10
5 200902 22 11 42 21
6 200903 25 12 67 33高手指教
id ym value
1 200801 10
2 200802 11
3 200803 12
4 200901 20
5 200902 22
6 200903 25
实现如下结果: 当前 当期值 同期值 年累计 同年累计
id ym dq tq nlj tnlj
1 200801 10 0 10 0
2 200802 11 0 21 0
3 200803 12 0 33 0
4 200901 20 10 20 10
5 200902 22 11 42 21
6 200903 25 12 67 33高手指教
sum(a.value)over(partition by substr(a.ym,1,4) order by a.ym)nlj,
sum(nvl(b.value,0))over(partition by substr(a.ym,1,4) order by a.ym)tnlj
from tt a left join tt b
on a.ym-100=b.ym
一条查询就搞定了,一定要写过程?
--最后一个值没有出来,比较惭愧
create table tt(id int ,ym varchar(10),value int);
insert into tt select 1,200801,10 from dual
union all select 2,200802,11 from dual
union all select 3,200803,12 from dual
union all select 4,200901,20 from dual
union all select 5,200902,22 from dual
union all select 6,200903,25 from dualselect a.*,nvl((select value from tt b where add_months(to_date(a.ym,'YYYY-MM'),-12)= to_date(b.ym,'YYYY-MM')),0) as "同期值",
(select sum(value) from tt c where substr(a.ym,1,4)=substr(c.ym,1,4) and a.ym>=c.ym ) as "年累计"
from tt a