ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle递归(二)

oracle递归(二)

原创 Linux操作系统 作者:Nalternative 时间:2011-03-18 11:08:29 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: vmware安装
请登录后发表评论 登录
全部评论

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    174439