维护树状数据

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

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博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-777280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-777280/