首页
/ 正文

PostgreSQL WITH 子句（一文讲透）

更新时间: 2025-04-16 14:23:05

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;演示链接： http://116.62.199.48:7070 ;

《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前，星球内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习，欢迎点击围观

前言：揭开 WITH 子句的神秘面纱

在数据库开发中，我们常常需要处理复杂的查询逻辑，例如多表关联、子查询嵌套或递归计算。当查询变得冗长时，代码可读性会急剧下降，维护成本也随之增加。此时，PostgreSQL WITH 子句（也称为 Common Table Expressions, CTE）便成为了解决这一问题的“瑞士军刀”。它通过将复杂逻辑拆分为多个步骤，让查询结构更加清晰，同时提升代码的复用性和执行效率。

本文将从基础语法、实际案例到性能优化，全面解析 PostgreSQL WITH 子句的核心用法，并通过类比和代码示例帮助读者快速掌握这一工具。

基础语法：WITH 子句的结构与用途

语法结构：给查询起一个“临时别名”

WITH 子句的核心语法如下：

WITH  
    temp1 AS (SELECT ...),  
    temp2 AS (SELECT ...),  
    temp3 AS (SELECT ...  
              FROM temp1  
              JOIN temp2 ...)  
SELECT *  
FROM temp3;

WITH：表示后续将定义一个或多个临时表（CTE）。
temp1 AS (...)：为子查询定义一个临时名称（如 temp1），后续可直接引用。
主查询：最终的 SELECT 语句，可引用所有定义的 CTE。

比喻：可以将 WITH 子句想象成“分步骤做菜”。例如，先切菜（temp1）、再炒菜（temp2），最后组合成一盘完整的菜（主查询）。每个步骤的名称（temp1）帮助我们清晰追踪每一步的作用。

单层 WITH：简化嵌套查询

场景：计算部门的平均销售额

假设我们有两个表：sales（销售记录）和 employees（员工信息）。需求是：

“查询每个部门的平均销售额，且只显示销售额高于公司平均的部门。”

传统写法：

SELECT department, AVG(amount) AS avg_sales  
FROM sales  
GROUP BY department  
HAVING AVG(amount) > (SELECT AVG(amount) FROM sales);

问题：子查询 (SELECT AVG(amount) FROM sales) 被重复计算，且嵌套结构降低了可读性。

WITH 子句优化：

WITH company_avg AS (  
    SELECT AVG(amount) AS avg_amount  
    FROM sales  
)  
SELECT department, AVG(amount) AS dept_avg  
FROM sales  
GROUP BY department  
HAVING AVG(amount) > (SELECT avg_amount FROM company_avg);

对比：

可读性提升：company_avg 清晰地表达了子查询的用途。
性能优化：PostgreSQL 可能复用 company_avg 的计算结果，避免重复执行子查询。

多层 WITH：分步解决复杂逻辑

场景：计算员工的奖金（基于多条件）

假设奖金规则如下：

员工销售额超过部门平均，获得 10% 的奖金；
若员工同时是部门经理，奖金再增加 5%。

WITH 子句分步实现：

WITH  
    -- 步骤1：计算每个部门的平均销售额  
    dept_avg AS (  
        SELECT department_id, AVG(amount) AS avg_sales  
        FROM sales  
        GROUP BY department_id  
    ),  
    -- 步骤2：筛选出销售额超过部门平均的员工  
    eligible_employees AS (  
        SELECT e.*, s.amount  
        FROM employees e  
        JOIN sales s ON e.id = s.employee_id  
        WHERE s.amount > (  
            SELECT avg_sales  
            FROM dept_avg da  
            WHERE da.department_id = e.department_id  
        )  
    )  
-- 最终计算奖金  
SELECT  
    name,  
    amount * 0.10 AS base_bonus,  
    CASE WHEN is_manager THEN amount * 0.05 ELSE 0 END AS manager_bonus,  
    amount * (0.10 + CASE WHEN is_manager THEN 0.05 ELSE 0 END) AS total_bonus  
FROM eligible_employees;

分步逻辑：

dept_avg 计算部门平均销售额；
eligible_employees 筛选出符合销售额条件的员工；
主查询结合员工是否为经理，计算最终奖金。

进阶用法：递归查询与性能优化

递归 WITH 子句：解决层级数据问题

场景：组织架构中的员工层级查询

假设 employees 表包含 id、name 和 manager_id（直属上级的ID）。需求是：

“查询某员工及其所有下属（包括间接下属）。”

递归 CTE 实现：

WITH RECURSIVE employee_tree AS (  
    -- 初始条件：根节点（目标员工）  
    SELECT id, name, manager_id  
    FROM employees  
    WHERE id = 101  -- 假设目标员工ID为101  
    UNION ALL  
    -- 递归条件：查找所有直属下属  
    SELECT e.id, e.name, e.manager_id  
    FROM employees e  
    INNER JOIN employee_tree et ON e.manager_id = et.id  
)  
SELECT * FROM employee_tree;

关键点：

RECURSIVE：声明这是一个递归 CTE。
初始条件：定义查询的起点（根节点）。
递归条件：通过 JOIN 自身，不断向下查找层级关系。

比喻：这就像在家族树中寻找所有后代，递归 CTE 会一层层“向下挖掘”，直到没有更多层级为止。

性能优化：避免重复计算与查询计划分析

案例：优化多条件统计查询

假设需要统计不同地区的订单数量、总金额及平均价格，且需排除未付款的订单：

低效写法：

SELECT  
    region,  
    COUNT(*) AS total_orders,  
    SUM(amount) AS total_amount,  
    AVG(amount) AS avg_price  
FROM orders  
WHERE payment_status = 'PAID'  
GROUP BY region;

优化后（WITH 子句）：

WITH filtered_orders AS (  
    SELECT *  
    FROM orders  
    WHERE payment_status = 'PAID'  
)  
SELECT  
    region,  
    COUNT(*) AS total_orders,  
    SUM(amount) AS total_amount,  
    AVG(amount) AS avg_price  
FROM filtered_orders  
GROUP BY region;

性能分析：

减少过滤次数：payment_status = 'PAID' 只执行一次，而非在多个聚合函数中重复判断。
查询计划：通过 EXPLAIN 可观察到，WITH 子句可能生成更高效的执行计划，例如先过滤再聚合。

实战案例：电商场景中的复杂报表

场景：生成用户行为分析报告

需求包括：

统计用户近7天的活跃天数；
计算每个用户的平均订单金额；
过滤出活跃天数≥3天且平均金额≥100的用户。

WITH 子句分步实现：

WITH  
    -- 步骤1：计算每个用户的活跃天数  
    active_days AS (  
        SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS days_active  
        FROM user_activity  
        WHERE created_at >= NOW() - INTERVAL '7 days'  
        GROUP BY user_id  
    ),  
    -- 步骤2：计算每个用户的平均订单金额  
    avg_order AS (  
        SELECT user_id, AVG(amount) AS avg_amount  
        FROM orders  
        GROUP BY user_id  
    ),  
    -- 步骤3：合并数据并筛选  
    final_users AS (  
        SELECT  
            a.user_id,  
            a.days_active,  
            o.avg_amount  
        FROM active_days a  
        JOIN avg_order o ON a.user_id = o.user_id  
    )  
-- 最终结果  
SELECT *  
FROM final_users  
WHERE days_active >= 3 AND avg_amount >= 100;

优势：

模块化设计：每个 CTE 负责一个独立功能，便于后续修改或新增条件。
代码复用：active_days 和 avg_order 可复用到其他报表中。

常见问题与最佳实践

1. WITH 子句与子查询的区别

子查询：嵌套在 SELECT、FROM 或 WHERE 子句中，可读性低；
WITH 子句：将逻辑分解为多个步骤，提升可读性并支持跨步骤复用。

类比：子查询像“即用即弃的便签纸”，而 WITH 子句是“分步骤的笔记本”。

2. 递归查询的终止条件

递归 CTE 必须确保终止条件，否则会导致无限循环。例如，在层级查询中：

-- 错误示例：缺少终止条件  
WITH RECURSIVE tree AS (  
    SELECT * FROM nodes WHERE parent_id = 1  
    UNION ALL  
    SELECT n.* FROM nodes n JOIN tree ON n.parent_id = tree.id  
)  
SELECT * FROM tree;

修正：

-- 添加过滤条件，例如排除根节点重复查询  
WITH RECURSIVE tree AS (  
    SELECT * FROM nodes WHERE id = 1 -- 根节点  
    UNION ALL  
    SELECT n.*  
    FROM nodes n  
    JOIN tree ON n.parent_id = tree.id  
    WHERE n.id != tree.id -- 避免循环引用  
)  
SELECT * FROM tree;

3. 性能调优技巧

合理拆分步骤：避免在单个 CTE 中执行过多操作，可能导致查询计划不优化。
使用物化（MATERIALIZED）：在递归查询中，可指定 MATERIALIZED 关键字强制物化临时结果，减少重复计算。
结合索引：确保 CTE 中使用的字段有适当索引，例如 JOIN 和 WHERE 条件的列。

结论：WITH 子句的价值与适用场景

PostgreSQL WITH 子句通过将复杂查询分解为逻辑步骤，显著提升了代码的可维护性和性能。无论是简化嵌套查询、处理递归数据，还是构建多步骤分析，它都是开发者的得力工具。

适用场景总结：

需要多次引用同一子查询时；
查询逻辑复杂，需分步骤拆解；
处理层级或树状结构数据；
在团队协作中提升代码可读性。

掌握 WITH 子句不仅能优化现有代码，更能为应对更复杂的数据库挑战打下坚实基础。下次遇到“查询太长看不懂”的情况，不妨试试用 WITH 子句为逻辑“分步骤编号”吧！

PostgreSQL WITH 子句（一文讲透）

前言：揭开 WITH 子句的神秘面纱

基础语法：WITH 子句的结构与用途

语法结构：给查询起一个“临时别名”

单层 WITH：简化嵌套查询

场景：计算部门的平均销售额

多层 WITH：分步解决复杂逻辑

场景：计算员工的奖金（基于多条件）

进阶用法：递归查询与性能优化

递归 WITH 子句：解决层级数据问题

场景：组织架构中的员工层级查询

性能优化：避免重复计算与查询计划分析

案例：优化多条件统计查询

实战案例：电商场景中的复杂报表

场景：生成用户行为分析报告

常见问题与最佳实践

1. WITH 子句与子查询的区别

2. 递归查询的终止条件

3. 性能调优技巧

结论：WITH 子句的价值与适用场景

最新发布

DataGrip 2025.1 最新激活码，破解版安装教程（至2099年~）

Webstorm 2025.1 最新激活码，破解版安装教程（至2099年~）

Pycharm 2025.1 最新激活码，破解版安装教程（至2099年~）

IDEA 2025.1 最新激活码，破解版安装教程（至2099年~）

Cursor @ 符号（千字长文）

POP3 协议（一文讲透）

IMAP 协议（保姆级教程）

RDP 协议（建议收藏）

SFTP 协议（保姆级教程）

UDP 协议（长文解析）