Cláusula WITH de PostgreSQL

En PostgreSQL, la cláusula WITH proporciona un método para escribir sentencias auxiliares para su uso en consultas más grandes.

La cláusula WITH ayuda a descomponer consultas complejas y grandes en formas más simples, lo que facilita su lectura. Estas sentencias se conocen comúnmente como Expresiones Tabulares Comunes (Common Table Expressions, CTE) y también pueden actuar como una tabla temporal existente para la consulta.

WITH 子句在多次执行子查询时特别有用，允许我们在查询中通过它的名称（可能是多次）引用它。

WITH 子句在使用前必须先定义。

语法

WITH 查询的基础语法如下：

WITH
　　　name_for_summary_data AS (
　　　　　　SELECT Statement)
　　　SELECT columns
　　　FROM name_for_summary_data
　　　WHERE conditions <=> (
　　　　　　SELECT column
　　　　　　FROM name_for_summary_data)
　　　[ORDER BY columns]

name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

WITH 递归

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

在线示例

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

w3codeboxdb# select　*　from COMPANY;
　id | name | age | address | salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　| California|　　20000
　　2　|　Allen　|　　25　| Texas |　　15000
　　3　|　Teddy　|　　23　| Norway |　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond |　　65000
　　5　|　David　|　　27　| Texas |　　85000
　　6　|　Kim　　　|　　22　|　Sur-Hall|　　45000
　　7　|　James　|　　24　| Houston |　　10000
(7　rows)

下面将使用 WITH 子句在上表中查询数据：

With CTE AS
(Select
　ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
选择　*　从 CTE 中；

Se obtuvo el siguiente resultado：

id | name | age | address | salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　| California|　　20000
　　2　|　Allen　|　　25　| Texas |　　15000
　　3　|　Teddy　|　　23　| Norway |　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond |　　65000
　　5　|　David　|　　27　| Texas |　　85000
　　6　|　Kim　　　|　　22　|　Sur-Hall|　　45000
　　7　|　James　|　　24　| Houston |　　10000
(7　rows)

接下来让我们使用 RECURSIVE 通过关键字和 WITH 子句编写一个查询，以查找 SALARY(salario) 字段小于 2计算 0000 的数据并求和：

WITH RECURSIVE t(n) AS (
　　　VALUES (0)
　　　UNION　ALL
　　　SELECT　SALARY　FROM　COMPANY　WHERE　SALARY　<　20000
)
SELECT　sum(n)　FROM　t;

Se obtuvo el siguiente resultado：

　sum
-------
　25000
(1　row)

A continuación, creamos una tabla COMPANY similar a la tabla COMPANY1 La tabla, utilizando la sentencia DELETE y la cláusula WITH para eliminar los datos de la tabla SALARY(salario) El campo es mayor o igual que 30000 de los datos, y luego inserta los datos eliminados en COMPANY1 La tabla, que realiza la transferencia de datos de la tabla COMPANY a COMPANY1 En la tabla：

CREATE　TABLE　COMPANY1(
　　　ID　INT　PRIMARY　KEY　　　　　NOT　NULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOT　NULL,
　　　AGE　　　　　　　　　　　　INT　　　　　NOT　NULL,
　　　ADDRESS　　　　　　　　CHAR(50),
　　　SALARY　　　　　　　　　REAL
);
WITH　moved_rows　AS　(
　　　DELETE　FROM　COMPANY
　　　WHERE
　　　　　　SALARY　>=　30000
　　　RETURNING　*
)
INSERT　INTO　COMPANY1　(SELECT　*　FROM　moved_rows);

Se obtuvo el siguiente resultado：

INSERT　0　3

En este momento, la tabla CAMPANY y CAMPANY1 Los datos de la tabla son los siguientes：

w3codeboxdb=#　SELECT　*　FROM　COMPANY;
　id　|　name　　|　age　|　　address　　　|　salary
----+-------+-----+------------+--------
　　1　|　Paul　　|　　32　|　California　|　　20000
　　2　|　Allen　|　　25　|　Texas　　　　　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　　　　|　　20000
　　7　|　James　|　　24　|　Houston　　　　|　　10000
(4　rows)
w3codeboxdb=#　SELECT　*　FROM　COMPANY1;
　id　|　name　　|　age　|　address　|　salary
----+-------+-----+-------------+--------
　　4　|　Mark　　|　　25　|　Rich-Mond　　　|　　65000
　　5　|　David　|　　27　|　Texas　　　　　　　|　　85000
　　6　|　Kim　　　|　　22　|　Sur-Hall　　|　　45000
(3　rows)