给定一个树，要求查询所有状态为"异常"的叶子节点的所有上级节点

create table t as select employee_id id,manager_id pid ,'正常' status from hr.employees;

update t set status='异常' where id in ('109','107');

查询表数据如下.因为节点109,107状态为异常，所以需要查他们的所有上级节点，即100,101,108,102,103

SQL> select sys_connect_by_path(id,'/') path,status from t start with pid is null connect by pid=prior id;

PATH STAT

-------------------- ----

/100 正常

/100/101 正常

/100/101/108 正常

/100/101/108/109 异常

/100/101/108/110 正常

/100/101/108/111 正常

/100/101/108/112 正常

/100/101/108/113 正常

/100/101/200 正常

/100/101/203 正常

/100/101/204 正常

/100/101/205 正常

/100/101/205/206 正常

/100/102 正常

/100/102/103 正常

/100/102/103/104 正常

/100/102/103/105 正常

/100/102/103/106 正常

/100/102/103/107 异常

/100/114 正常

/100/114/115 正常

/100/114/116 正常

/100/114/117 正常

....以下内容省略

已选择107行。

外连接方法
    1.查询状态为异常的叶子节点
2.查询所有非叶子节点(他们拥有子节点)
3.不等连接

select distinct t2.pid from (

    select id,pid,status,sys_connect_by_path(id,':') path from t

    where connect_by_isleaf=1 and status='异常'

    start with pid is null connect by pid=prior id) t1

inner join (

    select distinct pid from t where pid is not null) t2

on instr(t1.path,':'||t2.pid)!=0;

视图t1的数据

ID PID STAT PATH

--- ---------- ---- ----------------

109 108 异常 :100:101:108:109

107 103 异常 :100:102:103:107

树遍历方法
    1.找到所有状态为异常的叶子节点
2.自底向上遍历

with v1 as(

    select pid from t where connect_by_isleaf=1 and status='异常' start with pid is null connect by pid=prior id

)

select distinct t.id from t start with t.id in (select v1.pid from v1) connect by prior pid=id;

ITPub博客

维护树状数据

最新文章