1.7查询（公用表表达式）WITH7.8. 查询（公用表表达式）WITH 7.8.1. 在 WITH 中选择 7.8.2

7.8. 查询（公用表表达式）`WITH`

- 7.8.1. 在 WITH 中选择
- 7.8.2. WITH 中的数据修改语句

WITH提供了一种编写辅助语句以用于较大查询的方法。这些语句通常称为公用表表达式或 CTE，可以认为它们定义仅针对一个查询存在的临时表。子句中的每个辅助语句可以是、、或 ;子句本身附加到主语句，主语句也可以是、、或。WITH``SELECT``INSERT``UPDATE``DELETE``WITH``SELECT``INSERT``UPDATE``DELETE

7.8.1. 在SELECT``WITH

in 的基本价值是将复杂的查询分解为更简单的部分。一个例子是：SELECT``WITH

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;

仅显示顶级销售区域中每个产品的销售总额。该子句定义了两个名为和的辅助语句，其中的输出用于主查询，输出用于主查询。这个例子可以在没有的情况下编写，但我们需要两级嵌套的 sub-s。遵循这种方式更容易一些。WITH``regional_sales``top_regions``regional_sales``top_regions``top_regions``SELECT``WITH``SELECT

可选修饰符从单纯的语法便利转变为完成标准 SQL 中不可能完成的功能。使用，查询可以引用其自己的输出。一个非常简单的例子是这个查询，它对从 1 到 100 的整数求和：RECURSIVE``WITH``RECURSIVE``WITH

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
只要工作表不为空，请重复以下步骤：
1. 评估递归项，将工作表的当前内容替换为递归自引用。对于（但不是），放弃重复的行和与任何先前结果行重复的行。在递归查询的结果中包含所有剩余的行，并将它们放在临时中间表中。UNION``UNION ALL
2. 将工作表的内容替换为中间表的内容，然后清空中间表。

注意

虽然允许以递归方式指定查询，但在内部以迭代方式计算此类查询。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

使用递归查询时，请务必确保查询的递归部分最终不会返回元组，否则查询将无限循环。有时，使用 instead 可以通过丢弃与以前的输出行重复的行来实现此目的。但是，循环通常不涉及完全重复的输出行：可能需要只检查一个或几个字段以查看以前是否达到过相同的点。处理此类情况的标准方法是计算已访问值的数组。例如，请考虑以下使用字段搜索表的查询：UNION``UNION ALL``graph``link

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
)
SELECT * FROM search_graph;

如果关系包含循环，则此查询将循环。因为我们需要一个“深度”输出，所以仅仅更改为不会消除循环。相反，我们需要识别在遵循特定链接路径时是否再次到达同一行。我们在容易循环的查询中添加两列和：link``UNION ALL``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],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

除了防止循环之外，数组值本身通常很有用，因为它表示到达任何特定行所采用的“路径”。

在需要检查多个字段以识别循环的一般情况下，请使用行数组。例如，如果我们需要比较字段和：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)],
      false
    FROM graph g
  UNION ALL
    SELECT 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 sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

提示

在只需要检查一个字段即可识别循环的常见情况下省略语法。这允许使用简单的阵列而不是复合型阵列，从而提高效率。ROW()

提示

递归查询评估算法按广度优先搜索顺序生成其输出。您可以通过使外部查询成为以这种方式构造的“路径”列，以深度优先搜索顺序显示结果。ORDER BY

当您不确定查询是否可能循环时，测试查询的一个有用技巧是在父查询中放置 a。例如，此查询将永远循环，而没有：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``SELECT``INSERT``UPDATE``DELETE

7.8.2. 中的数据修改语句`WITH`

可以在中使用数据修改语句（、或）。这允许您在同一查询中执行多个不同的操作。一个例子是：INSERT``UPDATE``DELETE``WITH

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;

此查询有效地将行从移动到。in 从中删除指定的行，通过其子句返回其内容;然后主查询读取该输出并将其插入 .products``products_log``DELETE``WITH``products``RETURNING``products_log

上面示例的一个很好的点是，子句附加到，而不是中的子。这是必需的，因为数据修改语句只允许在附加到顶级语句的子句中使用。但是，正常的可见性规则适用，因此可以引用 sub- 的语句输出。WITH``INSERT``SELECT``INSERT``WITH``WITH``WITH``SELECT

中的数据修改语句通常包含子句（参见第 6.4 节），如上例所示。它是子句的输出，而不是数据修改语句的目标表，它构成了可由查询的其余部分引用的临时表。如果中的数据修改语句缺少子句，则它不会形成临时表，并且不能在查询的其余部分引用。尽管如此，这样的声明仍将执行。一个不是特别有用的例子是：WITH``RETURNING``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);

此查询将删除产品的所有直接和间接子部分。

中的数据修改语句只执行一次，并且始终执行完成，与主查询是否读取其所有（或实际上任何）输出无关。请注意，这与 in 的规则不同：如上一节所述，仅当主查询需要其输出时才执行 a。WITH``SELECT``WITH``SELECT

中的子语句彼此并发执行，并与主查询并发执行。因此，在中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用相同的快照执行（参见第 13 章），因此它们无法“看到”彼此对目标表的影响。这减轻了行更新的实际顺序不可预测性的影响，并意味着数据是在不同子语句和主查询之间传达更改的唯一方法。这方面的一个例子是，在WITH``WITH``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

1.7查询（公用表表达式）WITH

7.8. 查询（公用表表达式）WITH

7.8.1. 在SELECT``WITH

注意

提示

提示

7.8.2. 中的数据修改语句WITH

7.8. 查询（公用表表达式）`WITH`

7.8.2. 中的数据修改语句`WITH`