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

– 7.8.1. WITH 中的 SELECT

– 7.8.2. 递归查询

– 7.8.3. 公共表表达式的物化

– 7.8.4. WITH 中的数据修改语句

WITH 用于编写辅助语句，供一个更大的查询使用。这些语句通常称为公共表表达式（Common Table Expression，简称 CTE），可以理解为只在单次查询中存在的临时表。WITH 子句中的每条辅助语句都可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE；WITH 子句自身附属于一条主语句，而主语句也可以采用这五种语句之一。

7.8.1. 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 的两条辅助语句：top_regions 使用 regional_sales 的输出，主 SELECT 查询再使用 top_regions 的输出。不使用 WITH 也能编写本例，但需要两层嵌套的子 SELECT。这种写法更容易理解。

7.8.2. 递归查询

可选的 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）、一个递归项依次组成；只有递归项可以引用查询自身的输出。这样的查询按以下方式执行：

**递归查询求值**

1. 对非递归项求值。使用 UNION 时（不包括 UNION ALL），丢弃重复行。将所有剩余行加入递归查询结果，并放入一个临时工作表。

2. 只要工作表不为空，就重复以下步骤：

– 对递归项求值，使用工作表的当前内容代替递归自引用。使用 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

7.8.2.1. 搜索顺序

使用递归查询遍历树时，你可能希望按照深度优先或广度优先顺序排列结果。可以在其他数据列旁计算一个排序列，最后根据它排序。注意，这并不会真正控制查询求值时访问行的顺序；和 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 的输出行。

7.8.2.2. 环检测

使用递归查询时，必须确保递归部分最终不再返回任何元组，否则查询会无限循环。有时可以使用 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 并根据路径列排序更高效。如果需要广度优先顺序，同时指定二者则可能有用。

在测试时，如果不确定查询是否可能循环，一个实用技巧是在父查询中加入 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 查询的全部输出。

7.8.3. 公共表表达式的物化

WITH 查询有一个实用特性：在父查询每次执行期间，它通常只求值一次，即使父查询或同级 WITH 查询多次引用它。因此，多处需要的昂贵计算可以放入 WITH 查询，以避免重复工作。另一个用途是防止带有副作用的函数被不必要地多次求值。

不过，这也有另一面：优化器无法将父查询的限制条件下推到一个被多次引用的 WITH 查询中，因为这种下推可能影响所有使用该查询输出的地方，而限制原本只应影响其中一次使用。被多次引用的 WITH 查询会按原样求值，不会预先排除父查询后来可能丢弃的行。

不过，如前所述，如果引用处只需要有限数量的行，求值仍可能提前停止。

如果 WITH 查询既非递归又没有副作用，即它是一个不包含 volatile 函数的 SELECT，那么它可以折叠进父查询，使两个查询层次一起优化。默认情况下，父查询只引用 WITH 查询一次时会这样处理，多次引用时则不会。

你可以用 MATERIALIZED 强制单独计算 WITH 查询，也可以用 NOT MATERIALIZED 强制将其合并进父查询，以覆盖默认决定。后者可能使 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。在这些情况下，它实际上提供了可供主命令引用的临时表。

7.8.4. 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 的输出，而非数据修改语句的目标表。如果语句缺少 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 中的各子语句会彼此并发执行，也与主查询并发执行。因此，使用其中的数据修改语句时，指定更新实际发生的顺序无法预测。所有语句使用相同的快照，参见第 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 规则。

—

来源：PostgreSQL 18 文档，第7.8节，冻结原文入口为 https://www.postgresql.org/docs/current/queries-with.html 。本文为中文翻译，SQL示例保留原样。

PostgreSQL License

PostgreSQL Database Management System (also known as Postgres, formerly as Postgres95)

Portions Copyright © 1996–2026, The PostgreSQL Global Development Group

Portions Copyright © 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.