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';
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;
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
when pos+5
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 遍歷二叉樹-------遞迴&非遞迴二叉樹遞迴
- oracle遞迴(一)Oracle遞迴
- Oracle11gr2新增遞迴WITH語句(二)Oracle遞迴
- Oracle和Mysql遞迴OracleMySql遞迴
- ORACLE 遞迴查詢Oracle遞迴
- 【C++】翻轉二叉樹(遞迴、非遞迴)C++二叉樹遞迴
- Oracle遞迴查詢sqlOracle遞迴SQL
- 二叉樹的四種遍歷(遞迴與非遞迴)二叉樹遞迴
- 【資料結構】二叉樹遍歷(遞迴+非遞迴)資料結構二叉樹遞迴
- 遞迴和尾遞迴遞迴
- 二叉樹遞迴練習二叉樹遞迴
- 二叉樹的遞迴套路二叉樹遞迴
- Oracle with使用方法以及遞迴Oracle遞迴
- Oracle SQL的遞迴查詢OracleSQL遞迴
- oracle中的遞迴sql查詢 connect by prior實現遞迴Oracle遞迴SQL
- 遍歷二叉樹的遞迴與非遞迴程式碼實現二叉樹遞迴
- 二分法的簡單實現——-遞迴和非遞迴遞迴
- 【演算法拾遺】二分查詢遞迴非遞迴實現演算法遞迴
- 二叉樹建立及遍歷演算法(遞迴及非遞迴)二叉樹演算法遞迴
- 快速排序【遞迴】【非遞迴】排序遞迴
- 遞迴-M--二分查詢遞迴
- 二叉樹非遞迴遍歷二叉樹遞迴
- 二叉樹的前中後序遍歷(遞迴和非遞迴版本)二叉樹遞迴
- 二叉樹——後序遍歷的遞迴與非遞迴演算法二叉樹遞迴演算法
- [java] 二叉樹的後序遍歷(遞迴與非遞迴實現)Java二叉樹遞迴
- 遞迴遞迴
- python實現二叉樹及其七種遍歷方式(遞迴+非遞迴)Python二叉樹遞迴
- 什麼是遍歷二叉樹,JavaScript實現二叉樹的遍歷(遞迴,非遞迴)二叉樹JavaScript遞迴
- ACM(遞迴遞推—A)ACM遞迴
- 二叉平衡樹 python 列表 遞迴Python遞迴
- ACM(遞迴遞推—I)ACM遞迴
- JavaScript遞迴JavaScript遞迴
- go 遞迴Go遞迴
- 理解遞迴遞迴
- 遞迴 Java遞迴Java
- python 遞迴和二分法Python遞迴
- 非遞迴先序遍歷二叉樹遞迴二叉樹
- 【刷題】二叉樹非遞迴遍歷二叉樹遞迴