oracle 时间按半小时递归,oracle递归（一）

《剑破冰山》一书中例子

select e.empno,

e.ename,

e.job,

e.mgr,

e.deptno,

level,

sys_connect_by_path(ename,'\') as path,

connect_by_root(ename) as top_manager

from emp e

start with mgr is null

connect by prior empno=mgr;

/

--新的递归方法

with t(empno,ename,job,mgr,deptno,the_level,path,top_manager) as(

select e.empno,

e.ename,

e.job,

e.mgr,

e.deptno,

1 as the_level,

'\'||ename,

ename as top_manager

from emp e

where e.mgr is null

union all

select e.empno,

e.ename,

e.job,

e.mgr,

e.deptno,

1+t.level,

t.pathe||'\'||ename,

t.top_manager

from t.emp e

where t.empno=e.mgr

)

select * from t;

--求和100的组合

with coins as(

select 1 cents from dual

union all

select 5 cents from dual

union all

select 10 cents from dual

union all

select 25 cents from dual

union all

select 50 cents from dual

),

t(current_coin,total_val,c1,c5,c10,c25,c50)

as

(

select 0,0,0,0,0,0,0 from dual

union all

select c.cents,

t.total_val+c.cents,

c1+decode(c.cents,1,1,0),

c5+decode(c.cents,5,1,0),

c10+decode(c.cents,10,1,0),

c25+decode(c.cents,25,1,0),

c50+decode(c.cents,50,1,0)

from t,coins c

where t.total_val+c.cents<=100

and

t.current_coin<=c.cents

)

select '1*'||c1||'+5*'||c5||'+10*'||c10||'+25*'||c25||'+50*'||c50 as result

from t

where total_val=100;

--邮票例子：

with t(lvl,val,left_4,left_3,result)

as

(

select 0,0,2,3,'' from dual

union all

select t.lvl+1,

t.val+s.price*cnt,

left_4-decode(cnt,4,1,0),

left_3-decode(cnt,3,1,0),

t.result||' '||s.price||'*'||cnt

from   t,

(select rownum price_id,decode(rownum,4,5,5,10,rownum) as price from dual connect by rownum<=5) s,

(select 4 as cnt from dual union all select 3 from dual)

where t.lvl+1=s.price_id

and (cnt=4 and left_4>0 or cnt=3 and left_3>0)

)

select result||'='||val

from t

where lvl=5 and mod(val,10)=0