CREATE TABLE treeTEST(master VARCHAR2(10),SUB VARCHAR2(10),SCALE NUMBER);
insert into treeTEST values ('1', '2', 15);
insert into treeTEST values ('1', '3', 20);
insert into treeTEST values ('2', '4', 5);
insert into treeTEST values ('2', '5', 10);
insert into treeTEST values ('3', '5', 30);
insert into treeTEST values ('3', '6', 40);
COMMIT;
SELECT * FROM TREETEST
MASTER SUB SCALE
1 2 15
1 3 20
2 4 5
2 5 10
3 5 30
3 6 40
如果用树型结构表示如下:
'1'
-'2'
--'4'
--'5'
'1'
-'3'
--'5'
--'6'
select *
from treeTEST start with sub = '2' --相当于普通sql的where条件
connect by prior master = sub;
MASTER SUB SCALE
1 2 15
select *
from treeTEST start with sub = '2' --相当于普通sql的where条件
connect by sub =prior master;
MASTER SUB SALES
1 2 15
select *
from treeTEST start with sub = '6' --相当于普通sql的where条件
connect by sub =prior master;
MASTER SUB SALES
3 6 40
1 3 20
select *
from treeTEST start with sub = '5' --相当于普通sql的where条件
connect by sub =prior master;
MASTER SUB SCALE
2 5 10
1 2 15
3 5 30
1 3 20
select * from treeTEST
start with master='2'
connect by prior master=sub; --sub往上遍历至根节点
MASTER SUB SCALE
2 4 5
1 2 15
2 5 10
1 2 15
select sys_connect_by_path(MASTER,'->') from treeTEST 以->为分割符
start with master='2' --master表示我遍历的起点为master=2
connect by prior master=sub;
SYS_CONNECT_BY_PATH(MASTER,'->
->2
->2->1
->2
->2->1
select sub,master,sys_connect_by_path(MASTER,'->') from treetest
start with sub='5' --起点为sub='5',向根便利
connect by prior master=sub;
SUB MASTER SYS_CONNECT_BY_PATH(MASTER,'->
5 2 ->2
2 1 ->2->1
5 3 ->3
3 1 ->3->1
从sub开始向主遍历:
select sub, master, sys_connect_by_path(MASTER, '->')
from treetest start with sub is not null --遍历的起点
connect by prior master = sub;
SUB MASTER SYS_CONNECT_BY_PATH(MASTER,'->
2 1 ->1
3 1 ->1
4 2 ->2
2 1 ->2->1
5 2 ->2
2 1 ->2->1
5 3 ->3
3 1 ->3->1
6 3 ->3
3 1 ->3->1
从主开始向sub遍历:
select master, sub, sys_connect_by_path(sub, '->')
from treetest start with master is not null --遍历的起点
connect by prior sub = master;
MASTER SUB SYS_CONNECT_BY_PATH(SUB,'->')
1 2 ->2
2 4 ->2->4
2 5 ->2->5
1 3 ->3
3 5 ->3->5
3 6 ->3->6
2 4 ->4
2 5 ->5
3 5 ->5
3 6 ->6
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-891303/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/36779/viewspace-891303/