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 //后面就不再重复说这个数据顺序了

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

结论：这样是符合我们的平实需求的，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;

查询结果：

 

1 1 老太爷 
2 8 外姓爷爷 
结论：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;

查询结果：

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

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

 

 

demo4：

继续插入数据：

9 9 大爷爷的二儿子的儿子 7
10 10 大爷爷的大儿子的儿子 4

 

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

结果：

1 4 大爷爷的大儿子 2
2 10 大爷爷的大儿子的儿子 4
3 7 大爷爷的二儿子 2
4 9 大爷爷的二儿子的儿子 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 ;

结果：

1 2 2 大爷爷 1
2 2 3 二爷爷 1

 

结论：从start with 开始算第一级