关于WITH RECURSIVE查询Postgres的可能解释 [英] Possible explanation on WITH RECURSIVE Query Postgres

问题描述

我一直在阅读Postgres中的With Query。这就是让我感到惊讶的地方

I have been reading around With Query in Postgres. And this is what I'm surprised with

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

我不了解查询的评估方式。

I'm not able to understand how does the evaluation of the query work.

• t（n）听起来像是带有参数的函数。 n的值如何传递。

关于SQL递归语句如何分解的任何见解。

Any insight on how the break down happen of the recursive statement in SQL.

推荐答案

这称为公用表表达式，是在SQL中表示递归查询的一种方式：

This is called a common table expression and is a way of expressing a recursive query in SQL:

t（n）将CTE的名称定义为 t ，其中一个列名为 n 。类似于派生表的别名：

t(n) defines the name of the CTE as t, with a single column named n. It's similar to an alias for a derived table:

select ... 
from (
  ...
) as t(n);

递归从值1开始（即 values（1）部分），然后递归地将值加1，直到达到99。因此它将生成从1到99的数字。然后最终查询将所有这些数字相加。

The recursion starts with the value 1 (that's the values (1) part) and then recursively adds one to it until the 99 is reached. So it generates the numbers from 1 to 99. Then final query then sums up all those numbers.

n 是列名，不是是变量和赋值的发生方式与任何数据检索相同。

n is a column name, not a "variable" and the "assignment" happens in the same way as any data retrieval.

WITH RECURSIVE t(n) AS (
    VALUES (1) --<< this is the recursion "root"
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100 --<< this is the "recursive part"
)
SELECT sum(n) FROM t;

如果您展开该递归（实际上是一个迭代），那么您就结束了像这样：

If you "unroll" the recursion (which in fact is an iteration) then you'd wind up with something like this:

select x.n + 1
from (
  select x.n + 1
  from (
    select x.n + 1
    from (
      select x.n + 1
      from (
         values (1)
      ) as x(n) 
    ) as x(n)
  ) as x(n)
) as x(n)

手册中的更多详细信息：

https://www.postgresql.org/docs/current/static/queries-with.html

More details in the manual:
https://www.postgresql.org/docs/current/static/queries-with.html

这篇关于关于WITH RECURSIVE查询Postgres的可能解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！