【无标题】

递归查询connect by prior

1、测试环境

数据库表结构如下：

create table menu_prior( root_id number, id number, name varchar(20),
description varchar(20) );
插入数据

insert into menu_prior(root_id,id,name,description) values(0,100,'1menu','main menu');
insert into menu_prior(root_id,id,name,description) values(100,101,'1-1menu','1 level menu');
insert into menu_prior(root_id,id,name,description) values(100,102,'1-2menu','1 level menu');
insert into menu_prior(root_id,id,name,description) values(102,1021,'1-2-1menu','2 level menu');
insert into menu_prior(root_id,id,name,description) values(102,1022,'1-2-2menu','2 level menu');

insert into menu_prior(root_id,id,name,description) values(0,200,'2menu','main menu');
insert into menu_prior(root_id,id,name,description) values(200,201,'2-1menu','1 level menu');
insert into menu_prior(root_id,id,name,description) values(200,202,'2-2menu','1 level menu');
insert into menu_prior(root_id,id,name,description) values(202,2021,'2-1-1menu','2 level menu');
insert into menu_prior(root_id,id,name,description) values(202,2022,'2-1-2menu','2 level menu');
1
2
3
4
5
6
7
8
9
10
11

2、基本查询

（1）获取完整树：

select * from menu_prior;
select * from menu_prior start with root_id = 0 connect by prior id = root_id;

（2）获取特定子树：

select * from menu_prior start with id = 100 connect by prior id = root_id;
select * from menu_prior start with id = 200 connect by prior id = root_id;

select * from menu_prior start with id = 200 connect by root_id = id;

（3）不做深层递归

如果connect by prior中的prior被省略，则查询将不进行深层递归。
如：
select * from menu_prior start with root_id = 0 connect by id = root_id;
select * from menu_prior start with id = 100 connect by id = root_id;

3、递归查询分类

oracle的start with connect by prior是根据条件递归查询"树"，分为四种使用情况：

第一种：start with 子节点ID=’…’ connect by prior 子节点ID = 父节点ID

查询结果是：自己所有的后代节点（包括自己）

select * from menu_prior  m start with m.root_id=100 connect by prior m.id=m.root_id;
1

第二种：start with 子节点ID=’…’ connect by prior 父节点ID =子节点ID

查询结果是：自己所有的前代节点（包括自己）

select * from menu_prior  m start with m.id=1022 connect by prior m.root_id=m.id;
1

第三种：start with 父节点ID=’…’ connect by prior 子节点ID = 父节点ID

查询结果是：自己所有的后代节点（不包括自己）。

select * from menu_prior  m start with m.root_id=102 connect by prior m.id=m.root_id;
1

第四种：start with 父节点ID=’…’ connect by 父节点ID = prior 子节点ID

查询结果是：自己的第一代后节点和所有的前代节点（包括自己）。

select * from menu_prior  m start with m.root_id=102 connect by prior m.root_id=m.id;
1

4、总结：

1、start with id= 是定义起始节点（种子），可以是id也可以是root_id,定义为root_Id查询该节点下所有的树结构，定义为id（子节点）则查询指定的树。
2、connect by prior ：prior的含义为先前，前一条记录。prior id=root_id 也就是前一条记录的id等于当前记录的root_id(父id)。
3、可以向下或者向上查找，父节点在前，向前查找；父节点在后，向后查找。
4、level字段为oracle特有的层级字段，可以通过level字段查询指定的层级。

select root_id,id,name,level from menu_prior where level=1
 start with root_id = 0 
connect by prior id = root_id;

使用level控制层级：
select root_id,id,name,level from menu_prior where level=2
 start with root_id = 0 
connect by prior id = root_id;
1
2
3
4
5
6
7
8