公用表表达式(CTE)是写在主语句之前 WITH 子句中的具名子查询:WITH name AS (SELECT ...) SELECT ... FROM name。主查询像读表一样读取 CTE,于是层层嵌套的子查询就变成了从上往下读的一个个步骤。
CTE totals 汇总每位客户的订单金额,主查询只保留总额超过 100 的客户:cy 是 300,ana 是 180。CTE 只在它所属的语句中存在。
串联多个 CTE
多个 CTE 之间用逗号分隔。每个 CTE 都可以读取在它之前定义的 CTE。
average 读取 totals,主查询同时读取两者。客户总额分别是 180、70 和 300,平均约为 183,所以只有 cy 高于平均值。
用 WITH RECURSIVE 遍历树
WITH RECURSIVE 允许 CTE 引用自身。它由 UNION ALL 连接的两部分组成:
- 锚点查询,返回起始的行;
- 递归查询,把 CTE 和表连接起来,找到下一层。
PostgreSQL 对上一步产生的行运行递归部分,当某一步没有返回任何行时就停止。
每一步都找出直接向上一步那些人汇报的人。Mia 最终的深度是 3,路径是 Rosa > Omar > Ivo > Mia。如果只想列出某位经理手下的所有人,而不是整棵树,把锚点的 WHERE 改成 id = 2。
要从某个员工向上走到顶层,把连接条件换成 s.id = c.manager_id。
停止无限递归的 CTE
递归 CTE 只有在某一步什么都没返回时才会结束。如果数据有环(A 向 B 汇报,B 又向 A 汇报),它就永远不会结束。加上深度限制,比如 WHERE c.depth < 20,或者使用 CYCLE 子句(PostgreSQL 14+),它会标记第一个重复的行,不再沿着它继续:
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
用递归 CTE 生成行
递归部分不一定需要表。下面把每一行的值再送回去,从 1 数到 5:
如果只是简单的数字或日期序列,generate_series 写起来更短。
用修改数据的 CTE 移动行
CTE 可以是带 RETURNING 子句的 INSERT、UPDATE 或 DELETE,主语句读取它返回的行。下面用一条语句把旧订单移到归档表中:
订单 1 和 3 被删除,同时插入到归档表中,所以一旦失败,两张表都会保持原样。语句的所有部分看到的是同一个快照:主查询看不到 DELETE 对 orders 的影响,只能看到 RETURNING 传过来的行(见 RETURNING)。
用 MATERIALIZED 控制内联
从 PostgreSQL 12 开始,主查询只引用一次且没有副作用的 CTE 会被内联。查询规划器把它和外层查询一起优化,所以写在 CTE 外面的 WHERE 仍然可以使用索引。被引用两次或以上的 CTE 只计算一次,结果会被复用。两种行为都可以强制指定:
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
MATERIALIZED 让 CTE 单独计算一次,外层的 WHERE 不会推进去。如果内联后的计划反而更慢,就用它。NOT MATERIALIZED 要求即使 CTE 被引用多次也进行内联。递归 CTE 和修改数据的 CTE 总是单独计算。
常见问题
PostgreSQL 中 CTE 比子查询快吗?
通常谁也不比谁快。从 PostgreSQL 12 开始,只被引用一次且没有副作用的 CTE 会被内联到主查询中,所以它和等价的子查询得到相同的执行计划。在 12 版之前,每个 CTE 都单独计算,这可能导致用不上索引。
UPDATE 或 DELETE 中能用 CTE 吗?
能。WITH 可以放在 INSERT、UPDATE、DELETE 和 MERGE 之前,而 CTE 本身也可以是带 RETURNING 的 INSERT、UPDATE 或 DELETE,主语句读取它返回的行。
怎么避免递归 CTE 无限循环?
给递归部分加一个最终会返回零行的条件,比如深度限制(WHERE depth < 10)。对于可能绕回自身的数据,CYCLE 子句(PostgreSQL 14+)会标记重复的行并在那里停下。
一个 CTE 能在多条查询中使用吗?
不能。CTE 只在它所在的那条语句中存在。要在多条语句之间复用一个查询,就创建视图或临时表。