oracle、sqlServer、postgreSQL、greenplum递归查询、递归视图

sqlServer、postgreSQL、greenplum递归查询、递归视图

参考博客：https://blog.csdn.net/wenzhihui_2010/article/details/43935019

如果需要创建postgreSQL的递归视图(RECURSIVE VIEW)，可以参考(需要google搜索)：
How to create a postgreSQL recursive view

--基本语法如下(9.xxx版本)
create recursive view view_name AS 
select .....;

--or（之前版本，现在也适用）
create view view_name as 
with recursive temp1 as (
	--基础查询
	select id,pid::varchar(1000) as pid from tb where pid is not null
	union all 
	--递归查询
	select o.id,r.pid::varchar(1000) as pid from tb o
	join temp1  r 
	on o.pid = r.id 
)select id ,pid from temp1 ;

递归查询通常可用作将有层级关系的数据进行扁平化展示,如组织机构，包含关系等

1 测试数据创建

--创建表
create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); 

--插入测试数据
insert into tb values('002' , 0 , '浙江省'); 
insert into tb values('001' , 0 , '广东省'); 
insert into tb values('003' , '002' , '衢州市');  
insert into tb values('004' , '002' , '杭州市') ; 
insert into tb values('005' , '002' , '湖州市');  
insert into tb values('006' , '002' , '嘉兴市') ; 
insert into tb values('007' , '002' , '宁波市');  
insert into tb values('008' , '002' , '绍兴市') ; 
insert into tb values('009' , '002' , '台州市');  
insert into tb values('010' , '002' , '温州市') ; 
insert into tb values('011' , '002' , '丽水市');  
insert into tb values('012' , '002' , '金华市') ; 
insert into tb values('013' , '002' , '舟山市');  
insert into tb values('014' , '004' , '上城区') ; 
insert into tb values('015' , '004' , '下城区');  
insert into tb values('016' , '004' , '拱墅区') ; 
insert into tb values('017' , '004' , '余杭区') ; 
insert into tb values('018' , '011' , '金东区') ; 
insert into tb values('019' , '001' , '广州市') ; 
insert into tb values('020' , '001' , '深圳市') ;

2 递归查询语句

2.1 sqlServer递归

with result_table AS (
 select a.id,a.name,a.pid from tb a where id='002' 
 union all  
 select k.id,k.name,k.pid  from tb k inner join result_table c on c.id = k.pid 
) 
select * from result_table;

2.1 gp、postgreSQL递归

--以下SQL可以查询出所有id对应的所有的pid
--原始数据
id   pid
004  002
003  002
005  003
002  001 
--递归查询之后的数据
002  001 
003  001
004  001
005  001 
003  002
004  002 
005  002
005  003
   
with recursive result_table AS (
 --基础查询
 select a.id,a.name,a.pid from tb a where pid='0' 
 union all  
 --递归查询
 select k.id,k.name,k.pid  from tb k inner join result_table c on c.id = k.pid 
) 
select * from result_table;