关于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屋!