connect by 可以 递归查找 某个节点的 所有祖先节点多个已知节点,也固然可以 再用union 按level 排序 再取出排序的第一个 ;比如: select id,lvl from ( select a.id,a.lvl from (SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001042) CONNECT BY ID = PRIOR PID) a, (SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001140) CONNECT BY ID = PRIOR PID) b where a.id=b.id order by a.lvl ) where rownum=1但是如果有多个已知节点,比如100个已知节点,需要 做100次 union ,并且已知节点的个数 不确定, 感觉这样不是最优方案;
SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001042) 这里in 100个节点。 select id from ( select id,minlvl,row_number()over(order by minlvl) rn from ( select id,min(lvl) minlvl from ( SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001042,100,101) CONNECT BY ID = PRIOR PID ) group by id having count(*)=100 ) ) where rn=1
select id,lvl from (
select a.id,a.lvl from
(SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001042) CONNECT BY ID = PRIOR PID) a,
(SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001140) CONNECT BY ID = PRIOR PID) b
where a.id=b.id order by a.lvl
) where rownum=1但是如果有多个已知节点,比如100个已知节点,需要 做100次 union ,并且已知节点的个数 不确定, 感觉这样不是最优方案;
select id
from (
select id,minlvl,row_number()over(order by minlvl) rn
from (
select id,min(lvl) minlvl
from (
SELECT ID,level as lvl from t_sys_organization l start with l.id in(200001042,100,101) CONNECT BY ID = PRIOR PID
)
group by id
having count(*)=100
) )
where rn=1