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