📚 目录

  1. 概述
  2. 基本语法
  3. 使用 WITH 子句定义子查询
  4. 递归查询
  5. 多个公用表表达式（CTE）
  6. 结合 SELECT 使用
  7. 参考资料

🛠️ 概述

WITH 子句，也被称为公用表表达式（CTE），是 PostgreSQL 中一种非常强大的功能。它允许你定义一个临时的结果集，并可以在查询中多次引用。WITH 子句使得查询更加清晰、简洁，有助于组织复杂的查询逻辑。

📝 基本语法

WITH 子句的基本语法如下：

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;
  • cte_name：你为临时结果集指定的名称。
  • 子查询：WITH 后面的子查询部分定义了临时结果集。
  • 主查询：通过引用 cte_name，主查询可以使用该结果集。

🔢 使用 WITH 子句定义子查询

WITH 子句通常用于定义一个临时结果集，并在主查询中多次引用它，避免重复书写复杂的子查询。

示例（计算每个部门的员工总数，然后按总数排序）：

WITH department_counts AS (
    SELECT department, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)
SELECT department, employee_count
FROM department_counts
ORDER BY employee_count DESC;

该查询首先定义了一个 department_counts CTE，它计算了每个部门的员工数量。然后，主查询引用了这个临时结果集，并按员工数量降序排序。

🔁 递归查询

WITH 子句可以用于递归查询，解决一些复杂的层级查询问题，比如查找树形结构中的所有层级关系。递归查询由两个部分组成：基础部分和递归部分。

示例（递归查询员工和其直属上级）：

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

此查询首先选择没有上级的员工，然后递归查找所有下属员工，并最终生成整个员工层级结构。

🔢 多个公用表表达式（CTE）

你可以在一个查询中使用多个 WITH 子句，定义多个 CTE，每个 CTE 都可以包含一个子查询，并且可以在主查询中引用它们。

示例（使用多个 CTE）：

WITH department_counts AS (
    SELECT department, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
),
department_salaries AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
)
SELECT d.department, dc.employee_count, ds.total_salary
FROM department_counts dc
JOIN department_salaries ds ON dc.department = ds.department;

该查询定义了两个 CTE：department_counts 用于计算每个部门的员工数，department_salaries 用于计算每个部门的薪资总和。主查询将这两个 CTE 连接起来，返回每个部门的员工数量和总薪资。

🧑‍💻 结合 SELECT 使用

WITH 子句可以与其他 SQL 语句如 SELECT、INSERT、UPDATE 和 DELETE 一起使用，通常用来简化复杂的查询或数据处理逻辑。

示例（结合 SELECT 和 WITH 使用）：

WITH department_avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary
FROM employees e
JOIN department_avg_salary das ON e.department = das.department
WHERE e.salary > das.avg_salary;

该查询返回薪资高于各自部门平均薪资的员工。

📘 参考资料