# ORACLE冒泡算法与递归（阶乘）-----妙用~

8/19的是（1+（-0.0007））*（1+（-0.0004））

8/20的是（1+（-0.0007））*（1+（-0.0020））*（1+（-0.0014））

8/23的是（1+（-0.0007））*（1+（-0.0020））*（1+（0.0003））*（1+（-0.0012））

create table cluser

(scode number,

sdate date,

daily number(2,4),

rmonth number(2,4));

insert into cluser

values(396,to_date('2010-8-19','yyyy-mm-dd'),-0.0007,-0.0004);

insert into cluser

values(396,to_date('2010-8-20','yyyy-mm-dd'),-0.0020,-0.0014);

insert into cluser

values(396,to_date('2010-8-23','yyyy-mm-dd'),0.0003,-0.0012);

insert into cluser

values(396,to_date('2010-8-24','yyyy-mm-dd'),0.0008,-0.0008);

insert into cluser

values(396,to_date('2010-8-25','yyyy-mm-dd'),-0.0018,-0.0017);

insert into cluser

values(396,to_date('2010-8-26','yyyy-mm-dd'),0.0002,-0.0016);

commit;

with a as

(select scode,sdate,daily,rmonth,row_number() over(order by sdate) rown from cluser),

b as

(select dbms_aw.eval_number(1||sys_connect_by_path(1+daily,'*')) suma,level rlev

from a

a.rown = 1

connect by prior

a.rown+1 = a.rown)

select a.scode,a.sdate,a.daily,a.rmonth,a.rown,

b.suma,b.suma*(1+a.rmonth) as jlev

from a,b

where a.rown =  b.rlev

posted @ 2012-12-27 13:17 medci(卡樂江) 阅读(...) 评论(...) 编辑 收藏