start with connect by prior的使用方法

转载文章地址: https://www.cnblogs.com/benbenduo/p/4588612.html

测试数据

创建表

create table TEST_TABLE
(
  parentid VARCHAR2(10),
  subid    VARCHAR2(10)
);

插入测试数据

insert into test_table (PARENTID, SUBID) values ('1', '2');
insert into test_table (PARENTID, SUBID) values ('1', '3');
insert into test_table (PARENTID, SUBID) values ('2', '4');
insert into test_table (PARENTID, SUBID) values ('2', '5');
insert into test_table (PARENTID, SUBID) values ('3', '6');
insert into test_table (PARENTID, SUBID) values ('3', '7');
insert into test_table (PARENTID, SUBID) values ('5', '8');
insert into test_table (PARENTID, SUBID) values ('5', '9');
insert into test_table (PARENTID, SUBID) values ('7', '10');
insert into test_table (PARENTID, SUBID) values ('7', '11');
insert into test_table (PARENTID, SUBID) values ('10', '12');
insert into test_table (PARENTID, SUBID) values ('10', '13');

数据结构

使用场景

主要是用于B树等类型结构的递归查询，我们可以通过树中的任意一个节点来遍历该树。

语法格式

 select … from tablename
	start with 条件1
	connect by *** prior ***
	where 条件3;

语法说明

• start with: 遍历的开始节点，可以是父节点也可以是子节点

  • 父节点

    SELECT T.PARENTID, T.SUBID, LEVEL
      FROM TEST_TABLE T
     START WITH T.parentid = '3'
    CONNECT BY PARENTID = PRIOR SUBID
     ORDER BY LEVEL DESC
    

    结果：
    
    上面start with后面接的是父节点为3，遍历的结果就是以3位父节点的所有数据，如果父节点是最后的叶子节点(比如13)，那么查询结果就是空。

  • 子节点

    SELECT T.PARENTID, T.SUBID, LEVEL
      FROM TEST_TABLE T
     START WITH T.subid = '7'
    CONNECT BY PARENTID = PRIOR SUBID
     ORDER BY LEVEL DESC
    

    结果
    
    可以看到子节点subid是从7开始的。

• connect by prior : 连接条件。关键词prior，prior跟父节点列放在一起，就是往父结点方向遍历，prior跟子结点列放在一起，则往叶子结点方向遍历，父节点与子节点谁放在“=”前都无所谓，关键是prior跟谁在一起。

  • 可以看到在上面的案例中start with后面无论是接的subid还是parentid都是向树的下方(子节点)去遍历。这个就是与prior是与谁连接在一起有关了。上面的两个例子中与prior连接在一起的都是subid，因此都是向子节点去遍历。

  • prior与parentid相连

    SELECT T.PARENTID, T.SUBID, LEVEL
      FROM TEST_TABLE T
     START WITH T.subid = '7'
    CONNECT BY subid = PRIOR parentid
     ORDER BY LEVEL DESC
    

    结果
    
    可以看到此时是向父节点去遍历的。因为prior后面是紧跟的parentid。

使用level

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2。需要注意的是这里所说的根节点指的是start with后面的那个节点

parentid为第一层级

SELECT T.PARENTID, T.SUBID, LEVEL
  FROM TEST_TABLE T
 START WITH T.parentid = '7'
CONNECT BY parentid = PRIOR subid
 ORDER BY LEVEL DESC;

SELECT T.PARENTID, T.SUBID, LEVEL
  FROM TEST_TABLE T
 START WITH T.parentid = '7'
CONNECT BY subid = PRIOR parentid
 ORDER BY LEVEL DESC;

可以看到无论prior后面接的是parentid还是subid，第一层级都是parentid为7(start with后面的值)。

subid为第一层级

SELECT T.PARENTID, T.SUBID, LEVEL
  FROM TEST_TABLE T
 START WITH T.subid = '7'
CONNECT BY parentid = PRIOR subid
 ORDER BY LEVEL DESC;

SELECT T.PARENTID, T.SUBID, LEVEL
  FROM TEST_TABLE T
 START WITH T.subid = '7'
CONNECT BY subid = PRIOR parentid
 ORDER BY LEVEL DESC;

此时可以看到无论prior后面接的是parentid还是subid，当start with后面接的是subid为7时，那么第一层级都是subid为7。