PgSql和Oracle 的递归查询

1、 建表

1.1、 pgsql建表

create table test_recursion (
	id                serial  , 
    parentId      	  int4, 	
    name     		  VARCHAR(32) ,	
    tree_level    	  int4
)

1.2、 oracle建表

create table test_recursion (
	id                NUMBER, 
    parentId      	  NUMBER, 	
    name     		  VARCHAR2(32) ,	
    tree_level    		  NUMBER 
)
##创建序列
CREATE SEQUENCE SEQ_TEST_RECURSION
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
CACHE 5;

2、添加数据

## 此为pgsql添加数据语句，Oracle省略
insert into test_recursion (parentId, name, tree_level) values ( 0, '中国' , 1)

insert into test_recursion (parentId, name, tree_level) 
values ( 1, '陕西省' , 2), ( 1, '山西省' , 2),( 1, '北京' , 2),( 1, '重庆' , 2),( 1, '四川' , 2);

insert into test_recursion (parentId, name, tree_level) 
values ( 4, '北京市' , 3);

insert into test_recursion (parentId, name, tree_level) 
values ( 2, '西安市' , 3), ( 2, '渭南市' , 3),( 2, '延安市' , 3),( 2, '汉中市' , 3),( 2, '安康市' , 3);

insert into test_recursion (parentId, name, tree_level) 
values ( 7, '东城区' , 4), ( 7, '西城区' , 4),( 7, '朝阳区' , 4),( 7, '海淀区' , 4),( 7, '昌平区' , 4);

insert into test_recursion (parentId, name, tree_level) 
values ( 8, '雁塔区' , 4), ( 8, '碑林区' , 4),(8, '莲湖区' , 4),( 8, '灞桥区' , 4),( 8, '未央区' , 4),( 8, '长安区' , 4);

insert into test_recursion (parentId, name, tree_level) 
values ( 11, '汉台区' , 4), ( 11, '南郑区' , 4);

3、查询表数据

select r.* from test_recursion r

4、递归查询数据

4.1、pgsql递归查询数据

## 查询表中北京下面的城市以及区
with recursive a as (
	select b.* from test_recursion b where b.name = '北京'
	union  all
	select c.* from test_recursion c inner join a on c.parentId = a.id
)
select * from a

结果集

## 查询表中雁塔区在哪里
with recursive a as (
	select b.* from test_recursion b where b.name = '雁塔区'
	union  all
	select c.* from test_recursion c inner join a on c.id = a.parentId
)
select * from a

结果集

4.2、oracle递归查询数据

## 查询表中西安市下面的城市以及区
select b.* 
from test_recursion b 
start with b.name = '西安市'
connect by prior b.id = b.parentId

## 查询表中西安市在哪里
select b.* 
from test_recursion b 
start with b.name = '西安市'
connect by prior b.parentId = b.id