答案

指令WITH（或CTE — 公共表表达式）临时创建一个结果集，可以在主SELECT中使用，甚至可以递归使用。CTE使查询更易于理解，提高了可读性，并允许重用中间结果。

与普通子查询的优点：

示例：

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

“在声明CTE时，指明MATERIALIZED/NOT MATERIALIZED重要吗？这会如何影响性能？”

回答： 在PostgreSQL中，可以明确指定MATERIALIZED（总是计算CTE一次并存储，即使多次使用）或NOT MATERIALIZED（CTE将内嵌在主查询中）。通常优化器会自行选择是否物化CTE，但强制指定有时会显著影响速度。

示例：

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

历史

在一个大型项目中，分析师在不知道PostgreSQL在12版本之前CTE总是被物化的情况下，将所有子查询替换为CTE。结果是，查询速度降低了2-3倍。回归到子查询或升级到新版本后，问题解决了。

历史

一个包含多个嵌套子查询的报告对同事来说变得难以阅读。使用有意义名称重写为CTE后，查询的讨论和维护显著简化。

历史

两个具有相同名称的CTE在大型脚本中引起了混淆：在CTE声明之前就已使用了CTE。结果是——编译错误和在查找原因上花费了大量时间。

WITH指令（公共表表达式，CTE）做什么？使用CTE相比于子查询有什么优点？请举例说明。