PostgreSQL WITH 子句用法详解

在 PostgreSQL 里，WITH 子句也被叫做公共表表达式（CTE），它能够把复杂查询拆解成更易管理的部分。WITH 子句定义了一个或多个临时结果集，这些结果集可以在后续的主查询里引用。以下是关于 WITH 子句用法的详细介绍：

基本语法

WITH cte_name AS (
    -- 子查询
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- 主查询，引用 CTE
SELECT *
FROM cte_name;

解释

• WITH：用于开启 WITH 子句。
• cte_name：这是公共表表达式的名称，主查询会使用这个名称引用临时结果集。
• AS：用于分隔 CTE 名称和子查询。
• 子查询：是一个完整的 SELECT 语句，用来生成临时结果集。
• 主查询：可以像使用普通表那样使用 CTE。

简单示例

假设存在一个 employees 表，包含 id、name 和 salary 列。现在要找出薪水高于平均薪水的员工。

 

-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO employees (name, salary)
VALUES ('Alice', 5000.00),
       ('Bob', 6000.00),
       ('Charlie', 4500.00),
       ('David', 7000.00);

-- 使用 WITH 子句
WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT e.name, e.salary
FROM employees e
JOIN average_salary a ON e.salary > a.avg_salary;

解释

• average_salary 这个 CTE 计算了所有员工的平均薪水。
• 主查询把 employees 表和 average_salary CTE 进行连接，找出薪水高于平均薪水的员工。

多个 CTE 的使用

WITH 子句可以定义多个 CTE，并且这些 CTE 之间可以相互引用。

 

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 5000
),
average_high_salary AS (
    SELECT AVG(salary) AS avg_high_salary
    FROM high_salary_employees
)
SELECT hse.name, hse.salary
FROM high_salary_employees hse
JOIN average_high_salary ahs ON hse.salary > ahs.avg_high_salary;

解释

• high_salary_employees CTE 找出薪水高于 5000 的员工。
• average_high_salary CTE 计算这些高薪员工的平均薪水。
• 主查询找出薪水高于高薪员工平均薪水的员工。

递归 CTE

递归 CTE 可用于处理分层数据，像组织结构图、文件系统目录结构等。

 

-- 创建示例表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT REFERENCES departments(id)
);

-- 插入示例数据
INSERT INTO departments (name, parent_id)
VALUES ('Company', NULL),
       ('Sales', 1),
       ('Marketing', 1),
       ('East Sales', 2),
       ('West Sales', 2);

-- 递归 CTE
WITH RECURSIVE department_hierarchy AS (
    -- 初始查询
    SELECT id, name, parent_id, 0 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询
    SELECT d.id, d.name, d.parent_id, dh.level + 1
    FROM departments d
    JOIN department_hierarchy dh ON d.parent_id = dh.id
)
SELECT name, level
FROM department_hierarchy
ORDER BY level, name;

解释

• 递归 CTE 由两部分构成：初始查询和递归查询。
• 初始查询找出顶层部门（parent_id 为 NULL）。
• 递归查询把 departments 表和 department_hierarchy CTE 连接起来，找出每个部门的子部门。
• 最终查询显示所有部门及其层级。

注意事项

• CTE 仅在当前查询中有效，不会在数据库里永久保存。
• 递归 CTE 必须包含 UNION ALL 或 UNION，而且要明确终止条件，防止无限递归。
• 在复杂查询里使用 CTE 可以提高可读性和可维护性。