如何使用WITH RECURSIVE子句进行选择

首先，让我们尝试简化和阐明手册页上给出的算法描述。为了简化它，现在（和以后）仅考虑union allinwith recursive子句union：

WITH RECURSIVE pseudo-entity-name(column-names) AS (
    Initial-SELECT
UNION ALL
    Recursive-SELECT using pseudo-entity-name
)
Outer-SELECT using pseudo-entity-name

为了澄清这一点，让我们用伪代码描述查询执行过程：

working-recordset = result of Initial-SELECT

append working-recordset to empty outer-recordset

while( working-recordset is not empty ) begin

    new working-recordset = result of Recursive-SELECT 
        taking previous working-recordset as pseudo-entity-name

    append working-recordset to outer-recordset

end

overall-result = result of Outer-SELECT 
    taking outer-recordset as pseudo-entity-name

甚至更短-数据库引擎执行初始选择，将其结果行作为工作集。然后，它对工作集重复执行递归选择，每次用获得的查询结果替换工作集的内容。当递归选择返回空集时，此过程结束。并首先收集由初始选择然后递归选择给出的所有结果行，并将其馈送到外部选择，该结果将成为整体查询结果。

该查询正在计算3的阶乘：

WITH RECURSIVE factorial(F,n) AS (
    SELECT 1 F, 3 n
UNION ALL
    SELECT F*n F, n-1 n from factorial where n>1
)
SELECT F from factorial where n=1

初始选择SELECT 1 F, 3 n为我们提供初始值：3表示参数，1表示函数值。
递归选择SELECT F*n F, n-1 n from factorial where n>1指出，每当我们需要将最后一个函子值乘以最后一个参数值并递减参数值时，就会发生这种情况。
数据库引擎是这样执行的：

首先，它执行初始选择，这给出了工作记录集的初始状态：

F | n
--+--
1 | 3

然后，它使用递归查询转换工作记录集并获得其第二状态：

F | n
--+--
3 | 2

然后第三种状态：

F | n
--+--
6 | 1

在第三个状态中，没有行遵循n>1递归选择的条件，因此工作集为循环退出。

现在，外部记录集将保存所有行，这些行由初始选择和递归选择返回：

F | n
--+--
1 | 3
3 | 2
6 | 1

外部选择从外部记录集中过滤掉所有中间结果，仅显示最终阶乘值，该值成为整体查询结果：

F 
--
6

现在让我们考虑一下table forest(id,parent_id,name)：

id | parent_id | name
---+-----------+-----------------
1  |           | item 1
2  | 1         | subitem 1.1
3  | 1         | subitem 1.2
4  | 1         | subitem 1.3
5  | 3         | subsubitem 1.2.1
6  |           | item 2
7  | 6         | subitem 2.1
8  |           | item 3

“扩展全树”在这里是指在计算树的级别和（可能）路径时，以人类可读的深度优先顺序对树进行排序。如果不使用WITH RECURSIVE子句（或PostgreSQL不支持的Oracle CONNECT BY子句），则两个任务（正确的排序和计算级别或路径）都不能在一个（甚至是恒定数量的）SELECT中解决。但是，此递归查询可以完成工作（嗯，几乎可以做到，请参阅下面的注释）：

WITH RECURSIVE fulltree(id,parent_id,level,name,path) AS (
    SELECT id, parent_id, 1 as level, name, name||'' as path from forest where parent_id is null
UNION ALL
    SELECT t.id, t.parent_id, ft.level+1 as level, t.name, ft.path||' / '||t.name as path
    from forest t, fulltree ft where t.parent_id = ft.id
)
SELECT * from fulltree order by path

数据库引擎是这样执行的：

首先，它执行initail select，它给出表中所有最高级别的项（根）forest：

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
1  |           | 1     | item 1           | item 1
8  |           | 1     | item 3           | item 3
6  |           | 1     | item 2           | item 2

然后，它执行递归选择，从而给出forest表中的所有第二级项目：

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
2  | 1         | 2     | subitem 1.1      | item 1 / subitem 1.1
3  | 1         | 2     | subitem 1.2      | item 1 / subitem 1.2
4  | 1         | 2     | subitem 1.3      | item 1 / subitem 1.3
7  | 6         | 2     | subitem 2.1      | item 2 / subitem 2.1

然后，它再次执行递归选择，检索3d级别的项目：

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
5  | 3         | 3     | subsubitem 1.2.1 | item 1 / subitem 1.2 / subsubitem 1.2.1

现在，它再次执行递归选择，尝试检索第4级项目，但是没有一个项目，因此循环退出。

外部SELECT设置正确的人类可读行顺序，并在path列上排序：

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
1  |           | 1     | item 1           | item 1
2  | 1         | 2     | subitem 1.1      | item 1 / subitem 1.1
3  | 1         | 2     | subitem 1.2      | item 1 / subitem 1.2
5  | 3         | 3     | subsubitem 1.2.1 | item 1 / subitem 1.2 / subsubitem 1.2.1
4  | 1         | 2     | subitem 1.3      | item 1 / subitem 1.3
6  |           | 1     | item 2           | item 2
7  | 6         | 2     | subitem 2.1      | item 2 / subitem 2.1
8  |           | 1     | item 3           | item 3

注意：仅当/项目名称中没有标点字符排序规则时，结果行顺序才会保持正确。如果我们Item 2在中重命名Item 1 *，它将打破行顺序，位于Item 1其后代之间。
更为稳定的解决方案是E'\t'在查询中使用制表符（）作为路径分隔符（稍后可以用更具可读性的路径分隔符代替：在外部选择中，然后移至人类等）。制表符分隔的路径将保持正确的顺序，直到项目名称中包含制表符或控制字符为止-可以轻松检查和排除它们而不会损失可用性。

修改最后一个查询以扩展任意子树是非常简单的-只需parent_id is null用perent_id=1（例如）替换条件即可。请注意，此查询变体将返回相对于的 所有级别和路径Item 1。

现在是关于典型的错误。特定于递归查询的最明显的典型错误是在递归选择中定义错误停止条件，这会导致无限循环。

例如，如果我们where n>1在上面的阶乘样本中省略条件，则执行递归选择将永远不会给出空集（因为我们没有条件可以过滤出单行），并且循环将无限地继续。

那就是您的某些查询挂起的最可能原因（其他非特定但仍然可能的原因是select的效率很低，它在有限但很长的时间内执行）。

有没有太多的递归查询特定guidlines提，据我所知。但是我想建议（相当明显）逐步的递归查询构建过程。

• 分别构建和调试您的初始选择。

• 用带有RECURSIVE构造的脚手架包装它，
  然后开始构建和调试递归选择。

推荐的scuffolding结构是这样的：

WITH RECURSIVE rec( <Your column names> ) AS (
    <Your ready and working initial SELECT>
UNION ALL
    <Recursive SELECT that you are debugging now>
)
SELECT * from rec limit 1000

这个最简单的外部选择将输出整个外部记录集，正如我们所知，该记录集包含初始选择和重新执行选择的每次执行的所有输出行，按原始输出顺序循环播放-就像上面的示例一样！该limit 1000零件将防止悬挂，将其替换为超大输出，在该输出中您将能够看到错过的停止点。

• 在调试了初始选择和递归选择之后，构建并调试外部选择。

现在最后要提的是-使用union代替union allinwith recursive子句的区别。它引入了行唯一性约束，这导致我们的执行伪代码中多了两行：

working-recordset = result of Initial-SELECT

discard duplicate rows from working-recordset /*union-specific*/

append working-recordset to empty outer-recordset

while( working-recordset is not empty ) begin

    new working-recordset = result of Recursive-SELECT 
        taking previous working-recordset as pseudo-entity-name

    discard duplicate rows and rows that have duplicates in outer-recordset 
        from working-recordset /*union-specific*/

    append working-recordset to outer-recordset

end

overall-result = result of Outer-SELECT 
    taking outer-recordset as pseudo-entity-name