select * from (select level as mat_level, t.组件, connect_by_root t.物料 from table t start with 物料 = 882 connect by T.物料 = prior T.组件) where mat_level = 3;建议自己查一下语法,不难的。学会了也能多掌握点技巧
说明一下,上面写的MAT_LEVEL=3不是很精确,最好是取MAT_LEVEL最大的,我偷懒了
对于表的存储结构来说,882才是根节点,那四个是叶子节点。 “最顶层的组件”如果指的是全部叶子节点。 select * from (select level as mat_level, t.组件, connect_by_root t.物料, connect_by_isleaf from table t start with 物料 = 882 connect by T.物料 = prior T.组件) where connect_by_isleaf = 1 如果指的是深度最高的节点 select * from (select level as mat_level, t.组件, connect_by_root t.物料, connect_by_isleaf, max(level) over() mlv from table t start with 物料 = 882 connect by T.物料 = prior T.组件) where connect_by_isleaf = 1 and mlv = mat_level
select *
from (select level as mat_level, t.组件, connect_by_root t.物料
from table t
start with 物料 = 882
connect by T.物料 = prior T.组件)
where mat_level = 3;建议自己查一下语法,不难的。学会了也能多掌握点技巧
“最顶层的组件”如果指的是全部叶子节点。
select *
from (select level as mat_level,
t.组件,
connect_by_root t.物料,
connect_by_isleaf
from table t
start with 物料 = 882
connect by T.物料 = prior T.组件)
where connect_by_isleaf = 1
如果指的是深度最高的节点
select *
from (select level as mat_level,
t.组件,
connect_by_root t.物料,
connect_by_isleaf,
max(level) over() mlv
from table t
start with 物料 = 882
connect by T.物料 = prior T.组件)
where connect_by_isleaf = 1
and mlv = mat_level