PostgreSQL WITH 子句（CTE）详解

🌺The Begin🌺点点关注，收藏不迷路🌺

一、WITH 子句概述

PostgreSQL 的 WITH 子句（又称公用表表达式，Common Table Expression，CTE）是编写复杂SQL查询的强大工具。它允许创建临时命名结果集，可以在主查询中多次引用，显著提高复杂查询的可读性和可维护性。

基本语法结构

WITH cte_name AS (
    SELECT ...  -- CTE查询定义
)
SELECT ... FROM cte_name;  -- 主查询

二、CTE 执行原理

三、CTE 架构图

四、基础CTE应用

1. 简单CTE示例

-- 创建临时结果集
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
-- 使用CTE
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 100000;

2. 多CTE定义

WITH 
    dept_stats AS (
        SELECT department, COUNT(*) AS emp_count
        FROM employees
        GROUP BY department
    ),
    high_salary AS (
        SELECT department, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department
        HAVING AVG(salary) > 10000
    )
SELECT d.department, d.emp_count, h.avg_salary
FROM dept_stats d
JOIN high_salary h ON d.department = h.department;

五、递归CTE详解

1. 递归CTE结构

WITH RECURSIVE cte_name AS (
    -- 基础查询（非递归部分）
    SELECT ... FROM ...
    UNION [ALL]
    -- 递归查询（引用CTE自身）
    SELECT ... FROM ... JOIN cte_name ...
)
SELECT * FROM cte_name;

2. 递归CTE执行流程

3. 递归CTE应用实例

层级数据查询（树形结构）

-- 组织结构查询
WITH RECURSIVE org_hierarchy AS (
    -- 基础查询：查找根节点
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：连接子节点
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy
ORDER BY level, id;

数字序列生成

-- 生成1-10的数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

六、数据修改CTE

1. 数据移动模式

-- 将数据从表A移动到表B
WITH moved_rows AS (
    DELETE FROM table_a
    WHERE condition
    RETURNING *
)
INSERT INTO table_b
SELECT * FROM moved_rows;

2. 级联更新示例

WITH updated_products AS (
    UPDATE products
    SET price = price * 1.1
    WHERE category = 'Electronics'
    RETURNING id, price
)
SELECT 'Updated ' || COUNT(*) || ' products' AS result
FROM updated_products;

七、性能优化策略

1. CTE优化建议

优化点	建议	说明
物化策略	评估MATERIALIZED选项	PostgreSQL 12+支持
递归深度	设置合理限制	避免无限递归
索引利用	确保CTE查询使用索引	特别是递归CTE
结果集大小	限制中间结果	避免内存溢出

2. 执行计划分析

EXPLAIN ANALYZE
WITH regional_stats AS (
    SELECT region, AVG(sales) AS avg_sales
    FROM orders
    GROUP BY region
)
SELECT * FROM regional_stats
WHERE avg_sales > 1000;

八、最佳实践指南

1. 命名清晰：使用有意义的CTE名称
2. 适度分解：不要过度使用CTE导致复杂化
3. 递归控制：确保递归CTE有终止条件
4. 性能测试：比较CTE与子查询性能
5. 注释说明：为复杂CTE添加注释

九、与其他数据库对比

特性	PostgreSQL	MySQL (8.0+)	SQL Server	Oracle
基本CTE	支持	支持	支持	支持
递归CTE	支持	支持	支持	支持
数据修改	支持	有限支持	支持	支持
物化控制	MATERIALIZED选项	自动优化	提示控制	提示控制

十、总结

PostgreSQL 的 WITH 子句是处理复杂查询的利器，关键优势包括：

1. 提高可读性：将复杂查询分解为逻辑模块
2. 代码重用：避免重复子查询
3. 递归能力：轻松处理层级数据
4. 数据修改：在单一语句中实现复杂操作
5. 优化潜力：为查询优化器提供更多信息

通过合理应用CTE，可以显著提升复杂SQL查询的编写效率和执行性能，特别是在处理递归数据关系和分步数据操作时表现出色。

🌺The End🌺点点关注，收藏不迷路🌺