oracle递归查询

oracle的递归查询

最近在看公司的OA系统，OA系统中基本都会有节点树，其中对于树上的数据展示，就是用了数据库的递归查询，在这里总结下递归查询。

现在存在如下的一棵树：

不会画树，将就一下，该树对应下面创建的表数据。

创建如下表:

create table DG
(
id NUMBER not null, --主键
parent_id NUMBER, -- 父节点
childer_id NUMBER -- 当前节点，当时名字没区号，也表示当前的值，即树中的值
)

创建如下数据：（除去最后一条数据，其他的数据与树中的数据严格一致）

insert into DG (id , childer_id)values (goods_seq.nextval , 6);
insert into DG (id , Parent_Id , Childer_id) values(goods_seq.nextval,6,10);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,10,9);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,9,1);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,9,2);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,6,8);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,8,3);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,8,4);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,10,7);
insert into DG (id , Parent_Id , Childer_Id) values(goods_seq.nextval,11,7);

1. 查询表中的数据如下

   select * from DG

   ID PARENT_ID CHILDER_ID
   1 91.00 6.00
   2 92.00 6.00 10.00
   3 93.00 10.00 9.00
   4 94.00 9.00 1.00
   5 95.00 9.00 2.00
   6 96.00 6.00 8.00
   7 97.00 8.00 3.00
   8 98.00 8.00 4.00
   9 99.00 10.00 7.00
   10 100.00 11.00 7.00
   查询出来的是全部的数据，但是最后一条数据是不在树这张图上的，因不存在childer_id为11的记录

2. 使用递归查询，查找出树上的全部数据，不在树上的记录不显示，即某条记录的parent_id必须是另一条记录的childer_id,符合该条件的记录都显示出来。递归存在一个入口和一个出口，入口使用start with传入参数，出口就是某条记录不满足这样的条件了

   select * from DG start with childer_id = 6 connect by parent_id = prior childer_id;

   ID PARENT_ID CHILDER_ID
   1 91 6
   2 96 6 8
   3 97 8 3
   4 98 8 4
   5 92 6 10
   6 99 10 7
   7 93 10 9
   8 94 9 1
   9 95 9 2
   与上面比较，不在树中的记录已经没有被显示出来了。
   根据记录的显示，我们可以看出来记录是按照深度遍历的顺序排序的，而不是按广度遍历的顺序排序的
   但是在深度遍历的时候，对于兄弟节点，先遍历哪个，其实是随机的，或者说是按照先插入的数据排在前面，后插入的数据排在后面。
   但是如果我们需要按照我们的规则对兄弟节点的顺序进行排序，这样的排序明显不会符合我们。
   这里我们假设根据childer_id的大小排序，使用如下SQL:

3. 使用order by排序

   select * from DG start with childer_id = 6 connect by parent_id = prior childer_id order by childer_id;

   ID PARENT_ID CHILDER_ID
   1 96 6 8
   2 92 6 10
   3 97 8 3
   4 98 8 4
   5 94 9 1
   6 95 9 2
   7 93 10 9
   8 99 10 7
   9 100 11 7
   10 91 6
   虽然按照parent_id来进行排序了，但是整个结果都是按照parent_id来排序了，不再满足深度遍历的顺序，也就是不分层次了，这样的结果明显不是我们想要的。其实除去不在树中的那条记录外，这样的排序时跟下面的排序一致的

   select * from DG ORDER BY PARENT_ID;

4. 仍然按照深度遍历的顺序排序，但是兄弟节点的排序，使用order sibings by

   select * from DG start with childer_id = 6 connect by parent_id = prior childer_id order siblings by childer_id;

   ID PARENT_ID CHILDER_ID
   1 91 6
   2 96 6 8
   3 97 8 3
   4 98 8 4
   5 92 6 10
   6 99 10 7
   7 93 10 9
   8 94 9 1
   9 95 9 2
   观察整个表的记录，满足了深度遍历的排序，观察3,4 和6,7这两组记录发现兄弟节点的排序已经被我们控制了，用的就是order siblings by

5.获取每条记录的根节点，使用conect_by_root(field)函数

select DG.*, connect_by_root(childer_id) as childer_root , connect_by_root(parent_id) as parent_root , connect_by_root(id) as id_root from DG start with childer_id = 6 connect by parent_id = prior childer_id

ID PARENT_ID CHILDER_ID CHILDER_ROOT PARENT_ROOT ID_ROOT
91 6 6 91
96 6 8 6 91
97 8 3 6 91
98 8 4 6 91
92 6 10 6 91
99 10 7 6 91
93 10 9 6 91
94 9 1 6 91
95 9 2 6 91
--其中parent_root这一列是没有数据的，因为根节点对应的记录是没有parent_id值的
这样就获取了每条记录的根节点。因为查询出来的记录都是在一棵树上的，所以肯定所有记录的根节点信息都是相同的，通常，我们都是查找某一条记录的根节点，只要加个条件就可以了，如下：

select DG.*, connect_by_root(childer_id) as childer_root , connect_by_root(parent_id) as parent_root , connect_by_root(id) as id_root from DG where childer_id = 4 start with childer_id = 6 connect by parent_id = prior childer_id

ID PARENT_ID CHILDER_ID CHILDER_ROOT PARENT_ROOT ID_ROOT
98 8 4 6 空 91

6.获取记录的在树上的层次（用数字表示，第一层是1，level）和是否是叶子节点（0表示不是，1表示是,connect_by_isleaf）

select id , parent_id , childer_id , level , connect_by_isleaf from DG start with childer_id = 6 connect by parent_id = prior childer_id;

ID PARENT_ID CHILDER_ID LEVEL CONNECT_BY_ISLEAF
1 91 6 1 0
2 96 6 8 2 0
3 97 8 3 3 1
4 98 8 4 3 1
5 92 6 10 2 0
6 99 10 7 3 1
7 93 10 9 3 0
8 94 9 1 4 1
9 95 9 2 4 1
观察倒数两列的值，再与上面的树进行比较

7.显示节点的遍历路径 sys_connect_by_path(field,string)

select id , parent_id , childer_id , substring(sys_connect_by_path(childer_id, '->'),3) AS PATH from DG start with childer_id = 6 connect by parent_id = prior childer_id;

ID PARENT_ID CHILDER_ID PATH
1 91 6 6
2 96 6 8 6->8
3 97 8 3 6->8->3
4 98 8 4 6->8->4
5 92 6 10 6->10
6 99 10 7 6->10->7
7 93 10 9 6->10->9
8 94 9 1 6->10->9->1
9 95 9 2 6->10->9->2