oracle递归查询connect by prior

六月闻君

已于 2023-02-06 10:37:16 修改

阅读量3.4k

点赞数 2

分类专栏： Oracle 文章标签：数据库大数据 mysql

于 2022-06-27 17:12:30 首次发布

本文链接：https://blog.csdn.net/qq_39065491/article/details/125486385

版权

Oracle 专栏收录该内容

71 篇文章 1 订阅

订阅专栏

递归查询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

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

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字段查询指定的层级。

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