ORACLE冒泡算法与递归(阶乘)-----妙用~

昨天群里有人问一个问题,想了一下,今天上午没事,准备了一下,然后PO出来分享一下。

问题是有一张表数据如下,

 

然后我要找到每一条SDATE对应的一个阶乘,如:

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

start with

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(卡樂江) 阅读(...) 评论(...) 编辑 收藏