PostgreSQL / 系统课时

使用公共表表达式

使用公共表表达式

本课解决复杂 SQL 嵌套过深、难以阅读和调试的问题。你将使用 WITH 定义公共表表达式,将聚合、过滤和最终查询拆成清晰步骤。

学习目标

  • 掌握 WITH 的基本语法
  • 用 CTE 拆分复杂查询
  • 理解 CTE 的作用域与执行边界

CTE 的基本结构

公共表表达式通常简称 CTE。它在一条 SQL 语句开头定义一个临时结果集,后续查询可以像读取表一样引用它。

基本形式是 WITH 名称 AS (子查询) 主查询。一个 WITH 可以定义多个 CTE,后面的 CTE 还能引用前面已经定义的 CTE。

主示例:筛选销售额达标的分类

下面的 SQL 创建销售明细,先在 CTE 中按分类汇总,再由主查询筛选销售额不低于 1000 的分类。

sql
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;

预期结果

text
 category | total_amount
----------+-------------
 数码     |     1500.00
 图书     |     1100.00

category_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 不是自动提升性能的语法。它首先解决的是可读性和分步表达问题。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用公共表表达式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通