可以使用 start with 条件 connect by prior 条件 如:
drop table employee;
create table employee (
id number not null,
name varchar2(50),
parent_id number,
primary key(id)
);
insert into employee(id,name) values(100,'aaa');
insert into employee(id,name,parent_id) values(1001,'aa20',100);
insert into employee(id,name,parent_id) values(1002,'aa202',100);
insert into employee(id,name,parent_id) values(1005,'aa301',1001);
insert into employee(id,name,parent_id) values(1006,'aa302',1001);
insert into employee(id,name,parent_id) values(1008,'aa303',1002);
insert into employee(id,name,parent_id) values(10010,'aa401',1008);
insert into employee(id,name,parent_id) values(10011,'aa40',1008);
insert into employee(id,name,parent_id) values(10022,'4aa222',3001);
commit;
从root 开始,可以查出该树的所有节点记录:
select id, name from employee start with id=100 connect by prior id=parent_id;
从末端开始, 到root 查询出 该部分节点的记录
select id, name from employee start with id=10011 connect by prior parent_id=id;
添加条件
select r.id, r.name from
(select id, name from employee start with id=100 connect by prior id=parent_id ) r
where r.id>1100;
或
select id, name
from employee
where id <> '1002'
start with id = 10011
connect by prior parent_id = id;