PostgreSQL WITH 子句深度解析：面向 2026 的现代化 SQL 开发指南

2026-02-09 10:22:32 0条评论 3次阅读 0人点赞

在我们日常的数据库开发工作中，面对长达数百行的复杂 SQL 语句，那种头昏脑涨的感觉我们大家都很熟悉。层层嵌套的子查询不仅难以阅读，而且在调试时往往让人无从下手。别担心，在 PostgreSQL 中，有一个非常强大且优雅的工具能够帮我们解决这个问题——那就是 WITH 子句，也就是我们常说的 公用表表达式（CTE，Common Table Expressions）。

随着我们步入 2026 年，数据库技术栈与 AI 辅助开发的深度融合，使得编写“人类可读”的代码变得前所未有的重要。在这篇文章中，我们将作为并肩作战的开发者，深入探讨 WITH 子句 的方方面面。我们不再仅仅满足于简单的语法罗列，而是通过真实的企业级数据场景，结合现代开发工作流，详细解析它是如何将复杂的查询逻辑拆解为清晰、可管理的部分，以及如何利用它来优化我们的数据库性能和代码维护体验。无论你是刚入门 PostgreSQL 的新手，还是希望进阶的老手，这篇文章都将为你提供 2026 年视角下的实用见解和最佳实践。

1 什么是 WITH 子句（CTE）？
2 基础语法与结构
3 准备工作：创建示例环境
4 实战示例 1：简化基础聚合查询
5 实战示例 2：结合过滤条件的精准查询
6 实战示例 3：多 CTE 协同工作（模块化思维）
7 实战示例 4：嵌套 CTE 解决层级问题
8 实战示例 5：数据变更中的 CTE 应用（DML 增强）
9 2026 前沿视角：递归 CTE 与图数据处理
10 现代开发工作流：CTE 与 AI 辅助编程
11 性能优化与 2026 最佳实践
12 常见错误与解决方案
13 总结

什么是 WITH 子句（CTE）？

简单来说，WITH 子句 允许我们在执行主查询之前，先定义一个或多个临时的“结果集”（也就是 CTE）。你可以把它想象成在写代码时定义的一个临时变量或函数。这个临时结果集在随后的主查询中可以被像普通表一样反复引用。

为什么要使用它？

使用 CTE 不仅仅是为了让代码看起来更“高级”，更是为了提升代码的 可读性 和 可维护性。在 2026 年的开发理念中，代码即文档。想象一下，当你需要在一个查询中多次使用相同的复杂逻辑（例如计算某个复杂的加权平均值或处理时间序列数据）时，如果不使用 CTE，你可能不得不重复粘贴这段子查询代码，这不仅导致 SQL 臃肿，增加了出错的风险，还会让 AI 辅助工具（如 GitHub Copilot 或 Cursor）难以理解你的意图。通过 WITH 子句，我们可以将这部分逻辑封装起来，命名清晰，随取随用，实现了真正的 模块化 SQL。

基础语法与结构

让我们先通过一个标准的语法结构来看看它是如何工作的。

WITH cte_name AS (
    -- 这里是定义 CTE 的查询语句
    SELECT column1, column2
    FROM source_table
    WHERE some_condition
)
-- 这里是主查询，它可以引用上面的 cte_name
SELECT *
FROM cte_name;

核心参数解析：

INLINECODE71e8539a: 这是你给这个临时结果集起的名字。建议使用具有描述性的名称，比如 INLINECODEeeb449c0 或 monthly_sales，这样代码读起来就像自然语言一样流畅。
SELECT statement: 这是你用来生成临时数据的查询逻辑。在这里可以使用聚合、过滤、连接等任何标准 SQL 操作。
主查询: 定义完 CTE 后，必须紧跟一个主查询（SELECT、INSERT、UPDATE 或 DELETE）来使用这个 CTE。如果不使用，CTE 就没有意义了。

准备工作：创建示例环境

为了让你能直观地感受到 WITH 子句的威力，我们将创建一个实际的业务场景。假设我们正在为一个公司开发内部管理系统，我们需要处理员工、部门和薪资数据。

请执行以下 SQL 脚本来创建我们后续示例所需的 employees 表并插入初始数据：

-- 创建员工表，包含 ID、姓名、部门、薪资和入职日期
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary NUMERIC NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

-- 插入一些演示数据，涵盖不同部门和薪资水平
INSERT INTO employees (name, department, salary, hire_date) VALUES
(‘Alice‘, ‘HR‘, 50000, ‘2021-03-10‘),
(‘Bob‘, ‘IT‘, 60000, ‘2020-06-15‘),
(‘Charlie‘, ‘HR‘, 55000, ‘2019-11-01‘),
(‘David‘, ‘IT‘, 65000, ‘2021-01-20‘),
(‘Eva‘, ‘Finance‘, 70000, ‘2018-05-05‘),
(‘Frank‘, ‘IT‘, 72000, ‘2022-02-10‘),
(‘Grace‘, ‘Sales‘, 45000, ‘2021-09-12‘),
(‘Helen‘, ‘Sales‘, 48000, ‘2021-09-15‘);

现在我们的数据环境已经准备好了，让我们开始实战演练。

实战示例 1：简化基础聚合查询

在第一个例子中，我们面临一个简单的需求：查看每个部门的平均薪资。虽然我们直接使用 GROUP BY 就能做到，但为了演示 CTE 的基本用法，我们把它封装在一个 WITH 子句中。这在业务逻辑更复杂时（例如涉及多步计算）会非常有用。

场景： 我们需要计算各部门的平均薪资并展示出来。

-- 定义 CTE：计算平均薪资
WITH avg_salaries AS (
    SELECT 
        department, 
        AVG(salary) AS avg_salary -- 计算平均值并重命名列
    FROM employees
    GROUP BY department
)
-- 主查询：直接从 CTE 中读取数据
SELECT * 
FROM avg_salaries
ORDER BY avg_salary DESC; -- 按薪资降序排列

代码解析：

这里，INLINECODE6667afbe 充当了一个临时的汇总表。在主查询中，我们可以像操作普通表一样对它进行排序（INLINECODE6a771e67）或再次过滤。这种写法将“计算逻辑”与“展示逻辑”清晰地分离开来。

实战示例 2：结合过滤条件的精准查询

CTE 的真正威力在于它可以极大地简化复杂的逻辑。假设我们需要找出那些“平均薪资高于公司整体平均水平”的部门。如果不使用 CTE，我们可能需要在一个查询中嵌套另一个子查询，导致代码结构混乱。

场景： 列出平均薪资大于 60,000 的部门。

-- 第一步：定义 CTE 计算各部门平均值
WITH avg_salaries AS (
    SELECT 
        department, 
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
-- 第二步：在主查询中基于 CTE 的结果进行过滤
SELECT department, avg_salary
FROM avg_salaries
WHERE avg_salary > 60000;

输出分析：

运行上述代码后，你会发现只有 IT 部门和 Finance（财务）部门出现在结果列表中。这不仅提高了查询的可读性，还让性能优化器更容易理解我们的意图，特别是在处理大数据集时，清晰的逻辑有助于 PostgreSQL 选择最佳的执行计划。

实战示例 3：多 CTE 协同工作（模块化思维）

当我们需要在一个主查询中同时依赖多个不同的中间计算结果时，WITH 子句的优势就无可比拟了。我们可以定义多个 CTE，并用逗号分隔。

场景： 我们需要一份综合报告，同时显示“每个部门的总人数”和“平均薪资”。

-- 定义第一个 CTE：计算人数
WITH total_employees AS (
    SELECT 
        department, 
        COUNT(*) AS num_employees
    FROM employees
    GROUP BY department
),
-- 定义第二个 CTE：计算平均薪资（注意这里用逗号分隔）
avg_salaries AS (
    SELECT 
        department, 
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
-- 主查询：关联两个 CTE 得到最终结果
SELECT 
    t.department, 
    t.num_employees, 
    a.avg_salary
FROM total_employees t
JOIN avg_salaries a ON t.department = a.department
ORDER BY t.num_employees DESC;

为什么这样做？

你可以看到，我们将“统计人数”和“统计薪资”这两个独立的逻辑完全隔离开了。如果你以后需要修改薪资的计算公式（例如加入奖金），你只需要修改 INLINECODEedf5220c 这个 CTE，而完全不需要动 INLINECODE045c5935 或主查询的代码。这种 模块化 的思维是编写高质量 SQL 的关键，也非常符合现代软件工程中“关注点分离”的原则。

实战示例 4：嵌套 CTE 解决层级问题

在实际开发中，你经常会遇到基于“派生结果”再进行计算的情况。CTE 允许我们在定义中引用前面定义过的 CTE。

场景： 找出薪资高于“IT部门平均薪资”的所有员工（无论他们属于哪个部门）。这需要两个步骤：先算出 IT 的平均薪资，再用这个值去过滤所有员工。

-- 第一步：计算出 IT 部门的平均薪资
WITH it_avg_salary AS (
    SELECT AVG(salary) as it_avg
    FROM employees
    WHERE department = ‘IT‘
)
-- 第二步：在过滤条件中引用第一个 CTE 的结果
SELECT 
    name, 
    department, 
    salary
FROM employees, it_avg_salary
WHERE employees.salary > it_avg_salary.it_avg;

深入解析：

请注意主查询中的 INLINECODE493aa8de。这是一个交叉连接，因为 INLINECODE2675daa4 只包含一行数据。这种写法非常清晰：它明确告诉数据库“先算出这个基准值，再拿它去过滤大表”。相比之下，如果直接在 WHERE 子句里写子查询 (WHERE salary > (SELECT AVG...))，虽然功能相同，但在逻辑复杂时，代码的可读性会大打折扣。

实战示例 5：数据变更中的 CTE 应用（DML 增强）

WITH 子句不仅可以用于 INLINECODEcc171a5b 查询，还可以完美嵌入到 INLINECODEe735291b、INLINECODE59a9153c 或 INLINECODE5d80e472 语句中。这是一个非常高级且实用的技巧，特别是在处理需要复杂条件判断的数据更新时。

场景： 公司决定给所有薪资低于 50,000 的员工 涨薪 10%。我们不仅要执行更新，还想确保逻辑的原子性。

-- 定义 CTE：锁定需要涨薪的员工
WITH underpaid_emp AS (
    SELECT 
        id, 
        name, 
        salary, 
        salary * 1.1 AS new_salary -- 计算新薪资
    FROM employees
    WHERE salary < 50000
)
-- 执行更新操作，数据来源是 CTE
UPDATE employees
SET salary = underpaid_emp.new_salary
FROM underpaid_emp
WHERE employees.id = underpaid_emp.id;

实战价值：

这种写法保证了我们用于筛选的条件（“薪资 < 50000”）和更新逻辑是完全一致的，避免了手动写两次可能带来的误差。在 2026 年的微服务架构中，这种原子性的操作可以减少应用层代码与数据库层的交互次数，降低网络延迟。

2026 前沿视角：递归 CTE 与图数据处理

随着图数据库概念的普及和社交网络、知识图谱的广泛应用，处理树形或图结构数据变得越来越常见。WITH 子句中的 RECURSIVE 关键字是 PostgreSQL 处理此类问题的杀手锏。

场景： 假设我们有一张存储员工上下级关系的表 org_structure。我们需要查找某个员工的所有下级（直接或间接）。

-- 先创建递归示例表
CREATE TABLE org_structure (
    employee_id INT,
    manager_id INT,
    name VARCHAR(50)
);

INSERT INTO org_structure VALUES
(1, NULL, ‘CEO‘),
(2, 1, ‘CTO‘),
(3, 1, ‘CFO‘),
(4, 2, ‘Dev Lead‘),
(5, 2, ‘QA Lead‘),
(6, 4, ‘Senior Dev‘);

-- 递归查询：查找 CTO (ID=2) 下的所有员工
WITH RECURSIVE subordinates AS (
    -- 1. 初始成员：直接下级
    SELECT employee_id, name, manager_id
    FROM org_structure
    WHERE manager_id = 2  -- 从 CTO 开始
    
    UNION ALL
    
    -- 2. 递归成员：查找下级的下级
    SELECT o.employee_id, o.name, o.manager_id
    FROM org_structure o
    INNER JOIN subordinates s ON o.manager_id = s.employee_id
)
SELECT * FROM subordinates;

深度解析：

递归 CTE 的执行逻辑分为两部分：非递归项（找到初始节点）和 递归项（通过 JOIN 找到下一层节点），直到没有新数据为止。这种能力使得 PostgreSQL 在处理复杂层级数据（如权限系统、评论回复、物料清单 BOM）时，无需引入专门的图数据库即可胜任，大大简化了技术栈。

现代开发工作流：CTE 与 AI 辅助编程

在 2026 年，我们编写 SQL 的方式已经发生了根本性变化。当你使用 Cursor、Windsurf 或 GitHub Copilot 等工具时，WITH 子句 成为了沟通意图的最佳桥梁。

为什么 CTE 更适合 AI 辅助？

当我们使用 AI 生成 SQL 时，直接生成的嵌套子查询往往晦涩难懂，难以 Debug。而 CTE 强迫我们将问题分解为步骤，这正好符合人类和 AI 的逻辑思考链条。

Prompt 示例：“帮我写一个查询，先算出每个部门的平均销售额作为一个 CTE，然后在这个 CTE 的基础上，找出销售额排名前 10 的部门。”
AI 输出：AI 能够精准地生成包含 department_sales CTE 的代码，因为你的Prompt 结构与 CTE 的结构一一对应。

此外，当代码出现 Bug 时，你可以让 AI “只修改 department_sales 这个 CTE 的逻辑”，这种隔离性大大提高了调试效率，让我们能够专注于具体的业务逻辑模块，而不是陷入巨大的 SQL 嵌套地狱中。

性能优化与 2026 最佳实践

虽然 CTE 极大地提升了代码的可读性，但在性能方面，我们需要了解 PostgreSQL 的底层机制，并结合现代硬件环境做出最佳选择。

1. 优化器与物化

在早期的 PostgreSQL 版本中，CTE 往往被视为“优化栅栏”，意味着 CTE 总是会被物化（即独立计算并存储临时结果），这有时会导致性能不如直接的子查询。但从 PostgreSQL 12 开始，直到 2026 年的最新版本，查询优化器已经变得非常智能。对于简单的 CTE，它会自动进行“内联”处理，就像写子查询一样高效。但对于复杂的 CTE，或者当你明确需要强制物化以避免重复计算时，PostgreSQL 也能很好地处理。

2. 性能优化建议

必须使用 CTE：当需要递归查询（例如上文提到的树形结构）时，CTE 是唯一选择。
推荐使用 CTE：当代码逻辑复杂，包含多个引用相同子查询的部分时，CTE 能减少代码冗余。虽然在某些极端性能敏感的场景下，物化可能带来微小的 I/O 开销，但在现代服务器硬件（如 NVMe SSD 和大内存）下，这种开销通常可以忽略不计，而带来的开发效率提升是巨大的。
MATERIALIZED / NOT MATERIALIZED 提示：作为进阶开发者，你可以在 CTE 后面显式添加 INLINECODE83a10eac（强制物化，适合被多次引用的复杂计算）或 INLINECODE72641afa（强制内联，适合简单过滤），以微调执行计划。

3. 决策经验：何时不用？

如果你发现查询计划显示 CTE 导致了巨大的性能损耗（例如在大数据集上被多次物化），且逻辑非常简单（只有一层过滤），那么回退到传统的子查询或 LATERAL JOIN 可能是更优的选择。但在 99% 的业务场景中，请放心使用 CTE。

常见错误与解决方案

错误 1：忘记使用 CTE。定义了 CTE 却在主查询中没有引用它，PostgreSQL 会报错。

解决*：检查主查询的 INLINECODE1dcfd3f9 或 INLINECODEf7e6c422 子句，确保每个 CTE 都被调用。

错误 2：列名冲突。如果 CTE 中的列名与主查询中其他表的列名相同且没有明确别名，可能会导致歧义错误。

解决*：始终为 CTE 中的列起有意义的别名（如 INLINECODEef214ad2），并在引用时带上 CTE 名称前缀（如 INLINECODE33a282bf）。

总结

通过这篇文章，我们系统地探索了 PostgreSQL 的 WITH 子句（CTE）。从基本的语法结构，到多 CTE 的协同工作，再到递归查询和现代 AI 辅助开发环境下的应用，我们已经掌握了这项让 SQL 代码更加 模块化、易读且易维护 的核心技术。

在 2026 年，技术栈的复杂性要求我们写出更具表达力的代码。CTE 不仅仅是一个 SQL 语法糖，它是一种声明式编程思维的体现。对于你来说，下一步可以尝试在现有的项目中寻找那些冗长、难懂的 SQL 语句，尝试用 CTE 对它们进行重构。相信我，当你写出一个逻辑清晰、层次分明的查询时，不仅你的同事会感谢你，未来的你自己，甚至是协助你的 AI，都会庆幸做出了这样的选择。

现在，打开你的 PostgreSQL 客户端，让我们开始编写更优雅的代码吧！

投稿给我们	如何建站？
vps是什么？	如何安装宝塔？
如何通过博客赚钱？	便宜wordpress托管方案
免费wordpress主题	这些都是免费方案

豆丁博客