PostgreSQL - WITH 子句

PostgreSQL - WITH 子句（公共表表达式）

在 PostgreSQL 中，WITH 子句，也称为公共表表达式（Common Table Expression, CTE），允许你定义一个临时、命名的结果集，该结果集仅在单个查询的持续时间内存在。CTE 是一个极其强大的工具，用于将大型复杂查询分解为逻辑清晰、可读性强且易于维护的步骤。

可以将 CTE 视为创建一个临时视图或表，你可以在主 SELECT、INSERT、UPDATE 或 DELETE 语句中多次引用它。这对于避免重复的子查询以及构建递归查询特别有用。

语法

WITH 子句的基本语法如下：

WITH cte_name (column1, column2, ...) AS (
    -- 定义 CTE 的查询
    SELECT ...
    FROM ...
)
-- 使用 CTE 的主查询
SELECT ...
FROM cte_name
WHERE ...;

你还可以在单个 WITH 子句中定义多个 CTE，它们之间用逗号分隔。每个后续的 CTE 都可以引用在其之前定义的 CTE。

简单的 CTE 示例

假设有一个 employees 表。让我们找出所有薪资高于“工程”部门平均薪资的员工。

-- 员工表示例数据：
-- id | name    | department    | salary
-- ---+---------+---------------+--------
-- 1  | Alice   | Engineering   | 90000
-- 2  | Bob     | Engineering   | 80000
-- 3  | Charlie | HR            | 60000
-- 4  | David   | Engineering   | 120000

WITH engineering_avg_salary AS (
    SELECT AVG(salary) as avg_sal
    FROM employees
    WHERE department = 'Engineering'
)
SELECT e.name, e.salary
FROM employees e, engineering_avg_salary eas
WHERE e.department = 'Engineering' AND e.salary > eas.avg_sal;

此查询首先在一个名为 engineering_avg_salary 的 CTE 中计算工程部门的平均薪资。然后，主查询将 employees 表与此 CTE 连接，以过滤出所需的员工。这比在 WHERE 子句中使用子查询要清晰得多。

递归 CTE

CTE 的一个强大特性是能够使用 WITH RECURSIVE 语法使其具有递归性。这非常适合查询分层数据，例如组织结构图或嵌套类别结构。

示例：查找特定员工的管理链。假设我们的 employees 表包含 id、name 和 manager_id 列。

WITH RECURSIVE management_chain AS (
    -- 非递归部分（起始点）
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE name = 'Bob'

    UNION ALL

    -- 递归部分（与自身连接）
    SELECT e.id, e.name, e.manager_id, mc.level + 1
    FROM employees e
    JOIN management_chain mc ON e.id = mc.manager_id
)
SELECT name, level
FROM management_chain
ORDER BY level DESC;

此查询从员工“Bob”（非递归项）开始，然后重复连接 employees 表，沿着层级向上查找每个经理，直到到达顶层（manager_id 为 NULL 的地方）。

CTE 中的数据修改语句

你可以在 WITH 子句内部使用 INSERT、UPDATE 或 DELETE。结合 RETURNING 子句，这允许你执行一个操作，并立即在查询的另一部分中使用受影响的行。

示例：通过在一个语句中将旧订单从 orders 表移动到 archived_orders 表来归档它们。

WITH moved_rows AS (
    DELETE FROM orders
    WHERE order_date < '2023-01-01'
    RETURNING *
)
INSERT INTO archived_orders
SELECT * FROM moved_rows;

这会原子性地删除旧订单并将它们插入到存档表中。RETURNING * 子句使被删除的行可供 moved_rows CTE 使用，然后该 CTE 被用作 INSERT 语句的源。