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);
-
查询表中的数据如下
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的记录
-
使用递归查询，查找出树上的全部数据，不在树上的记录不显示，即某条记录的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:
-
使用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;
-
仍然按照深度遍历的顺序排序，但是兄弟节点的排序，使用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
