如果查询的节点级别大于2可以这么做 2以内的话 那就要查询当前节点所在的整个树结构了 会麻烦很多create table t2 (子 varchar2(5),父 varchar2(5));insert into t2 values ('a4','a3'); insert into t2 values ('a3','a2'); insert into t2 values ('a2','a1'); insert into t2 values ('b3','b2'); insert into t2 values ('b2','b1'); select 父 from t2 where level=2 start with 子 ='a4' connect by prior 父 = 子 父 -------------- 1 a2
scott@CNMMBO> select * from ( 2 select child,parent,level lvl from t1 3 start with child='a4' 4 connect by prior parent=child) where lvl=2;CH PA LVL -- -- ---------- a3 a2 2 详细参考 :SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
level
insert into t2 values ('a3','a2');
insert into t2 values ('a2','a1');
insert into t2 values ('b3','b2');
insert into t2 values ('b2','b1');
select 父 from t2 where level=2 start with 子 ='a4' connect by prior 父 = 子 父
--------------
1 a2
scott@CNMMBO> select * from (
2 select child,parent,level lvl from t1
3 start with child='a4'
4 connect by prior parent=child) where lvl=2;CH PA LVL
-- -- ----------
a3 a2 2
详细参考 :SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)