PostgreSQL WITH 子句

PostgreSQL WITH 子句 在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。 WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。 WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。 WITH 子句在使用前必须先定义。 语法 WITH 查询的基础语法如下： WITH name_for_summary_data AS ( SELECT Stat 在 PostgreSQL 数据库系统中，`WITH` 子句是一个强大的工具，它允许用户定义临时的、只在当前查询中有效的中间结果集，也就是通用表表达式（Common Table Expression，简称 CTE）。CTE 可以使得复杂的查询变得更加清晰，便于理解和维护。在 SQL 查询中，我们可以将一个大查询分解为多个小的、可重用的部分，每个部分都可以用一个 CTE 来表示。 基础语法： `WITH` 子句的基本形式如下： ```sql WITH name_for_summary_data AS ( SELECT column1, column2, ... FROM table_name WHERE condition ) SELECT columns FROM name_for_summary_data WHERE conditions [ORDER BY columns] ``` 这里，`name_for_summary_data` 是 CTE 的名字，可以自由命名，用于后续的查询中引用这个临时结果集。`SELECT` 语句定义了 CTE 中包含的数据，可以是任何合法的 SQL 查询。 **递归公用表表达式 (Recursive CTE)**： 递归 CTE 是 `WITH` 子句的一个扩展，它允许 CTE 引用自身，形成一个递归过程。这在处理层次结构数据或者需要自连接的场景中非常有用。例如，假设我们有一个员工组织结构，每个员工可能有下属，递归 CTE 可以用来遍历整个结构。 以下是一个简单的递归 CTE 示例，用于找到 `SALARY` 小于 20000 的所有员工的工资总和： ```sql WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT salary FROM company WHERE salary < 20000 ) SELECT SUM(n) FROM t; ``` 这个例子首先创建了一个名为 `t` 的 CTE，初始值为 0。然后，递归部分会持续从 `company` 表中选择 `salary` 小于 20000 的记录，直到没有符合条件的记录为止。使用 `SUM` 函数计算所有符合条件的员工的工资总和。 **在 `WITH` 子句中进行 DML 操作**： 除了 `SELECT`，`WITH` 子句还可以与 `INSERT`, `UPDATE` 和 `DELETE` 语句结合，使你在单个查询中执行多个数据库操作。例如，以下代码将删除 `COMPANY` 表中 `SALARY` 大于等于 30000 的记录，并将这些记录插入到新的 `COMPANY1` 表中： ```sql WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * ) INSERT INTO COMPANY1 SELECT * FROM moved_rows; ``` 这里，`moved_rows` CTE 保存了被删除的记录，然后这些记录被插入到 `COMPANY1` 表中。`RETURNING` 子句用于在 `DELETE` 语句完成后返回删除的行，以便于在 `INSERT` 语句中使用。 总结起来，`WITH` 子句是 PostgreSQL 提供的一种高级查询构造，它允许我们将复杂查询分解成易于管理的组件，同时支持递归操作和 DML 操作的组合，大大提高了 SQL 查询的灵活性和可读性。在处理大量数据和复杂逻辑时，熟练使用 `WITH` 子句能显著提升数据库管理和数据分析的效率。