使用 PostgreSQL CTEs 实现递归查询

本文介绍PostgreSQL 递归查询，首先介绍其语法结构，然后通过几个示例进行说明。

语法说明

严格意义上说迭代过程而不是递归，但 RECURSIVE 是SQL标准委员会选择的术语。一般PostgreSQL 递归查询结构包括：

1. 非递归部分select语句
2. Union or Union all
3. 递归部分select语句

语法如下：

WITH RECURSIVE name_cte AS (
SELECT statement  /* non-recursive statement */
UNION [ALL]
SELECT statement  /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;

PostgreSQL 递归查询过程如下：

1. 执行非递归部分并创建临时表
2. 执行递归部分并增加结果至临时表
3. 重复第二步直到工作表为空

union 和 union all 的差异为后者允许重复记录，union消除重复记录。

示例1

生成数字序列：

WITH RECURSIVE tens AS (
   SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;

这是基本递归查询示例，返回10条记录结果：

n
1
2
3
4
5
6
7
8
9
10

示例2

下面示例利用递归查询阶乘：

WITH RECURSIVE fact (n, factorial)
AS (
    SELECT 1 as n, 5 as factorial
union all
    SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;

该查询返回两个表，第一个包括1和5自然数，另一个表计算阶乘；我们可以返回最后一行，但为了查看过程，输出结果如下：

n	factorial
1	5
2	5
3	10
4	30
5	120

示例3

我们再利用递归查询斐波那契数列：

WITH RECURSIVE fibb
AS (
    SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
    SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;

返回结果：

b
1
1
2
3
5
8
13
21
34
55

示例4

利用递归查询，还可以查询组织树：

create table employees(
	employee_id int,
	full_name text,
	manager_id int
);

INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);

Abhi 是第一级， Bhargav, Chay, Dravid, Erin 是第二级， 剩下的为最后一级。

下面语句通过递归查询层级：

WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)  
SELECT * FROM subordinates;

返回结果如下：

employee_id	manager_id	full_name	level
1		Abhi	0
2	1	Bhargav	1
3	1	Chay	1
4	1	Dravid	1
5	1	Erin	1
6	2	Ford	2
7	2	Gagan	2
8	3	Harry	2
9	3	Isaac	2
10	4	Jack	2
11	5	Kiran	2