WITH 查询（公用表表达式） #WITH 提供了一种编写辅助语句的方法，以便在较大的查询中使用。这些语句通常称为公用表表达式或CTE，可以被认为是定义了仅针对一个查询存在的临时表。WITH 子句中的每个辅助语句都可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE；WITH 子句本身附加到一个主要语句，该主要语句也可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE。
WITH 中的 SELECT #WITH 中使用 SELECT 的基本价值是将复杂的查询分解为更简单的部分。一个例子是
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
它仅显示顶级销售区域的每个产品的销售总额。WITH 子句定义了两个名为 regional_sales 和 top_regions 的辅助语句，其中 regional_sales 的输出在 top_regions 中使用，而 top_regions 的输出在主 SELECT 查询中使用。这个例子可以在没有 WITH 的情况下编写，但是我们需要两层嵌套的子 SELECT。这样更容易理解一些。
可选的 RECURSIVE 修饰符将 WITH 从一个纯粹的语法便利转换为一个可以完成标准 SQL 中其他不可能实现的功能的特性。使用 RECURSIVE，WITH 查询可以引用自己的输出。一个非常简单的例子是这个查询，用于计算从 1 到 100 的整数之和
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
递归 WITH 查询的一般形式始终是一个非递归项，然后是 UNION（或 UNION ALL），然后是一个递归项，其中只有递归项可以包含对查询自身输出的引用。此类查询的执行方式如下
递归查询评估
评估非递归项。对于 UNION（但不是 UNION ALL），丢弃重复的行。将所有剩余行包含在递归查询的结果中，并将它们放入临时工作表中。
只要工作表不为空，就重复这些步骤
评估递归项，将工作表的当前内容替换为递归自引用。对于 UNION（但不是 UNION ALL），丢弃重复的行和重复任何先前结果行的行。将所有剩余行包含在递归查询的结果中，并将它们放入临时中间表中。
将工作表的内容替换为中间表的内容，然后清空中间表。
虽然 RECURSIVE 允许以递归方式指定查询，但在内部，此类查询是以迭代方式评估的。
在上面的示例中，工作表在每个步骤中只有一行，并且在连续的步骤中采用从 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 ALL
SELECT p.sub_part, p.part, p.quantity * pr.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
当使用递归查询计算树遍历时，您可能希望以深度优先或广度优先的顺序排列结果。这可以通过计算与其他数据列一起的排序列，并使用它在最后对结果进行排序来实现。请注意，这实际上并没有控制查询评估访问行的顺序；这与 SQL 实现无关。此方法仅提供了一种方便的方法来在之后对结果进行排序。
要创建深度优先顺序，我们为每个结果行计算一个我们目前访问过的行数组。例如，考虑以下使用 link 字段搜索表 tree 的查询
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
)
SELECT * FROM search_tree;
要添加深度优先排序信息，您可以编写此代码
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[t.id] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || t.id FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
在一般情况下，需要使用多个字段来标识一行时，请使用行数组。例如，如果我们需要跟踪字段 f1 和 f2
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2) FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
在仅需要跟踪一个字段的常见情况下，省略 ROW() 语法。这允许使用简单的数组而不是复合类型数组，从而提高效率。
要创建广度优先顺序，您可以添加一个跟踪搜索深度的列，例如
WITH RECURSIVE search_tree(id, link, data, depth) AS ( SELECT t.id, t.link, t.data, 0 FROM tree t UNION ALL SELECT t.id, t.link, t.data, depth + 1 FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY depth;
要获得稳定的排序，请添加数据列作为辅助排序列。
递归查询评估算法以广度优先搜索顺序生成其输出。但是，这是一个实现细节，依赖它可能是不合理的。无论如何，每个级别内的行的顺序肯定是未定义的，因此可能需要一些显式的排序。
有内置的语法来计算深度或广度优先排序列。例如
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;
此语法在内部扩展为类似于上述手写形式的内容。SEARCH 子句指定是需要深度优先还是广度优先搜索、要跟踪进行排序的列的列表，以及一个将包含可用于排序的结果数据的列名。该列将隐式添加到 CTE 的输出行中。
在使用递归查询时，务必确保查询的递归部分最终不会返回任何元组，否则查询将无限循环。有时，使用 UNION 而不是 UNION ALL 可以通过丢弃重复先前输出行的行来实现此目的。但是，通常循环不涉及完全重复的输出行：可能需要检查一个或几个字段，以查看是否之前已到达同一点。处理这种情况的标准方法是计算一个已访问值的数组。例如，再次考虑以下使用 link 字段搜索表 graph 的查询
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 0
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
)
SELECT * FROM search_graph;
如果 link 关系包含循环，此查询将会进入循环。因为我们需要一个 “深度” 输出，仅仅将 UNION ALL 改为 UNION 并不能消除循环。相反，我们需要识别在跟踪特定链接路径时是否再次到达同一行。我们在容易出现循环的查询中添加了两个列 is_cycle 和 path。
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[g.id] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, g.id = ANY(path), path || g.id FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
除了防止循环之外，数组值本身通常也很有用，它可以表示到达任何特定行所采用的 “路径”。
在一般情况下，如果需要检查多个字段来识别循环，请使用行数组。例如，如果我们需要比较字段 f1 和 f2
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[ROW(g.f1, g.f2)] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, ROW(g.f1, g.f2) = ANY(path), path || ROW(g.f1, g.f2) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
在只需要检查一个字段来识别循环的常见情况下，请省略 ROW() 语法。这允许使用简单数组而不是复合类型数组，从而提高效率。
有内置语法可以简化循环检测。上面的查询也可以这样写
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
它将在内部被重写为上面的形式。CYCLE 子句首先指定用于循环检测的列列表，然后指定一个列名，该列名将显示是否检测到循环，最后指定另一个将跟踪路径的列名。循环和路径列将隐式添加到 CTE 的输出行中。
循环路径列的计算方式与上一节中显示的深度优先排序列相同。一个查询可以同时具有 SEARCH 和 CYCLE 子句，但是深度优先搜索规范和循环检测规范会创建冗余计算，因此仅使用 CYCLE 子句并按路径列排序更有效。如果需要广度优先排序，则同时指定 SEARCH 和 CYCLE 会很有用。
在不确定查询是否可能循环时，测试查询的一个有用的技巧是在父查询中放置一个 LIMIT。例如，如果没有 LIMIT，此查询将永远循环下去
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
这是因为 PostgreSQL 的实现仅评估父查询实际获取的 WITH 查询的行数。不建议在生产环境中使用此技巧，因为其他系统的工作方式可能不同。此外，如果使外部查询对递归查询的结果进行排序或将其连接到其他表，通常也行不通，因为在这种情况下，外部查询通常会尝试获取 WITH 查询的全部输出。
WITH 查询的一个有用属性是，它们通常在每次执行父查询时仅评估一次，即使父查询或同级 WITH 查询多次引用它们也是如此。因此，可以将多个地方需要的高成本计算放在 WITH 查询中，以避免冗余工作。另一种可能的应用是防止副作用函数的不必要的多重评估。但是，硬币的另一面是，优化器无法将父查询中的限制向下推送到多次引用的 WITH 查询中，因为这可能会影响 WITH 查询输出的所有用途，而实际上它应该仅影响一个用途。多次引用的 WITH 查询将按原样进行评估，而不会抑制父查询之后可能放弃的行。（但是，如上所述，如果对查询的引用仅需要有限数量的行，则评估可能会提前停止。）
但是，如果 WITH 查询是非递归的且无副作用的（即，它是包含无易失性函数的 SELECT），则可以将其折叠到父查询中，从而允许对两个查询级别进行联合优化。默认情况下，如果父查询仅引用 WITH 查询一次，则会发生这种情况，但如果父查询多次引用 WITH 查询，则不会发生这种情况。可以通过指定 MATERIALIZED 来覆盖该决策，以强制单独计算 WITH 查询，或者通过指定 NOT MATERIALIZED 来强制将其合并到父查询中。后一种选择有重复计算 WITH 查询的风险，但如果 WITH 查询的每个用法仅需要 WITH 查询的全部输出的一小部分，则仍然可以节省总体成本。
这些规则的一个简单示例是
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
此 WITH 查询将被折叠，从而产生与以下代码相同的执行计划
SELECT * FROM big_table WHERE key = 123;
特别是，如果 key 上有索引，则可能会使用它来仅获取具有 key = 123 的行。另一方面，在
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
中，WITH 查询将被物化，从而生成 big_table 的临时副本，然后将其自身连接，而无需任何索引的帮助。如果写成如下形式，则此查询的执行效率会更高
WITH w AS NOT MATERIALIZED (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
以便可以将父查询的限制直接应用于对 big_table 的扫描。
一个 NOT MATERIALIZED 可能不受欢迎的示例是
WITH w AS (
SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
在此，WITH 查询的物化可确保对每个表行仅评估一次 very_expensive_function，而不是两次。
上面的示例仅显示 WITH 与 SELECT 一起使用，但可以以相同的方式附加到 INSERT，UPDATE，DELETE 或 MERGE。在每种情况下，它都有效地提供了可以在主命令中引用的临时表。
WITH 中的数据修改语句 #您可以在 WITH 中使用数据修改语句（INSERT、UPDATE、DELETE 或 MERGE）。这允许您在同一查询中执行多个不同的操作。一个例子是
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
此查询有效地将行从 products 移动到 products_log。WITH 中的 DELETE 从 products 中删除指定的行，并通过其 RETURNING 子句返回其内容；然后，主查询读取该输出并将其插入到 products_log 中。
上面示例的一个细微之处是，WITH 子句附加到 INSERT，而不是 INSERT 中的子 SELECT。这是必要的，因为仅允许在附加到顶级语句的 WITH 子句中使用数据修改语句。但是，正常的 WITH 可见性规则适用，因此可以从子 SELECT 中引用 WITH 语句的输出。
WITH 中的数据修改语句通常具有 RETURNING 子句（请参见第 6.4 节），如上面的示例所示。它是 RETURNING 子句的输出，而不是数据修改语句的目标表，它构成了可以由查询其余部分引用的临时表。如果 WITH 中的数据修改语句缺少 RETURNING 子句，则它不会形成临时表，并且无法在查询的其余部分中引用。该语句仍将执行。一个不是特别有用的示例是
WITH t AS (
DELETE FROM foo
)
DELETE FROM bar;
此示例将从表 foo 和 bar 中删除所有行。报告给客户端的受影响的行数将仅包括从 bar 中删除的行。
不允许在数据修改语句中进行递归自引用。在某些情况下，可以通过引用递归 WITH 的输出来解决此限制，例如
WITH RECURSIVE included_parts(sub_part, part) AS (
SELECT sub_part, part FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_parts);
此查询将删除产品的所有直接和间接子部件。
无论主查询是否读取其全部（或实际上任何）输出，WITH 中的数据修改语句都将精确执行一次，并始终执行完毕。请注意，这与 WITH 中的 SELECT 的规则不同：如上一节所述，SELECT 的执行仅在主查询需要其输出时才进行。
WITH 中的子语句与彼此以及主查询并发执行。因此，在 WITH 中使用数据修改语句时，实际发生指定更新的顺序是不可预测的。所有语句均使用相同的 快照 执行（请参阅第 13 章），因此它们无法 “看到” 彼此对目标表的影响。这减轻了行更新的实际顺序不可预测的影响，并且意味着 RETURNING 数据是在不同的 WITH 子语句和主查询之间传达更改的唯一方法。一个例子是，在
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
中，外部 SELECT 将返回 UPDATE 操作之前的原始价格，而在
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
中，外部 SELECT 将返回更新后的数据。
不支持在单个语句中两次尝试更新同一行。仅发生一项修改，但是要可靠地预测哪一项修改并不容易（有时甚至不可能）。这也适用于删除同一语句中已更新的行：仅执行更新。因此，通常应避免尝试在单个语句中两次修改同一行。特别是，请避免编写可能影响主语句或同级子语句更改的同一行的 WITH 子语句。这种语句的效果是不可预测的。
目前，在 WITH 中用作数据修改语句目标的任何表都不能具有条件规则，也不能具有 ALSO 规则，也不能具有展开为多个语句的 INSTEAD 规则。
如果您在文档中发现任何不正确、与您使用特定功能的体验不符或需要进一步澄清的地方，请使用此表格报告文档问题。