递归的使用：oracle&pgsql

一、数据准备

create table test(
id varchar2(2),
parent varchar2(10),
child varchar2(10)
);

insert into test  (id,parent,child)
values('1','a','ab');
insert into test  (id,parent,child)
values('2','a','ac');
insert into test  (id,parent,child)
values('3','b','bc');
insert into test  (id,parent,child)
values('4','ab','abc');
insert into test  (id,parent,child)
values('5','abc','abcd');
insert into test  (id,parent,child)
values('6','ac','acd');
insert into test  (id,parent,child)
values('7','bc','bcd');

查询数据结果：select * from test

1    a         ab
2    a         ac
3    b         bc
4    ab      abc
5    abc    abcd
6    ac      acd
7    bc      bcd
数据分析：存在两条递归

1 : a -> ab -> abc -> abcd  

     a -> ac-> acd

2 : b -> bc-> bcd

二、oracle数据库

关键字分析，详情见具体sql和查询结果：

       level：可以得到递归的第几层；

       start with ：开始递归起点；

       connect by ： 连接条件；

      prior ： 优先的意思，个人理解：固定数据，在那个条件前面，该数据就固定了。

--最常用，已知父级查询所有子级
select t.parent,t.child,level from test t 
start with t.parent = 'a'
connect by prior t.child = t.parent;
a	ab	1
ab	abc	2
abc	abcd	3
a	ac	1
ac	acd	2

select t.parent,t.child,level from test t 
start with t.parent = 'b'
connect by prior t.child = t.parent;
b	bc	1
bc	bcd	2

select t.parent,t.child,level from test t 
start with t.parent = 'a'
connect by  t.child = prior t.parent;
a	ab	1
a	ac	1

select t.parent,t.child,level from test t 
start with t.parent = 'b'
connect by  t.child = prior t.parent;
b	bc	1
-------------------------------------------------------------------
select t.parent,t.child,level from test t 
start with t.child = 'ab'
connect by prior t.child = t.parent;
a	ab	1
ab	abc	2
abc	abcd	3

select t.parent,t.child,level from test t 
start with t.child = 'abcd'
connect by prior t.child = t.parent;
abc	abcd	1

--常用，已知子级查询所有父级
select t.parent,t.child,level from test t 
start with t.child = 'abcd'
connect by  t.child = prior t.parent;   
abc	abcd	1
ab	abc	2
a	ab	3

三、pgsql数据库

**数据准备阶段，没有varchar2类型，记得改成varchar

关键字分析，实际先生成一个递归临时表，然后继续进行查询。详情见具体sql和查询结果：

        recursive : 递归的意思

--常用的，已知父级，查询所有子级
with recursive rs as (
select * from test  where parent = 'a'
union all
select a.* from test a, rs where a.parent = rs.child
)
select * from rs ;
1	a	ab
2	a	ac
4	ab	abc
6	ac	acd
5	abc	abcd

with recursive rs as (
select * from test  where child = 'abcd'
union all
select a.* from test a, rs where a.child = rs.parent
)
select * from rs  ；
5	abc	abcd
4	ab	abc
1	a	ab

with recursive rs as (
select * from test  where child = 'abcd'
union all
select a.* from test a, rs where a.parent = rs.child
)
select * from rs  
5	abc	abcd

--常用的，已知子级，查询所有父级
with recursive rs as (
select * from test  where child = 'abcd'
union all
select a.* from test a, rs where a.child = rs.parent
)
select * from rs  
5	abc	abcd
4	ab	abc
1	a	ab