Oracle.start with … connect by [… and] prior…order siblings by …

在整個BOM中，動態地指定修改其中某一層，則其子項隨其修改項做出對應變更。

clip_image001[4]

 

clip_image002[4]

 

clip_image003[4]

 

EMPLOYEE_ID

MANAGER_ID

NAME

CREATE_DATE

LEVEL

100

NULL

King

2000-12-12

1

148

100

Cambrault

2009-01-01

2

172

148

Bates

2010-11-01

3

169

148

Bloom

2011-08-21

3

170

148

Fox

2009-03-01

3

173

148

Kumar

2009-06-11

3

168

148

Ozer

2009-10-07

3

171

148

Smith

2012-05-01

3

102

100

DeHaan

2012-01-01

2

103

102

Hunold

2009-12-03

3

105

103

Austin

2011-01-01

4

104

103

Ernst

2011-11-01

4

107

103

Lorentz

2012-11-01

4

106

103

Pataballa

2009-01-01

4

147

100

Errazuriz

2010-11-23

2

166

147

Ande

2010-12-14

3

167

147

Banda

2009-09-01

3

 

--create table
create table kol_employees_tmp
(
employee_id   number(5) not null,
name          varchar2(50) not null,
manager_id    number(5) ,
create_date   date
)


--insert data
begin

insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(172,'Bates',148,to_date('2010-11-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(169,'Bloom',148,to_date('2011-08-21', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(173,'Kumar',148,to_date('2009-06-11', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(168,'Ozer',148,to_date('2009-10-07', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(100,'King',null,to_date('2009-01-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(148,'Cambrault',100,to_date('2009-01-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(170,'Fox',148,to_date('2009-03-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(147,'Errazuriz',100,to_date('2010-11-23', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(167,'Banda',147,to_date('2009-09-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(103,'Hunold',102,to_date('2009-12-03', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(166,'Ande',147,to_date('2010-12-14', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(171,'Smith',148,to_date('2012-05-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(102,'DeHaan',100,to_date('2012-01-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(107,'Lorentz',103,to_date('2012-11-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(106,'Pataballa',103,to_date('2009-01-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(105,'Austin',103,to_date('2011-01-01', 'YYYY-MM-DD'));
insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(104,'Ernst',103,to_date('2011-11-01', 'YYYY-MM-DD'));

end;

  

image

 

--create temp table
with kol_employees_tmp as(
select '2010-11-01' create_date, 172 employee_id, 148 manager_id, 'Bates' name from dual
union all
select null create_date, 169 employee_id, 148 manager_id, 'Bloom' name from dual
union all
select null create_date, 173 employee_id, 148 manager_id, 'Kumar' name from dual
union all
select null create_date, 168 employee_id, 148 manager_id, 'Ozer' name from dual
union all
select '2000-04-04' create_date, 100 employee_id, null manager_id, 'King' name from dual
union all
select null create_date, 148 employee_id, 100 manager_id, 'Cambrault' name from dual
union all
select null create_date, 170 employee_id, 148 manager_id, 'Fox' name from dual
union all
select '2010-11-23' create_date, 147 employee_id, 100 manager_id, 'Errazuriz' name from dual
union all
select '2009-09-01' create_date, 167 employee_id, 147 manager_id, 'Banda' name from dual
union all
--select '2009-12-03' create_date, 103 employee_id, 102 manager_id, 'Hunold' name from dual
select '2013-12-03' create_date, 103 employee_id, 102 manager_id, 'Hunold' name from dual
union all
select null create_date, 166 employee_id, 147 manager_id, 'Ande' name from dual
union all
select null create_date, 171 employee_id, 148 manager_id, 'Smith' name from dual
union all
select '2012-01-01' create_date, 102 employee_id, 100 manager_id, 'DeHaan' name from dual
union all
select null create_date, 107 employee_id, 103 manager_id, 'Lorentz' name from dual
union all
select '2009-01-01' create_date, 106 employee_id, 103 manager_id, 'Pataballa' name from dual
union all
--select '2011-01-01' create_date, 105 employee_id, 103 manager_id, 'Austin' name from dual
select '2018-01-01' create_date, 105 employee_id, 103 manager_id, 'Austin' name from dual
union all
select null create_date, 104 employee_id, 103 manager_id, 'Ernst' name from dual
)
,
condition_list  as(
select 
        emp.employee_id,
        emp.manager_id,
        emp.name,
        emp.create_date,
        level level_id
from kol_employees_tmp emp
where 1=1
and emp.create_date is not null
--start with emp.employee_id = :p_root_item -- itme no
start with emp.employee_id = 100 -- itme no
connect by prior emp.employee_id = emp.manager_id
)
,
redo_employees as(
select 
        t2.employee_id,
        t2.manager_id,
        t2.name,
        t2.create_date,
        t2.parent_create_date,
        t2.level_id
from(
        select 
                t1.employee_id,
                t1.manager_id,
                t1.name,
                t1.create_date,
                t1.parent_create_date,                
                t1.level_id,
                --t1.path,
                --t1.isleaf,                  
                row_number() over(partition by t1.employee_id, t1.manager_id order by t1.parent_create_date desc) maxover_seq
        from(
                select 
                        emp.employee_id,
                        emp.manager_id,
                        emp.name,
                        emp.create_date,
                        connect_by_root(emp.create_date) parent_create_date,                                          
                        level level_id
                        --sys_connect_by_path(emp.employee_id || decode(emp.manager_id,null, '', '*' || emp.manager_id), '/') path,      
                        --connect_by_isleaf isleaf
                from kol_employees_tmp emp
                where 1=1
                --and emp.employee_id in( 102, 105, 106, 147, 167,172)             
                -- start with emp.employee_id = 100  
                start with emp.employee_id in(select cl.employee_id from condition_list cl)     --in( 102, 105, 106, 147, 167,172, 103)     
                connect by prior emp.employee_id = emp.manager_id
                --order siblings by emp.name
        ) t1
   ) t2
where 1=1
and t2.maxover_seq = 1
---------------------
union all
select 
        ket2.employee_id,
        ket2.manager_id,
        ket2.name,
        ket2.create_date,
        ket2.create_date parent_create_date,
        level level_id
from kol_employees_tmp ket2
--start with ket2.employee_id = decode( (select count(1) from condition_list where employee_id = :p_root_item), 0, :p_root_item, '-1') -- itme no
start with ket2.employee_id = decode( (select count(1) from condition_list where employee_id = 100), 0, 100, '-1') -- itme no
connect by     ket2.employee_id not in(select cl.employee_id from condition_list cl)  --in( 102, 105, 106, 147, 167,172, 103) -- break level
and prior ket2.employee_id = ket2.manager_id  
)
--query process tables
select 
        emp.employee_id,
        emp.manager_id,
        emp.name,
        emp.create_date,
        emp.parent_create_date,
        level level_id
from redo_employees emp
--start with emp.employee_id = :p_root_item  -- itme no
start with emp.employee_id = 100  -- itme no
connect by prior emp.employee_id = emp.manager_id
order siblings by emp.name;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/quanweiru/p/2775843.html