sql(oracle) 查询树 记录

可以使用 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;