oracle遞迴（二）

Nalternative發表於2011-03-18
Oracle遞迴
WITH t (DEPART,ARRIVE,PATH,COST,LVL)
AS(
       SELECT  DEPART,
               ARRIVE,
               '/'||DEPART AS PATH,
               PRICE,
               1
       FROM FARES
       WHERE DEPART='BJ'
       UNION ALL
       SELECT  F.DEPART,
               F.ARRIVE,
               T.PATH||'/'||F.DEPART,
               T.COST+F.PRICE,
               T.LVL+1
       FROM T,FARES F
       WHERE F.DEPART=T.ARRIVE
       AND 'BJ'<>F.ARRIVE
       AND T.ARRIVE<>'SH'
       AND T.COST+F.PRICE<5000
       AND LVL<=10
       AND INSTR(T.PATH,'/'||F.DEPART)=0
)
SELECT T.PATH||'/'||T.ARRIVE PATH,T.COST
FROM T
WHERE ARRIVE='SH';
 
with t (cnt,people_left,pos,people_eliminated)
as
(
select 10,
       max(sys_connect_by_path(lpad(rownum,4),',')),
       6,
       ''
from dual
connect by rownum<=10
union all
select cnt-1,
substr(people_left,1,pos-1)||substr(people_left,pos+5),
case when pos+5          then pos+5
     when pos+5          then 1
     else 6
end,
cast(people_eliminated||substr(people_left,pos,5) as varchar2(200))
from t
where cnt>1
)
select people_left,people_eliminated from t where cnt=1;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-689836/，如需轉載，請註明出處，否則將追究法律責任。

相關文章