SQL中with recursive用法案例详解

SQL提供了递归查询，可将当前查询结果作为下一次的查询集合进行再次查询，最后得到我们想要的结果。
关键字 with recursive

准备

假设我们有一张机构表org，如下：

列名	描述
id	机构ID
pid	上级机构ID
name	机构名称

查询当前机构和它的所有下级，以列表形式显示

with recursive cte as
(
	select pc1.* from org pc1 where pc1.id in ('000000')
	union all
	select pc2.* from org pc2 inner join cte c on c.id=pc2.pid
)

select ct.* from cte ct;
SQL

从上到下，以链路形式追加

如获取某个机构ID和它所有的下级，且以链路的形式显示
机构ID： a>b>c>d
机构名称：机构1>机构2>机构3>机构4

with recursive cte as
(
	select pc1.id,
	cast(pc1.id as varchar(500)) as id_seq,
	cast(pc1.name as varchar(500)) as name_seq, 
	from org pc1 where pc1.id in ('000000')

	union all
	
	select pc2.id,
	cast(c.id_seq || '>' || pc2.id  as varchar(500)) as id_seq,
	cast(c.name_seq || '>' || pc2.name   as varchar(500)) as name_seq, 
	from org pc2 inner join cte c on c.id=pc2.pid
)

select ct.* from cte ct;
SQL

从下到上 获取链路

如获取某个机构ID和它的所有上级，且以链路的形式显示

with recursive cte as
(
	select 
	pc1.id,
	pc1.pid,
	cast(pc1.id as varchar(500)) as id_seq,
	cast(pc1.name as varchar(500)) as name_seq, 
	from org pc1 where pc1.id in ('66666')

	union all
	
	select 
	pc2.id,
	pc2.pid,
	cast(pc2.id || '>' || c.id_seq  as varchar(500)) as id_seq,
	cast(pc2.name || '>' || c.name_seq   as varchar(500)) as name_seq, 
	from org pc2 inner join cte c on c.pid=pc2.id
)

select ct.* from cte ct;
SQL

彩蛋

利用with recursive实现斐波那契数列

方法1

with recursive fibonacci as
(
	select 1 as n, 0 as fib_n,1 as next_fib_n

	union all
	
	select n+1, next_fib_n, fib_n+next_fib_n from fibonacci where n < 40
)

select * from fibonacci;
SQL

方法2

with recursive fibonacci(n,fib_n,next_fib_n) as
(
	select 1, 0 ,1

	union all
	
	select n+1, next_fib_n, fib_n+next_fib_n from fibonacci where n < 40
)

select * from fibonacci;
SQL