oracle 树形查询

oracle里面实现树形查询比mssql要轻松的多：

今天学习的 记录下：

 

例子：

建表

 

create table Dept(
DepartNO varchar2(10),
DepartName varchar2(20),
TopNo varchar2(10)
);

create table Dept( 
DepartNO varchar2(10),
DepartName varchar2(20),
TopNo varchar2(10)
);  

  初始化：

 insert into Dept values('001',' 董事会','0'); commit;
 insert into Dept values('002','总裁办 ','001'); commit;
 insert into Dept values('003','财务部 ','001'); commit;
insert into Dept values('004','市场部 ','002'); commit;
insert into Dept values('005','公关部 ','002'); commit;
insert into Dept values('006','销售部 ','002'); commit;
insert into Dept values('007','分销处 ','006'); commit;
insert into Dept values('008','业务拓展处','004'); commit;
insert into Dept values('009','销售科','007'); commit;

 insert into Dept values('001',' 董事会','0'); commit;
 insert into Dept values('002','总裁办 ','001'); commit;
 insert into Dept values('003','财务部 ','001'); commit; 
insert into Dept values('004','市场部 ','002'); commit; 
insert into Dept values('005','公关部 ','002'); commit; 
insert into Dept values('006','销售部 ','002'); commit; 
insert into Dept values('007','分销处 ','006'); commit; 
insert into Dept values('008','业务拓展处','004'); commit; 
insert into Dept values('009','销售科','007'); commit; 

 

 

根据输入节点向下查询（包括自身节点信息）

select distinct departno,departname,level
from dept
connect by prior topno=departno
start with departno='005';

select distinct departno,departname,level 
from dept 
connect by prior topno=departno 
start with departno='005';

  

根据输入节点向下查询（不包括自身节点信息）

select distinct departno,departname,level
from dept
connect by prior topno=departno
start with topno='005';

select distinct departno,departname,level 
from dept 
connect by prior topno=departno 
start with topno='005';  

  

根据输入节点向上查询 

select distinct departno,departname,level
from dept
connect by prior departno =topno
start with departno='005';