kingbase 递归查询

本文以简单的例子说明KingbaseES里的递归查询。

语法结构：

with recursive 名字 as (

  A.初始条件语句(非递归部分)

   

  union [all] 

   

  B.递归部分语句

) [SELECT | INSERT | UPDATE | DELETE]

1.1 说明

上半部分A为初始条件语句，下半部分B为要进行的递归语句

先执行A语句，然后将A语句的结果作为B语句的条件，如果需要对查询结果去重则使用union进行连接，否则使用union all进行连接

1、创建表

create table test_area(id int,name varchar(10),fatherid int);

insert into test_area values

(1,'中国',0),

(2,'辽宁',1),

(3 ,'山东', 1),

(4 ,'沈阳', 2),

(5 ,'大连', 2),

(6 ,'济南', 3),

(7 ,'和平区', 4),

(8 ,'沈河区', 4 );

select * from test_area;

2、with语句

WITH RECURSIVE r AS ( 

 SELECT id,name,fatherid, 1 lvl

  FROM test_area 

  WHERE id = 1

 UNION ALL 

 SELECT a.id,a.name,a.fatherid,r.lvl+1 lvl 

  FROM test_area a, r 

  WHERE a.fatherid = r.id 

) 

SELECT * 

 FROM r ORDER BY id;

--根遍历

WITH RECURSIVE r AS ( 

 SELECT t.id,t.name,t.fatherid,1 as level,t.name::text as path, (select decode(max(s.id),null,1,0) from test_area s where s.id=t.fatherid) as isroot, (select decode(max(s.id),null,1,0) from test_area s where s.fatherid=t.id) as isleaf

  FROM test_area t

  WHERE fatherid = 0

 UNION ALL 

 SELECT a.id,a.name,a.fatherid,r.level+1 as level,r.path||'>'||a.name as path,(select decode(max(s.id),null,1,0) from test_area s where s.id=a.fatherid) as isroot, (select decode(max(s.id),null,1,0) from test_area s where s.fatherid=a.id) as isleaf

  FROM test_area a, r 

  WHERE a.fatherid = r.id 

) 

SELECT * 

 FROM r ORDER BY id;

--叶子遍历

WITH RECURSIVE r AS ( 

 SELECT t.id,t.name,t.fatherid,1 lvl,t.name::text as path, (select decode(max(s.id),null,1,0) from test_area s where s.id=t.fatherid) as isroot, (select decode(max(s.id),null,1,0) from test_area s where s.fatherid=t.id) as isleaf

  FROM test_area t

  WHERE id = 7

 UNION ALL 

 SELECT a.id,a.name,a.fatherid,r.lvl+1 lvl,r.path||'>'||a.name as path,(select decode(max(s.id),null,1,0) from test_area s where s.id=a.fatherid) as isroot, (select decode(max(s.id),null,1,0) from test_area s where s.fatherid=a.id) as isleaf

  FROM test_area a, r 

  WHERE a.id = r.fatherid

) 

SELECT * 

 FROM r ORDER BY id;

3、connect by语句

--根遍历

select id,name,fatherid,level,sys_connect_by_path(name,'>') as path,connect_by_root(id) as isroot,connect_by_isleaf as isleaf

 from test_area

 start with id=1

connect by prior id = fatherid;

--叶子遍历

select id,name,fatherid,level,sys_connect_by_path(name,'>') as path,connect_by_root(id) as isroot,connect_by_isleaf as isleaf

 from test_area

 start with id=7

connect by id = prior fatherid;