# 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
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

• 博文量
123
• 访问量
178289