oracle里面实现树形查询比mssql要轻松的多:
今天学习的 记录下:
例子:
建表
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;
根据输入节点向下查询(包括自身节点信息)
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 departno =topno
start with departno='005';