PostgreSQL / 系统课时
使用公共表表达式
使用公共表表达式
本课解决复杂 SQL 嵌套过深、难以阅读和调试的问题。你将使用 WITH 定义公共表表达式,将聚合、过滤和最终查询拆成清晰步骤。
学习目标
- 掌握
WITH的基本语法 - 用 CTE 拆分复杂查询
- 理解 CTE 的作用域与执行边界
CTE 的基本结构
公共表表达式通常简称 CTE。它在一条 SQL 语句开头定义一个临时结果集,后续查询可以像读取表一样引用它。
基本形式是 WITH 名称 AS (子查询) 主查询。一个 WITH 可以定义多个 CTE,后面的 CTE 还能引用前面已经定义的 CTE。
主示例:筛选销售额达标的分类
下面的 SQL 创建销售明细,先在 CTE 中按分类汇总,再由主查询筛选销售额不低于 1000 的分类。
CREATE TEMP TABLE sales (
id integer PRIMARY KEY,
category text NOT NULL,
amount numeric(10, 2) NOT NULL
);
INSERT INTO sales (id, category, amount) VALUES
(1, '图书', 420.00),
(2, '图书', 680.00),
(3, '数码', 1500.00),
(4, '家居', 300.00);
WITH category_total AS (
SELECT
category,
SUM(amount) AS total_amount
FROM sales
GROUP BY category
)
SELECT category, total_amount
FROM category_total
WHERE total_amount >= 1000
ORDER BY total_amount DESC;预期结果
category | total_amount
----------+-------------
数码 | 1500.00
图书 | 1100.00category_total 只在当前 SQL 语句内有效。语句结束后,它不会成为真实表,也不会保存数据。
多个步骤的组织方式
复杂查询可以按“原始过滤、聚合计算、排名或二次筛选”的顺序定义多个 CTE。名称应表达结果含义,例如 paid_orders、daily_total,不要使用 temp1、data2 这类难以理解的名称。
CTE 的价值主要是提高结构清晰度。拆分后仍应检查每一步是否必要,避免把简单查询切成过多层次。
执行与优化边界
在现代 PostgreSQL 中,满足条件的普通 CTE 可能被优化器内联。需要明确控制时,可以使用 MATERIALIZED 强制先计算结果,或使用 NOT MATERIALIZED 允许优化器展开;是否更快应通过 EXPLAIN ANALYZE 验证,而不是只凭语法判断。
CTE 也可以与 INSERT、UPDATE、DELETE 配合,但数据修改型 CTE 需要特别关注执行顺序和事务影响。
常见错误
CTE 名称只在当前语句中可见,不能在下一条 SQL 中继续引用。主查询引用的列名必须来自 CTE 输出列,聚合别名也只能在 CTE 外层使用。
另外,WITH 不是自动提升性能的语法。它首先解决的是可读性和分步表达问题。