PostgreSQL提供了WITH语句,允许你构造用于查询的辅助语句。这些语句通常称为公共表表达式或cte。cte类似于只在查询执行期间存在的临时表。
递归查询是指递归CTE的查询。递归查询在很多情况下都很有用,比如查询组织结构、物料清单等层次数据
下面演示了递归CTE的语法:
WITH RECURSIVE cte_name(
CTE_query_definition -- non-recursive term
UNION [ALL]
CTE_query definion -- recursive term
) SELECT * FROM cte_name;
递归CTE有三个元素:
1.非递归项:非递归项是CTE查询定义,它构成CTE结构的基本结果集。
2.递归项:递归项是使用UNION或UNION ALL操作符将一个或多个CTE查询定义与非递归项连接起来。递归项引用CTE名称本身。
3.终止检查:当上一个迭代没有返回任何行时,递归将停止。
PostgreSQL按以下顺序执行递归CTE:
1.执行非递归项来创建基本结果集(R0)。
2.以Ri作为输入执行递归项,返回结果集Ri+1作为输出。
3.重复步骤2,直到返回一个空集。(终止检查)
4.返回最终的结果集,它是一个并集,或者是所有结果集R0、R1、……Rn的并集
我们将创建一个新表来演示PostgreSQL递归查询。
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);