他的回复:
通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。一个非常简单的例子是计算从1到100的整数合的查询：WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECT n+1 FROM t WHERE n 100)SELECT sum(n) FROM t;一个递归WITH查询的通常形式总是一个非递归项，然后是UNION（或者UNION ALL），再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行：递归查询求值1. 计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。2. 只要工作表不为空，重复下列步骤：a. 计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNIONALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。b. 用中间表的内容替换工作表的内容，然后清空中间表。Note: 严格来说，这个处理是迭代而不是递归，但是RECURSIVE是SQL标准委员会选择的术语。在上面的例子中，工作表在每一步只有一个行，并且它在连续的步骤中取值从1到100。在第100步，由于WHERE子句导致没有输出，因此查询终止。递归查询通常用于处理层次或者树状结构的数据。一个有用的例子是这个用于找到一个产品的直接或间接部件的查询，只要给定一个显示了直接包含关系的表：WITH RECURSIVE included_parts(sub_part, part, quantity) AS (SELECT sub_part, part, quantity FROM parts WHERE part = ’our_product’UNION ALLSELECT p.sub_part, p.part, p.quantityFROM included_parts pr, parts pWHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part103Chapter 7. 查询在使用递归查询时，确保查询的递归部分最终将不返回元组非常重要，否则查询将会无限循环。在某些时候，使用UNION替代UNION ALL可以通过抛弃与之前输出行重复的行来达到这个目的。不过，经常有循环不涉及到完全重复的输出行：它可能只需要检查一个或几个域来看相同点之前是否达到过。处理这种情况的标准方法是计算一个已经访问过值的数组。例如，考虑下面这个使用link域搜索表graph的查询：WITH RECURSIVE search_graph(id, link, data, depth) AS (SELECT g.id, g.link, g.data, 1FROM graph gUNION ALLSELECT g.id, g.link, g.data, sg.depth + 1FROM graph g, search_graph sgWHERE g.id = sg.link)SELECT * FROM search_graph;如果link关系包含环，这个查询将会循环。因为我们要求一个“depth”输出，仅仅将UNIONALL 改为UNION不会消除循环。反过来在我们顺着一个特定链接路径搜索时，我们需要识别我们是否再次到达了一个相同的行。我们可以项这个有循环倾向的查询增加两个列path和cycle：WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (SELECT g.id, g.link, g.data, 1,ARRAY[g.id],falseFROM graph gUNION ALLSELECT g.id, g.link, g.data, sg.depth + 1,path || g.id,g.id = ANY(path)FROM graph g, search_graph sgWHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;除了阻止环，数组值对于它们自己的工作显示到达任何特定行的“path”也有用。在通常情况下如果需要检查多于一个域来识别一个环，请用行数组。例如，如果我们需要比较域f1和f2：WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (SELECT g.id, g.link, g.data, 1,ARRAY[ROW(g.f1, g.f2)],falseFROM graph gUNION ALLSELECT g.id, g.link, g.data, sg.depth + 1,path || ROW(g.f1, g.f2),ROW(g.f1, g.f2) = ANY(path)FROM graph g, search_graph sgWHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;104Chapter 7. 查询Tip: 在通常情况下只有一个域需要被检查来识别一个环，可以省略ROW()语法。这允许使用一个简单的数组而不是一个组合类型数组，可以获得效率。Tip: 递归查询计算算法使用宽度优先搜索顺序产生它的输出。你可以通过让外部查询ORDER BY一个以这种方法构建的“path”，用来以深度优先搜索顺序显示结果。当你不确定查询是否可能循环时，一个测试查询的有用技巧是在父查询中放一个LIMIT。例如，这个查询没有LIMIT时会永远循环：WITH RECURSIVE t(n) AS (SELECT 1UNION ALLSELECT n+1 FROM t)SELECT n FROM t LIMIT 100;这会起作用，因为PostgreSQL的实现只计算WITH查询中被父查询实际取到的行。不推荐在生产中使用这个技巧，因为其他系统可能以不同方式工作。同样，如果你让外层查询排序递归查询的结果或者把它们连接成某种其他表，这个技巧将不会起作用，因为在这些情况下外层查询通常将尝试取得WITH查询的所有输出。WITH查询的一个有用的特性是在每一次父查询的执行中它们只被计算一次，即使它们被父查询或兄弟WITH查询引用了超过一次。因此，在多个地方需要的昂贵计算可以被放在一个WITH查询中来避免冗余工作。另一种可能的应用是阻止不希望的多个函数计算产生副作用。但是，从另一方面来看，优化器不能将来自父查询的约束下推到WITH查询中而不是一个普通子查询。WITH查询通常将会被按照所写的方式计算，而不抑制父查询以后可能会抛弃的行（但是，如上所述，如果对查询的引用只请求有限数目的行，计算可能会提前停止）。以上的例子只展示了和SELECT一起使用的WITH，但是它可以被以相同的方式附加在INSERT、UPDATE或DELETE上。在每一种情况中，它实际上提供了可在主命令中引用的临时表。
