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';
此文章来源于网络,版权不归本人所有。