最近需要做实验，用到hr的employees表，但是这个表的数据很多，不容易看清结果。
所以需要精简他的数据，但是还要维护他原有的树形结构。

准备实验数据

create table emp as select * from hr.employees;

select lpad(employee_id,level*4,' ') from emp

start with manager_id is null

connect by prior employee_id=manager_id;

100
101
108
109
110
111
112
113
200
203
204
205
206
102
103
104
105
106
107
114
115
116
117
118
119
120
125
126
127
128
180
181
182
183
121
......

上面的结果红色部分保留（101,102,114及其子节点保留），其余删除。

第一种方法，不等连接

delete from emp where employee_id not in(

    select t1.employee_id from (

        --自顶向下访问

        select employee_id,sys_connect_by_path(employee_id,\',\') path

        from emp

        start with manager_id is null

        connect by prior employee_id=manager_id

    ) t1

    inner join (

        select employee_id target from emp where employee_id in(101,102,114)

    ) t2

    on instr(t1.path,','||t2.target)!=0

) and employee_id!=100;

第二种方法，根据节点遍历树

delete from emp where employee_id not in

(

    select t1.employee_id from

    (

        --根据101,102,114 分别自顶向下和自底向上遍历

        select employee_id from emp

        start with manager_id in (101,102,114)

        connect by prior employee_id=manager_id

        union

        select employee_id from emp

        start with manager_id in (101,102,114)

        connect by employee_id =prior manager_id

    ) t1,

    (

        select employee_id,sys_connect_by_path(employee_id,\',\') path

        from emp

        start with manager_id is null

        connect by prior employee_id=manager_id

    ) t2

    where t1.employee_id=t2.employee_id

);

ITPub博客

裁剪树

最新文章