oracle树形父子关系递归检索

1、初始化数据

id   parent_id

 1 
 2        1
 3        1
 4        2
 5        2
 6        4
 7        4
 8 
 9        8

create table connect_table (ID int, PARENT_ID int);

insert into connect_table (ID, PARENT_ID)
values (1, null);

insert into connect_table (ID, PARENT_ID)
values (2, 1);

insert into connect_table (ID, PARENT_ID)
values (3, 1);

insert into connect_table (ID, PARENT_ID)
values (4, 2);

insert into connect_table (ID, PARENT_ID)
values (5, 2);

insert into connect_table (ID, PARENT_ID)
values (6, 4);

insert into connect_table (ID, PARENT_ID)
values (7, 4);

insert into connect_table (ID, PARENT_ID)
values (8, null);

insert into connect_table (ID, PARENT_ID)
values (9, 8);

2、分析

    这个表是2棵树，2个根节点分别是 id=1 or id =8

    connect by prior id=parent_id   -----自根节点开始便利到叶子节点

    start with id =?  ----选一个根，则查询出该root的所有分支和叶子

3、适用场合举例

      查询id=8是否为id=1的分支或叶子，是返回y，否返回n。

      select t.*, level from connect_table t connect by prior id =parent_id start with id=1;  ---显示level

select decode(count(1), 0, 'n', 'y')
  from dual
 where 8 in (select id
               from connect_table t
             connect by prior id = parent_id
              start with id =1);