My data table, is actually a bom structure table
End1id: master pieces of id
End2id: child id
End2basname: child name
Quantity: quantity


Can I use now start with and connect by query by a master piece of id to the whole structure of the

But some of the nodes (virtual), I want to remove (or hidden)
The node with higher node (id), also has the lower node (id), if delete the virtual parts, the nodes of the main pieces of id should be changed at a lower level, consumption as well as change

Could you tell me how to query should be?

CodePudding user response:

Didn't understand what do you mean,,,

CodePudding user response:

reference 1st floor baidu_36457652 response:

didn't understand what you mean,,,

Is similar to the problems in the SQL server

CodePudding user response:

https://bbs.csdn.net/topics/390714141

CodePudding user response:

With tab1 as (
Select id 1, null pid from dual union all
Select 2 id, 1 pid from dual union all
Select 3 id, 2 pid from dual union all
Select 4 id, 2 pid from dual union all
Select 5 id, 3 pid from dual union all
Select 6 id, 3 pid from dual union all
Select 7 id, 3 pid from dual
)
Tab2 as (
The select t1. Id, t1. Pid, sys_connect_by_path (t1) id, '/') c_path from tab1 t1
Start with t1. Id=1
Connect by the prior t1. Id=t1. Pid
), tab3 as (
Select a t1. *, regexp_replace (t1) c_path, '\/3) new_path from tab2 t1
)
Select a t1. *, substr (substr (t1. New_path, 1, instr (t1) new_path, '/' | | t1. Id) - 1), instr (substr (t1. New_path, 1, instr (t1) new_path, '/' | | t1. Id) - 1), '/', 1) + 1) new_p_id from tab3 t1
Where a t1 id!=3
;