了解递归CTE的步骤 [英] Understanding steps of recursive CTE

问题描述

从中间步骤和过程中涉及的工作/临时表的角度，我很难理解递归CTE的工作原理。

I am having trouble understanding how recursive CTE works, in terms of the intermediate steps and the working/temporary tables involved along the way.

从 PostgreSQL文档：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 5
)
SELECT n FROM t;

在Postgres（9.5）中运行此命令，我得到：

Running this in Postgres (9.5), I get:

 n 
---
 1
 2
 3
 4
 5
(5 rows)

但是为什么我们没有得到更多行？例如，

But why didn't we get more rows? For example

    SELECT n+1 FROM t WHERE n < 5

当n = 2时，为什么表格 t 有两行

when n = 2, why doesn't the table t have two rows

---
 1
 2

并基于此生成

---
 2
 3

？
如果是这种情况，最终结果应该有很多重复的值，例如 2 和 UNION ALL 。

文档的相关部分对工作表和中间表进行了以下说明，尽管对我而言，描述性不够清晰：

The relevant part of the documentation says the following about a "working table" and an "intermediate table", which is although descriptive not clear enough to me:

1。评估非递归项。 ...将所有剩余的行包括在递归查询的结果中，并将它们放置在临时的
工作表中。

1.Evaluate the non-recursive term. ... Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.

2。工作表不为空，请重复以下步骤：

2.So long as the working table is not empty, repeat these steps:

a。评估递归项，将工作表中
的当前内容替换为递归自引用。 ...将所有剩余的
行包括在递归查询的结果中，并将
放在临时中间表中。

a. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. ... Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.

b。用中间表
的内容替换工作表的内容，然后清空中间表。

b. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.

我的问题是：

有人可以逐步解释上面简单的例子吗？

Can anyone please explain step-wise what is going on with the simple example above?

也，我试图从编程的角度理解这种递归CTE。谁能在上述CTE中为算法概述框架以生成序列？

Also, I am trying to understand this recursive CTE from a programming perspective. Can anyone outline a skeleton for the algorithm in the above CTE to generate the sequence?

推荐答案

每次CTE的后半段运行时，只会看到 上次运行的结果。因此，第一轮执行执行上半部分并产生1。第二轮执行执行下半部分。它看到 t 包含1，因此返回2。第三次运行看到 t 包含2（而不是1和2，因为它只看到上一次运行的结果），所以它返回3。

Each time the second half of the CTE runs, it sees only the results of the previous run. So the initial run executes the top half and yields 1. The second run executes the bottom half. It sees t as containing 1, so it returns 2. The third run sees t as containing 2 (not 1 and 2, because it only sees the results of the previous run), so it returns 3.

第四次运行看到3并返回4。

The fourth run sees 3 and returns 4.

第五次运行看到4并返回5。

The fifth run sees 4 and returns 5.

第六次运行看到5，但不包括WHERE 子句，因此它不返回任何行。

The sixth run sees 5, but that is excluded by the WHERE clause, so it returns no rows. Returning no rows is the signal to stop.

因此，现在CTE的完整结果是 1、2、3、4、5 ，这是CTE在外部看到的所有内容。

So now the complete result of the CTE is 1, 2, 3, 4, 5, which is what everything outside the CTE sees.

这篇关于了解递归CTE的步骤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！