Oracle 层次化查询的一个小例子
今天在ITPUB看到的题目2、有如下信息：
起始地目的地距离（公里）
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发，可达到的目的地（包括间接方式）。
创建表test
SQL> select * from test;
START_PLACE END_PLACE DISTANCE
----------- ---------- ----------
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
select start_place, end_place
from (select * from testwhere end_place <> 'A')
start with start_place = 'A'
connect by prior end_place = start_place;
START_PLACE END_PLACE
----------- ----------
A C
C B
B D
D F
F G
A D
D F
F G
A E
这些条目中的END_PLACE就是所有可以从A到达的点
connect by prior 实现树状查询 connect by prior 左边的为父接点，右边为自接点。
oracle还提供函数sys_connect_by_path（）可以把路径表示出来如下面语句
select sys_connect_by_path(start_place, '->')||'->'||end_place
from (select * from duanjw where end_place <> 'A')
start with start_place = 'A'
CONNECT BY PRIOR end_place = start_place;
SYS_CONNECT_BY_PATH(START_PLAC
--------------------------------------------------------------------------------
->A->C
->A->C->B
->A->C->B->D
->A->C->B->D->F
->A->C->B->D->F->G
->A->D
->A->D->F
->A->D->F->G
->A->E
这样看起来就清楚了很多
好例子:) select start_place, end_place
from (select * from testwhere end_place <> 'A')
start with start_place = 'A'
connect by prior end_place = start_place;
这个里面应该在connect by后面加一个nocycle，
否则执行会报ORA-01436：CONNECT BY loop in user data
的错误。
学习了。 这个题非常好，谢谢分享！:D
页:
[1]