关于WITH RECURSIVE查询Postgres的可能解释

本文介绍了关于WITH RECURSIVE查询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)

手册中的更多详细信息： www.postgresql/docs/current/static/queries-with.html

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