create table test_wx
(id varchar2(30),
pid varchar2(30),
type varchar2(30)
);
insert into test_wx (ID, PID, TYPE)
values ('10', '0', 'A');
insert into test_wx (ID, PID, TYPE)
values ('1010', '10', 'B');
insert into test_wx (ID, PID, TYPE)
values ('1011', '10', 'C');
insert into test_wx (ID, PID, TYPE)
values ('101010', '1010', 'D');
insert into test_wx (ID, PID, TYPE)
values ('10101010', '101010', 'E');
insert into test_wx (ID, PID, TYPE)
values ('1010101010', '10101010', 'F');
insert into test_wx (ID, PID, TYPE)
values ('101010101011', '1010101010', 'H');
1.查询id = ‘101010101011’的所有上级叶子节点信息
select a.*
from test_wx a
start with a.id = '101010101011'
connect by prior a.pid = a.id;
2.查询id = ‘101010101011’的上级叶子节点信息,截止到type='B'
select level, a.*
from test_wx a
where level <= (select level
from test_wx a
where a.type = 'B'
start with a.id = '101010101011'
connect by prior a.pid = a.id)
start with a.id = '101010101011'
connect by prior a.pid = a.id;