[postgresql]postgresql的递归查询sql实例

 

--1.递归获取组织树

--创建表

create table public.org_test_t(id int ,name varchar(300),parentid int);

--初始化数据

insert into public.org_test_t(id,name,parentid) values(1,'中国',0),

(2,'辽宁',1),

(3,'广东',1),

(31,'广州',3),

(311,'天河',31),

(32,'深圳',3),

(321,'福田',32);

 

--方法一：查询 叶子节点是福田的树

WITH RECURSIVE r AS (

SELECT * FROM public.org_test_t WHERE id = 321

UNION ALL

SELECT t1.* FROM public.org_test_t t1 , r

WHERE t1.id = r.parentid

)

SELECT * FROM r ORDER BY id;

 

--方法二：查询 叶子节点是福田的树

WITH RECURSIVE r(id,name,parentid) AS (

SELECT * FROM (values(cast(0 as int),cast('0' as varchar(300)),cast('321' as int))) as  c(id,name,parentid)

UNION ALL

SELECT t1.* FROM public.org_test_t t1 , r

WHERE t1.id = r.parentid

)

SELECT id,name,parentid FROM r ORDER BY id;

 

SELECT * FROM r ORDER BY id;

--2.递归求和

WITH RECURSIVE t(n) AS (

    VALUES (1)

  UNION ALL

  SELECT n+1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;