Oracle - start with connect by 树操作

Love The Way You Lie 2022-02-26 15:26 221阅读 0赞

> Oracle - start with connect by 树操作

### 准备工作：建表、插数据 ###

create table tprior
    ( parentid number(10),
      subid    number(10)
    );
     
    insert into tprior values ( 1, 2 );
    insert into tprior values ( 1, 3 );
    insert into tprior values ( 2, 4 );
    insert into tprior values ( 2, 5 );
    insert into tprior values ( 3, 6 );
    insert into tprior values ( 3, 7 );
    insert into tprior values ( 5, 8 );
    insert into tprior values ( 5, 9 );
    insert into tprior values ( 7, 10 );
    insert into tprior values ( 7, 11 );
    insert into tprior values ( 10, 12 );
    insert into tprior values ( 10, 13 );
     
    commit;

### 表数据概况、B-Tree 结构 ###

`select * from tprior;`

#### 表数据 ####

![1240][]

#### 对应的 B-Tree 结构： ####

![1240 1][]

### SQL ###

select parentid, subid, level
      from tprior
     start with subid = 7
    connect by subid = prior parentid
     order by level;

![1240 2][]

*  start with subid = 7，首先找出 level 1 记录 (parentid, 7) 即 (3, 7)，作为起始点
 *  connect by subid = prior parentid，表示从子节点往父节点方向遍历，即 level 1 记录 (3, 7) 中的父节点 3，作为 level 2 记录的子节点，查得 level 2 记录 (1, 3)
 *  一直查到根节点结束

select parentid, subid, level
      from tprior
     start with subid = 7
    connect by prior subid = parentid
     order by level;

![1240 3][]

*  start with subid = 7，level 1 记录 (3, 7)
 *  connect by prior subid = parentid，从父节点向子节点方向遍历，即 level 1 记录 (3, 7) 中的子节点 7，作为 level 2 记录的父节点，查得 level 2 记录 (7, 10)、(7, 11)
 *  以此类推，一直查到没有子节点的节点为止

select parentid, subid, level
      from tprior
     start with parentid = 7
    connect by subid = prior parentid
     order by level;

![1240 4][]

*  start with parentid = 7，level 1 记录 (7, 10)、(7, 11)
 *  connect by subid = prior parentid，子节点向节点方向遍历，上一组数据的父节点 7，作为 level 2 记录的子节点，查得 (3, 7)、(3, 7) \{为什么 level 2 是 2 组数据呢？因为 level 1 记录有 2 个\}
 *  以此类推

select parentid, subid, level
      from tprior
     start with parentid = 7
    connect by prior subid = parentid
     order by level;

![1240 5][]

*  start with parentid = 7，level 1 记录 (7, subid)、(7, subid) 即 (7, 10)、(7, 11)
 *  connect with prior parentid = subid，子节点向父节点遍历，level 1 记录的父节点 10、11，作为 level 2 记录的子节点，查得 (10, 13)、(10, 12)
 *  以此类推

select parentid, subid, level
      from tprior
     where parentid > 3
     start with subid = 12
    connect by subid = prior parentid
     order by level;

![1240 6][]

*  SQL 执行顺序是：先执行 start with，再执行 connect by，最后执行 where。
 *  结论：where 只是树结构的修剪，不改变树的层次结构。

[1240]: /images/20220226/ecc806e811a64b97b6b483ea19493669.png
[1240 1]: /images/20220226/becfc2a151c1414ba65d52a9ca6a4e10.png
[1240 2]: /images/20220226/f952fad09b7e47fcabd7d18aab44b976.png
[1240 3]: /images/20220226/1cb378e03b7745e7b1ae2c57463ccc6c.png
[1240 4]: /images/20220226/082b86eb5dff4a6c9cef39ed9272def4.png
[1240 5]: /images/20220226/d1249885a42b44f8b50d170c866fbe95.png
[1240 6]: /images/20220226/d97f4f4fd59e43669f77e74a5cf36018.png