Oracle start with...connect by prior...用法

目录

1. 定义
2. 举个栗子
3. 第一种情况
4. 第二种情况
5. 第三种情况
6. 第四种情况
7. 总结

定义

start with: 指定起始节点的条件，可以放父级条件，也可以放子级条件

connect by: 连接条件

prior: 优先级关键字 ，这个关键字右边的条件很重要。右边的子级和父级是不一样的结果

举个栗子

我们直接来看例子

首先，建表语句，创建一个数据表用于测试。

CREATE TABLE TREETABLE (
  "CID" NUMBER NOT NULL ,
  "VALUE" VARCHAR2(200 BYTE) ,
  "PID" NUMBER 
)

INSERT INTO TREETABLE VALUES ('1', 'aa', '0');
INSERT INTO TREETABLE VALUES ('2', 'bb', '1');
INSERT INTO TREETABLE VALUES ('3', 'cc', '1');
INSERT INTO TREETABLE VALUES ('4', 'dd', '1');
INSERT INTO TREETABLE VALUES ('5', 'ee', '2');
INSERT INTO TREETABLE VALUES ('6', 'ff', '2');
INSERT INTO TREETABLE VALUES ('7', 'hh', '3');
INSERT INTO TREETABLE VALUES ('8', 'hh', '7');

第一种情况

select cid,pid,value from treetable
start with cid=1
connect by prior cid =  pid
order by cid

我们指定 子级id 作为起始条件，prior 关键字的右边是 子级id，查询结果为
​
首先，start with cid=1 表示找到 cid=1 的行数据，我们称为第一行数据，然后看 connect by prior cid = pid ，不管咋样先看prior 的右边连接的是哪个字段，就取对应的第一行数据的字段去匹配 prior的结尾字段，比如 cid，那就用 1 去匹配 pid 为1 的数据，依次类推，...............。
从结果我们可以看出，首先查询 cid = 1 的，然后 prior 右边是子级id，就往子级的方向查询，以 cid为基础，去匹配 pid =上级cid的数据。

总结，查询的数据是本身+子级数据。

第二种情况

select cid,pid,value from treetable
start with cid=1
connect by  cid = prior pid
order by cid

还是 start with cid=1 查询出第一行数据，然后我们再看 prior 的右边是哪个字段，OK，是 pid ，那我们就把第一行数据的 pid 取出来去匹配，第一行 数据 的 pid 是 0，没有 cid 为 0 的数据， 所以数据如下。
​
那我们换一个 cid =2 的看一下，那现在第一行数据 就是 { 2 1 bb } 了，那就是取出第一行数据 的 pid=1 去匹配 cid = 1的数据了， 因为存在，所以可以看到 找到 了 cid =1 的数据行 ，然后依次类推，...............。
​

第三种情况

select cid,pid,value from treetable
start with pid=1
connect by prior cid =  pid
order by cid

这里我们 start with 的条件 换成了 pid =1 ，查询出 pid =1 的数据有三行，作为第一层，再看 prior 右边 连接的是 cid ，那就取出三行数据的cid 分别去匹配 pid = 第一行数据 的 cid ,得到第二层数据，再依次类推.........，得到第三层数据.......。

​

第四种情况

select cid,pid,value from treetable
start with pid=1
connect by  cid = prior pid
order by cid

这里我们把 prior 换到了 另外一边，那就是查询出 pid =1 的数据作为第一层数据，然后 取出 第一层 的三行数据的 pid 去匹配 第二层的cid ,我们看到有重复的数据，因为第一层分别有三行数据，这就说明是以每一行数据去依次匹配的，所以查询匹配了三次 cid =1 的。

​
我们可以再举个栗子，

select cid,pid,value from treetable
start with pid=2
connect by  cid = prior pid
order by cid

查询 pid =2 的，我们可以看到 分别以 第一层的两行数据的 pid ，去匹配 cid =2 的第二层数据，然后再以第二层的 两行数据的pid，去匹配第三层 cid =1 的数据。就是这样依次类推。

​

总结

今天我们学习了 start with ... connect by prior 的用法，主要是用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。我们还可以 添加 where 条件， 比如 ：
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是
可以用来去除 根数据。