我的数据表是这样的,实际上是一个bom结构表
end1id:主件id
end2id:子件id
end2basname:子件名称
quantity:用量
我现在用start with和connect by可以通过一个主件id查询到整个结构了但是其中有一些节点(虚拟件),我想要删除(或者隐藏)
该节点有上级节点(主件id),也有下级节点(子件id),如果删除掉该虚拟件,那下级节点的主件id就应该改变了,用量也一样改变请问应该怎么查询呢?
end1id:主件id
end2id:子件id
end2basname:子件名称
quantity:用量
我现在用start with和connect by可以通过一个主件id查询到整个结构了但是其中有一些节点(虚拟件),我想要删除(或者隐藏)
该节点有上级节点(主件id),也有下级节点(子件id),如果删除掉该虚拟件,那下级节点的主件id就应该改变了,用量也一样改变请问应该怎么查询呢?
select 1 id, 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 (
select t1.id, t1.pid, sys_connect_by_path(t1.id, '/') c_path from tab1 t1
start with t1.id = 1
connect by prior t1.id = t1.pid
), tab3 as (
select t1.*, regexp_replace(t1.c_path, '\/3') new_path from tab2 t1
)
select 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 t1.id != 3
;