了解PostgreSQL中的递归查询

许多人认为递归查询是一个困难的主题。尽管如此，它们仍然使您能够做在SQL中不可能做的事情。

本文通过示例对其进行了简单介绍，并展示了与Oracle递归查询实现的不同之处。

可以将公用表表达式(CTE)视为仅对单个查询有效的视图：

这也可以在FROM中编写为子查询，但是使用CTE有一些优势：

您可以在查询中多次引用CTE，并且只会计算一次。

请注意，在V12之前，PostgreSQL总是物化CTE。这意味着，CTE是独立于包含的查询计算的。从V12开始，CTE可以“内联”到查询中，这提供了进一步的优化潜力。

CTE(以及下面提到的递归CTE)是在SQL标准中定义的，尽管PostgreSQL没有实现搜索和循环子句。

PostgreSQL在内部使用工作表来处理递归CTE。此处理不是真正的递归，而是迭代：

首先，通过执行CTE的非递归分支来初始化工作表。CTE的结果也使用此结果集进行初始化。如果递归CTE使用UNION而不是UNION ALL，则会删除重复行。

计算CTE的递归分支，用工作表替换对CTE的引用。

将所有结果行添加到CTE结果。如果UNION用于合并分支，则丢弃重复行。

用上一步中的所有新行替换工作表(不包括任何删除的重复项)。

请注意，到目前为止，CTE的自引用分支不是使用完整的CTE结果执行的，而是仅使用自上一次迭代以来的新行(工作表)执行的。

在这里，您必须意识到无休止循环的危险：如果迭代永远不会结束，查询将一直运行，直到结果表变得足够大，从而导致错误。有两种方法可以解决这个问题：

通常，您可以通过使用UNION来避免无限递归，这样可以删除重复的结果行(当然，这需要额外的处理工作)。

另一种方法是在使用CTE的查询上放置一个LIMIT子句，因为如果递归CTE计算的行数与父查询提取的行数一样多，PostgreSQL就会停止处理。这并不是说该技术不能移植到其他符合标准的数据库。

我们可以假设依赖项不包含循环(没有人直接或间接地是他或她自己的经理)。因此，我们可以使用Union all组合查询，因为不会出现重复。

有时您希望添加更多信息，如层次结构级别。您可以通过将起始级别作为常量添加到非递归分支中来做到这一点。在递归分支中，您只需在级别上加1即可：

如果在循环引用的情况下使用UNION来避免重复行，则不能使用此技术。这是因为添加级别会使以前相同的行不同。但在这种情况下，分层级别无论如何都没有多大意义，因为条目可能出现在无限多个级别上。

Oracle数据库对不符合SQL标准的递归查询有不同的语法。原始示例如下所示：

此语法更简洁，但不如递归CTE强大。对于涉及联接的更复杂的查询，可能会变得困难和混乱。

将Oracle“分层查询”转换成递归CTE总是很容易的：

非递归分支是不带CONNECT BY子句但包含START WITH子句的Oracle查询。

递归分支是不带START WITH子句但包含CONNECT BY子句的Oracle查询。添加一个名为递归CTE的联接，并用该联接的CTE中的列替换以前的所有列。

除此之外，Oracle还支持符合标准的递归CTE。它们还支持PostgreSQL没有实现的搜索和循环子句。

如果没有递归CTE，许多可以用过程语言编写的内容就不能用SQL编写。这通常不成问题，因为SQL是用来查询数据库的。如果您需要过程性代码，您总是可以用PostgreSQL中提供的多种过程性语言中的一种来编写数据库函数。

但是递归CTE使SQL图灵变得完整，也就是说，它可以执行与任何其他编程语言相同的计算。显然，这样的“方案”往往比较复杂，效率不高，这只是理论上的考虑，没有太大的实用价值。尽管如此，前面的示例表明递归CTE可以做一些有用的工作，否则您无法在SQL中执行这些工作。

作为递归查询功能的示例，下面是一个递归CTE，它计算斐波那契序列的前几个元素：

此示例还演示了如何使用父查询上的LIMIT子句避免死循环。

您不应该害怕递归查询，即使术语“递归”让您感到害怕。它们并不难理解，而且它们提供了查询包含层次结构和图形的数据库的最简单方法。在许多情况下，它们也比等效的过程代码效率高得多，因为它们避免了对相同表的重复查询。

与窗口函数一起，递归查询是SQL必须提供的最强大的工具之一。