递归查询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');
2、基本查询
（1）获取完整树：
select * from menu_prior;
SQL> select * from menu_prior start with root_id = 0 connect by prior id = root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 100 1menu main menu
100 101 1-1menu 1 level menu
100 102 1-2menu 1 level menu
102 1021 1-2-1menu 2 level menu
102 1022 1-2-2menu 2 level menu
0 200 2menu main menu
200 201 2-1menu 1 level menu
200 202 2-2menu 1 level menu
202 2021 2-1-1menu 2 level menu
202 2022 2-1-2menu 2 level menu
10 rows selected
（2）获取特定子树：
select * from menu_prior start with id = 100 connect by prior id = root_id;
SQL> select * from menu_prior start with id = 100 connect by prior id = root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 100 1menu main menu
100 101 1-1menu 1 level menu
100 102 1-2menu 1 level menu
102 1021 1-2-1menu 2 level menu
102 1022 1-2-2menu 2 level menu
select * from menu_prior start with id = 200 connect by prior id = root_id;
SQL> select * from menu_prior start with id = 200 connect by prior id = root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 200 2menu main menu
200 201 2-1menu 1 level menu
200 202 2-2menu 1 level menu
202 2021 2-1-1menu 2 level menu
202 2022 2-1-2menu 2 level menu
select * from menu_prior start with id = 200 connect by root_id = id;
SQL> select * from menu_prior start with id = 200 connect by root_id = id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 200 2menu main menu
（3）不做深层递归
如果connect by prior中的prior被省略，则查询将不进行深层递归。
如：
select * from menu_prior start with root_id = 0 connect by id = root_id;
SQL> select * from menu_prior start with root_id = 0 connect by id = root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 100 1menu main menu
0 200 2menu main menu
select * from menu_prior start with id = 100 connect by id = root_id;
SQL> select * from menu_prior start with id = 100 connect by id = root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
0 100 1menu main menu
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;
SQL> select * from menu_prior m start with m.root_id=100 connect by prior m.id=m.root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
100 101 1-1menu 1 level menu
100 102 1-2menu 1 level menu
102 1021 1-2-1menu 2 level menu
102 1022 1-2-2menu 2 level menu
第二种：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;
SQL> select * from menu_prior m start with m.id=1022 connect by prior m.root_id=m.id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
102 1022 1-2-2menu 2 level menu
100 102 1-2menu 1 level menu
0 100 1menu main menu
第三种：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;
SQL> select * from menu_prior m start with m.root_id=102 connect by prior m.id=m.root_id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
102 1021 1-2-1menu 2 level menu
102 1022 1-2-2menu 2 level menu
第四种：start with 父节点ID=’…’ connect by 父节点ID = prior 子节点ID
查询结果是：自己的第一代后节点和所有的前代节点（包括自己）。文章来源：https://www.toymoban.com/news/detail-766462.html
select * from menu_prior m start with m.root_id=102 connect by prior m.root_id=m.id;
SQL> select * from menu_prior m start with m.root_id=102 connect by prior m.root_id=m.id;
ROOT_ID ID NAME DESCRIPTION
---------- ---------- -------------------- --------------------
102 1021 1-2-1menu 2 level menu
100 102 1-2menu 1 level menu
0 100 1menu main menu
102 1022 1-2-2menu 2 level menu
100 102 1-2menu 1 level menu
0 100 1menu main menu
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字段查询指定的层级。文章来源地址https://www.toymoban.com/news/detail-766462.html
select root_id,id,name,level from menu_prior where level=1 start with root_id = 0 connect by prior id = root_id;
SQL> select root_id,id,name,level from menu_prior where level=1
2 start with root_id = 0
3 connect by prior id = root_id;
ROOT_ID ID NAME LEVEL
---------- ---------- -------------------- ----------
0 100 1menu 1
0 200 2menu 1
--使用level控制层级：
select root_id,id,name,level from menu_prior where level=2 start with root_id = 0 connect by prior id = root_id;
SQL> select root_id,id,name,level from menu_prior where level=2
2 start with root_id = 0
3 connect by prior id = root_id;
ROOT_ID ID NAME LEVEL
---------- ---------- -------------------- ----------
100 101 1-1menu 2
100 102 1-2menu 2
200 201 2-1menu 2
200 202 2-2menu 2
到了这里，关于oracle递归查询connect by prior的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
