create table ccc
(a int,
b int);
insert into ccc values (10,null);
insert into ccc values (20,10);
insert into ccc values (30,10);
insert into ccc values (40,20);
insert into ccc values (50,20);
insert into ccc values (60,30);
insert into ccc values (70,50);
SELECT * from ccc;
--带序号的树形结构
SELECT T.*,
SUBSTR(SYS_CONNECT_BY_PATH(LV, '.'), 2) ALV,
CONNECT_BY_ROOT(LV) RT
FROM (SELECT B.*, ROW_NUMBER() OVER(PARTITION BY B.B ORDER BY B.RN) LV
FROM (SELECT A.*, ROWNUM RN FROM CCC A) B) T
START WITH T.B IS NULL
CONNECT BY PRIOR T.a = T.b
思路是利用分析函数根据父节点分组排序,获取叶子几点,再利用sys_connect_by_path 来获取叶子节点的所有上层节点。这样就获得的目录结构。