鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 数据库 > oracle > >

Oracle - start with connect by 树操作

来源:互联网 作者:佚名 时间:2018-02-03 10:48
prior 准备工作:建表、插数据 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 int

prior

 

准备工作:建表、插数据

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;

 

select * from tprior;

 

对应的 B-Tree 结构:

 

start with subid = ?

connect by subid = prior parentid

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

  • 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)
  • 一直查到根节点结束

 

start with subid = ?

connect by prior  subid = parentid

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

  • 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)
  • 以此类推,一直查到没有子节点的节点为止

 

start with parentid = ?

connect by subid = prior parentid

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

  • 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 个}
  • 以此类推

 

start with parentid = ?

connect by prior subid = parentid

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

  • 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;

SQL 执行顺序是:先执行 start with,再执行 connect by,最后执行 where。

结论:where 只是树结构的修剪,不改变树的层次结构。

 

参考资料:

  • 博客园 - http://www.cnblogs.com/benbenduo/p/4588612.html#commentform
  • CSDN - http://blog.csdn.net/wang_yunj/article/details/51040029

 

网友评论
<