--带序号的树形结构

类似world的目录结构

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 NULLCONNECT BY PRIOR T.a = T.b思路是利用分析函数根据父节点分组排序，获取叶子几点，再利用sys_connect_by_path 来获取叶子节点的所有上层节点。这样就获得的目录结构。