五月 19, 2023
在 Oracle 中，层次查询 START WITH ... CONNECT BY 可以用于创建从给定的行开始的单向链表结构。链表可以采用树的形式，并且没有平衡要求。
Oracle 示例
为了说明这一点，让我们从一个查询开始，并假设表中有 5 行。
SELECT * FROM person;
last_name | first_name | id | parent_id
------------+------------+----+-----------
Goodman | Jordan | 1 | (null)
George | Callie | 2 | 1
Steele | Donald | 3 | 1
Allison | Richard | 4 | 1
Thomas | Shaun | 5 | 3
(5 rows)
下面是使用 Oracle 语法的表的分层查询。
select id, parent_id from person
start with parent_id IS NULL
connect by prior id = parent_id;
id | parent_id
----+-----------
1 | (null)
4 | 1
3 | 1
2 | 1
5 | 3
PostgreSQL 示例
我们可以使用 PostgreSQL 的 WITH RECURSIVE 查询，实现和 Oracle CONNECT BY 类似的功能。
WITH RECURSIVE a AS (
SELECT id, parent_id FROM person
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.parent_id
FROM person d
JOIN a ON a.id = d.parent_id )
SELECT id, parent_id FROM a;
id | parent_id
----+-----------
1 | (null)
4 | 1
3 | 1
2 | 1
5 | 3
(5 rows)
这是一个“公用表表达式”（CTE）。它定义了一组查询，这些查询将在同一语句中执行，而不仅仅是在同一事务中执行。您可以有任意数量的括号表达式和最后的语句。对于这种用法，我们只需要一个。通过将声明该语句为递归，它将迭代执行，直到不再返回行。
SELECT
UNION ALL
SELECT
这是递归查询的规定短语。它在文档中被定义为区分起点和递归算法的方法。在 Oracle 术语中，您可以将它们视为与 CONNECT BY 子句联合的 START WITH 子句。
JOIN a ON a.id = d.parent_id
这是 CTE 语句的自联接，该语句将上一行数据提供给后续迭代。
为了说明其工作原理，让我们向查询添加一个迭代指示器：
WITH RECURSIVE a AS (
SELECT id, parent_id, 1::integer recursion_level
FROM person
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.parent_id, a.recursion_level +1
FROM person d
JOIN a ON a.id = d.parent_id )
SELECT * FROM a;
id | parent_id | recursion_level
----+-----------+-----------------
1 | (null) | 1
4 | 1 | 2
3 | 1 | 2
2 | 1 | 2
5 | 3 | 3
(5 rows)
我们用一个值初始化递归级别指标。请注意，在返回的行中，第一个递归级别仅出现一次。这是因为第一个子句只执行一次。
第二个子句是迭代逻辑生效的地方。在这里，我们可以查看上一行数据以及当前行数据。这允许我们执行递归计算。