这样试试(类似语句):select id,ltrim(sys_connect_by_path(name,'/'),'/') namepath from xxx_tab start with pid is null connect by prior id=pid;输出: id namepath 001 aaa 002 aaa/bbb 003 aaa/bbb/ccc 004 aaa/ddd ...
做了个小例子,参考一下吧 :)SQL> create table Eqp_s_User_Assistant_Sort (User_Sort_No number,Parent_No number);Table createdSQL> insert into Eqp_s_User_Assistant_Sort values(1,null);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(2,1);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(3,1);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(4,2);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(5,2);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(6,3);1 row insertedSQL> insert into Eqp_s_User_Assistant_Sort values(7,3);1 row insertedSQL> commit;Commit completeSQL> SELECT User_Sort_No, 2 Parent_No, 3 Substr(ltrim(sys_connect_by_path(Parent_No, '/'), '/'), 4 1, 5 Instr(ltrim(sys_connect_by_path(Parent_No, '/'), '/'), 6 '/', 7 1, 8 1) - 1) Parent_Parent_No 9 from Eqp_s_User_Assistant_Sort 10 where level = 3 11 connect by prior User_Sort_No = Parent_No 12 start with Parent_No is null;USER_SORT_NO PARENT_NO PARENT_PARENT_NO ------------ ---------- ------------------------------------------------------------------ 4 2 1 5 2 1 6 3 1 7 3 1
from xxx_tab start with pid is null connect by prior id=pid;输出:
id namepath
001 aaa
002 aaa/bbb
003 aaa/bbb/ccc
004 aaa/ddd
...
2 Parent_No,
3 Substr(ltrim(sys_connect_by_path(Parent_No, '/'), '/'),
4 1,
5 Instr(ltrim(sys_connect_by_path(Parent_No, '/'), '/'),
6 '/',
7 1,
8 1) - 1) Parent_Parent_No
9 from Eqp_s_User_Assistant_Sort
10 where level = 3
11 connect by prior User_Sort_No = Parent_No
12 start with Parent_No is null;USER_SORT_NO PARENT_NO PARENT_PARENT_NO
------------ ---------- ------------------------------------------------------------------
4 2 1
5 2 1
6 3 1
7 3 1