Start with...Connect By子句的应用

/*Start with...Connect By子句的应用*/

/* 从树根往叶子递归 */
select *
from
(
    select '1' as id, '10' as name,'0' as pid from dual union all
    select '2','11','1' from dual union all
    select '3','20','0' from dual union all
    select '4','12','1' from dual union all
    select '5','121','2' from dual    
) t
start with t.pid = 0
connect by prior id = t.pid;

/* 执行结果 */

 1 10 0
 2 11 1
 5 121 2
 4 12 1
 3 20 0

/* 从叶子往树根递归 */
select *
from
(
    select '1' as id, '10' as name,'0' as pid from dual union all
    select '2','11','1' from dual union all
    select '3','20','0' from dual union all
    select '4','12','1' from dual union all
    select '5','121','2' from dual    
) t
start with t.id = 5
connect by prior pid = id;

/* 执行结果 */

5 121 2
2 11 1
1 10 0

/* 显示各节点的路径 */

select id, pid, name, substr(sys_connect_by_path(sort, '.'), 2) Path 
from
(
    select id, pid, name, row_number() over(partition by pid order by id) sort
    from
    (
        select '1' as id, '10' as name,'0' as pid from dual union all
        select '2','11','1' from dual union all
        select '3','20','0' from dual union all
        select '4','12','1' from dual union all
        select '5','121','2' from dual    
    ) t
) x
start with pid = 0
connect by prior id = pid;

/* 执行结果 */

1 0 10 1
2 1 11 1.1
5 2 121 1.1.1
4 1 12 1.2
3 0 20 2