这篇文章对递归SQL的抽象和解释比较到位,摘录如下,有兴趣可参考原文.
A general format of a recursive query is as follows:
WITH RECURSIVE t AS (
non-recursive term (1)
UNION ALL
recursive term (2)
)
SELECT * FROM t; (3)
The non-recursive term (1) is performed first. Then the recursive term (2) is iterated while it returns some rows. The recursive term is called so because it can access the output of the previous iteration that is available under the name of t.
In the process, the output of each iteration is placed into a resulting table, which will be available under the same name of t when all the query is complete (3). If we replace UNION ALL with UNION, duplicate rows will be eliminated at each iteration.
Using pseudocode, we may represent this as follows:
res ← EMPTY;
t ← (non-recursive term);
WHILE t IS NOT EMPTY LOOP
res ← res UNION ALL t;
aux ← (recursive term);
t ← aux;
END LOOP;
t ← res;