PostgreSQL WITH 子句

天天向上

发布： 2025-03-11 23:52:46

原创

759 人浏览过

在 PostgreSQL 中，WITH 子句（也称为公用表表达式，简称 CTE）用于定义临时结果集，这些结果集可以在 SELECT、INSERT、UPDATE 或 DELETE 查询中重复使用。使用 WITH 子句可以使复杂查询更易读、易于调试，并提高查询的组织性和可维护性。

1. `WITH` 子句的基本语法

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE another_condition;

cte_name: 公用表表达式（CTE）的名称。
SELECT column1, column2, ...: 在 CTE 中定义的查询，可以是任何有效的 SELECT 查询。
FROM cte_name: 在主查询中引用 CTE 的名称。
WHERE: 主查询的过滤条件。

2. `WITH` 子句的基本示例

假设你有一个名为 employees 的表，包含 employee_id、name 和 salary 等字段。以下是一个使用 WITH 子句的简单示例：

WITH high_salary AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT name
FROM high_salary;

在这个例子中，WITH 子句首先定义了一个临时的 CTE high_salary，它包含所有薪水大于 50000 的员工。然后，主查询从这个 CTE 中选取所有员工的名字。

3. 使用多个 CTE

你可以在一个 WITH 子句中定义多个 CTE，每个 CTE 用逗号分隔。每个 CTE 都可以被后续查询引用：

WITH
high_salary AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 50000
),
low_salary AS (
    SELECT name, salary
    FROM employees
    WHERE salary <= 50000
)
SELECT * FROM high_salary
UNION ALL
SELECT * FROM low_salary;

这个例子中，我们定义了两个 CTE，一个用于存储高薪员工的数据，另一个用于存储低薪员工的数据。然后，主查询将这两个 CTE 中的数据合并起来。

4. 递归 CTE

PostgreSQL 还支持递归 CTE，允许你定义递归查询。这对于处理层次结构数据（如组织结构、目录结构等）非常有用。递归 CTE 包括两个部分：基础查询和递归查询。

4.1 递归 CTE 的基本语法

WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT column1, column2
    FROM table_name
    WHERE condition
    UNION ALL
    -- 递归查询
    SELECT t.column1, t.column2
    FROM table_name t
    JOIN cte_name cte ON t.column1 = cte.column2
)
SELECT * FROM cte_name;

WITH RECURSIVE: 指定递归 CTE。
基础查询: 定义递归的起始条件。
递归查询: 定义递归的逻辑，通常会通过 UNION ALL 来连接递归查询和基础查询。

4.2 递归 CTE 示例

假设你有一个 employees 表，表示员工和他们的经理。每个员工有一个 employee_id 和 manager_id，manager_id 是他们上级的 employee_id。我们可以使用递归 CTE 查找某个员工及其所有上级经理：

WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询：查找特定员工
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE employee_id = 101
    UNION ALL
    -- 递归查询：查找该员工的经理
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT * FROM employee_hierarchy;

在这个例子中，WITH RECURSIVE 用于定义递归查询，首先选择员工 101 的数据（基础查询），然后递归查找该员工的所有上级经理。查询最终返回该员工及其所有上级经理的列表。

5. 使用 `WITH` 子句提高查询可读性

在处理复杂的查询时，使用 WITH 子句可以显著提高代码的可读性和可维护性。例如，假设你有一个复杂的查询，涉及多次对同一子查询的引用，使用 WITH 子句可以避免重复代码。

5.1 避免重复查询

WITH total_sales AS (
    SELECT product_id, SUM(amount) AS total
    FROM sales
    GROUP BY product_id
)
SELECT p.product_name, ts.total
FROM products p
JOIN total_sales ts ON p.product_id = ts.product_id;

在这个例子中，WITH 子句定义了一个名为 total_sales 的临时结果集，它计算了每个产品的销售总额。主查询使用这个临时结果集，以避免在查询中重复编写计算总销售额的逻辑。

6. `WITH` 子句与事务

WITH 子句定义的 CTE 只在当前查询的生命周期内有效，不能跨查询或事务共享。如果你在事务中使用 WITH 子句，它的作用范围仅限于事务内执行的单个查询。

7. 性能考虑

WITH 子句可以使查询更简洁、更容易理解，但要小心在复杂查询中使用它，因为在某些情况下它可能会导致性能问题，尤其是当 CTE 被多次引用时，查询引擎可能会多次执行它。
对于简单的查询，使用 CTE 是一个很好的选择，但如果查询非常复杂且涉及大量数据，考虑将 CTE 转化为临时表或优化查询。

8. 总结

WITH 子句用于定义临时结果集，这些结果集可以在查询中重复使用。
它可以帮助你简化复杂查询、提高代码的可读性和可维护性。
递归 CTE 是 PostgreSQL 特有的功能，适用于处理层次结构数据。
使用 WITH 子句时要注意查询的性能，尤其是在查询很复杂或者涉及大量数据时。

掌握 WITH 子句，能够让你在 PostgreSQL 中处理更复杂的查询，尤其是当涉及多个子查询和重复使用查询结果时。更多详细内容请关注其他相关文章！

1. WITH 子句的基本语法

2. WITH 子句的基本示例