PostgreSQL WITH 子句（长文解析）

PostgreSQL WITH 子句：让复杂查询变得清晰优雅

在日常开发中，我们常常需要处理多层嵌套的 SQL 查询。比如要统计每个部门的平均薪资，同时还要找出高于平均值的员工。这类需求如果用传统子查询写，代码会变得冗长、难以维护，甚至让人望而生畏。而 PostgreSQL 提供了一种更优雅的解决方案——WITH 子句。

这就像你在整理房间时，不会直接把所有东西都堆在桌上，而是先用几个收纳盒把物品分类，再逐一处理。WITH 子句就是 SQL 里的“收纳盒”，它允许你先定义一组临时的、可重用的查询结果，然后再在主查询中引用它们。这种方式不仅让代码更清晰，还能提升性能。

什么是 PostgreSQL WITH 子句

WITH 子句是 PostgreSQL 中一种强大的语法结构，用于定义“临时结果集”，也叫“公共表表达式”（CTE，Common Table Expression）。它在当前查询执行期间有效，查询结束后自动释放，不会影响数据库的长期状态。

想象一下，你正在做一道复杂的数学题，需要先算出 A、B、C 三个中间值，再用它们来求最终结果。WITH 子句就相当于在草稿纸上写下 A = 10, B = 5, C = 3，然后在正式计算时直接引用这些中间结果，而不是每次都重新算一遍。

它语法简洁，结构清晰，特别适合处理递归查询、多层数据聚合、复杂逻辑拆分等场景。

基本语法与使用示例

WITH 子句的基本结构如下：

WITH 临时表名 AS (
    SELECT 字段列表
    FROM 表名
    WHERE 条件
)
SELECT *
FROM 临时表名
-- 可以继续使用其他表或嵌套 WITH

我们来看一个具体的例子。假设有一个员工表 employees，包含字段：id（员工编号）、name（姓名）、department（部门）、salary（薪资）。

-- 定义一个名为 dept_avg 的临时表，计算每个部门的平均薪资
WITH dept_avg AS (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
-- 主查询：找出薪资高于部门平均值的员工
SELECT 
    e.name,
    e.department,
    e.salary,
    d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC;

这段代码的逻辑非常清晰：

• 第一步：用 WITH 定义 dept_avg，计算每个部门的平均薪资；
• 第二步：在主查询中通过 JOIN 把员工表和这个临时结果连接起来；
• 第三步：筛选出薪资高于平均值的员工。

这种写法比嵌套子查询更直观，也更容易调试。

多个 CTE 的组合使用

WITH 子句支持定义多个临时表，它们之间可以相互引用，也可以独立使用。这就像你有多个文件夹，每个文件夹放不同类别的资料，最终组合起来完成任务。

比如，我们想分析“高薪员工”和“低薪员工”的分布情况，同时还要统计各部门的员工总数。

WITH 
-- 第一个 CTE：计算每个部门的平均薪资
dept_avg AS (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
-- 第二个 CTE：统计每个部门的员工总数
dept_count AS (
    SELECT 
        department,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
),
-- 第三个 CTE：找出薪资高于部门平均值的员工
high_earners AS (
    SELECT 
        e.id,
        e.name,
        e.department,
        e.salary,
        d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)

-- 主查询：汇总所有信息
SELECT 
    h.department,
    h.name,
    h.salary,
    h.avg_salary,
    c.emp_count AS total_employees,
    ROUND((h.salary - h.avg_salary) * 100.0 / h.avg_salary, 2) AS salary_premium_percent
FROM high_earners h
JOIN dept_count c ON h.department = c.department
ORDER BY h.salary DESC;

在这个例子中：

• dept_avg 提供平均薪资；
• dept_count 提供部门人数；
• high_earners 是基于前两个结果的筛选结果；
• 主查询将三者合并，输出详细的分析报表。

这种“分步构建”的方式，让复杂逻辑变得可读、可维护。

注：上表为示例数据，实际结果取决于原始数据。

递归查询中的强大应用

PostgreSQL 的 WITH 子句最惊艳的功能之一是支持递归 CTE，它可以处理树形结构或层级数据，比如组织架构、分类目录、文件夹路径等。

举个例子：我们有一个 org_chart 表，记录员工和上级的关系，字段为 employee_id、name、manager_id。

-- 递归 CTE：找出某个员工的所有下属（包括间接下属）
WITH RECURSIVE 
-- 第一层：初始值，从指定员工开始（如经理 ID = 1）
subordinates AS (
    -- 基础部分：选择根节点（经理）
    SELECT 
        employee_id,
        name,
        manager_id,
        0 AS level
    FROM org_chart
    WHERE employee_id = 1  -- 从经理开始

    UNION ALL

    -- 递归部分：连接下属
    SELECT 
        o.employee_id,
        o.name,
        o.manager_id,
        s.level + 1  -- 层级加 1
    FROM org_chart o
    INNER JOIN subordinates s ON o.manager_id = s.employee_id
)
-- 查询所有下属，按层级排序
SELECT 
    employee_id,
    name,
    manager_id,
    level
FROM subordinates
ORDER BY level, name;

这个例子中：

• RECURSIVE 关键字表示这是一个递归查询；
• 第一个 SELECT 是递归的“起点”；
• UNION ALL 连接起点和递归结果；
• 每次递归都会向下查找下一层级的员工。

这种写法可以轻松处理无限层级的组织结构，而不用写复杂的循环逻辑。

实用技巧与最佳实践

在实际项目中，使用 PostgreSQL WITH 子句有一些值得推荐的技巧：

1. 命名清晰：给每个 CTE 起一个有意义的名字，如 sales_summary、user_active_stats，避免使用 cte1、temp 这类模糊名称。

2. 避免重复计算：如果多个地方需要使用同一个中间结果，用 WITH 定义一次，多次引用，比重复写子查询更高效。

3. 注意性能：虽然 CTE 有助于可读性，但过度嵌套或在大表上频繁使用，可能影响性能。建议在关键路径上做执行计划分析。

4. 可调试性强：你可以先单独运行 CTE 部分，验证中间结果是否正确，再整合进主查询，极大提升调试效率。

5. 支持视图与函数：CTE 不仅能在查询中使用，还能作为视图或函数的内部逻辑，增强代码复用性。

总结与展望

PostgreSQL WITH 子句是现代 SQL 中一项非常实用且优雅的特性。它让复杂查询不再“一团乱麻”，而是像搭积木一样，一步步构建出清晰的逻辑结构。无论是处理数据聚合、多表关联，还是递归层级查询，它都能提供简洁、高效、易维护的解决方案。

对于初学者来说，掌握 WITH 子句是迈向高级 SQL 的重要一步。对于中级开发者，它能显著提升代码质量与团队协作效率。在日常开发中，遇到复杂的查询逻辑，不妨先问问自己：“能不能用 WITH 子句来拆解？”

随着业务数据的复杂化，SQL 的可读性与可维护性变得越来越重要。PostgreSQL WITH 子句正是为此而生。掌握它，不仅能写出更好的 SQL，也能让你在技术成长的道路上走得更远。