oracle 递归查询 start with connect by prior-优快云博客

表结构：

-- Create table create table FAMILY ( ID NUMBER not null, NAME VARCHAR2(20), FATHER_ID NUMBER ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );

数据：

insert into FAMILY (ID, NAME, FATHER_ID) values (1, '老太爷', null); insert into FAMILY (ID, NAME, FATHER_ID) values (2, '大爷爷', 1); insert into FAMILY (ID, NAME, FATHER_ID) values (3, '二爷爷', 1); insert into FAMILY (ID, NAME, FATHER_ID) values (4, '大爷爷的大儿子', 2); insert into FAMILY (ID, NAME, FATHER_ID) values (5, '二爷爷的二儿子', 3); insert into FAMILY (ID, NAME, FATHER_ID) values (6, '二爷爷的大儿子', 3); insert into FAMILY (ID, NAME, FATHER_ID) values (7, '大爷爷的二儿子', 2); insert into FAMILY (ID, NAME, FATHER_ID) values (8, '外姓爷爷', null); insert into FAMILY (ID, NAME, FATHER_ID) values (9, '大爷爷的二儿子的儿子', 7); insert into FAMILY (ID, NAME, FATHER_ID) values (10, '大爷爷的大儿子的儿子', 4); commit;

无序的数据：

id name father_id

1老太爷
2大爷爷1
3二爷爷1
4大爷爷的大儿子2
5二爷爷的二儿子3
6二爷爷的大儿子3
7大爷爷的二儿子2
8外姓爷爷

然后开始试试了:

demo1：

select *
from family a
start with a.father_id is null
connect by prior a.id = a.father_id;

查询结果：

行数 id name father_id //后面就不再重复说这个数据顺序了

11老太爷
22大爷爷1
34大爷爷的大儿子2
47大爷爷的二儿子2
53二爷爷1
65二爷爷的二儿子3
76二爷爷的大儿子3
88外姓爷爷

结论：这样是符合我们的平实需求的，start with 后面跟根节点，最高级

connect by prior 后面跟上下级条件。也就是以father_id为空为最高级结点，开始遍历往下找他们的子孙。

demo2：

select *
from family a
start with a.father_id is null
connect by prior a.father_id = a.id;

查询结果：

11老太爷
28外姓爷爷
结论：connect by prior 后面如果紧跟start with的条件，那么就是向上找父级，如果跟进的是start with后面条件的上下级对应条件，那么就是找子孙。

demo3:

select *
from family a
start with a.father_id = 2
connect by prior a.father_id = a.id;

查询结果：

14大爷爷的大儿子2
22大爷爷1
31老太爷
47大爷爷的二儿子2
52大爷爷1
61老太爷

结论：针对demo2的结论“connect by prior 后面如果紧跟start with的条件，那么就是向上找父级”，是错误的，正确的应该是：connect by prior 后面如果紧跟start with的条件，那么就是倒着来，而且倒着来到start with的条件还不停止，还会继续倒着找条件的父亲。

demo4：

继续插入数据：

99大爷爷的二儿子的儿子7
1010大爷爷的大儿子的儿子4

select *
from family a
start with a.father_id = 2
connect by prior a.id = a.father_id ;

结果：

14大爷爷的大儿子2
210大爷爷的大儿子的儿子4
37大爷爷的二儿子2
49大爷爷的二儿子的儿子7

结论：start with 后面的条件，放在等号后面，确实是查找子孙的顺序查找。

白哥提醒还有个level：就是这样

demo5：

select level, a.*
from family a
where level = 2
start with a.father_id is null
connect by prior a.id = a.father_id ;

结果：

122大爷爷1
223二爷爷1