Cláusula WITH PostgreSQL

No PostgreSQL, a cláusula WITH oferece um método para escrever sentenças auxiliares, facilitando o uso em consultas maiores.

A cláusula WITH ajuda a decompor consultas complexas e grandes em formas mais simples, facilitando a leitura. Essas sentenças são geralmente chamadas de Expressões de Tabela Comuns (Common Table Expressions, CTE) e também podem ser usadas como uma tabela temporária para consultas.

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　|　South-Hall|　　45000
　　7　|　James　|　　24　| Houston |　　10000
(7　rows)

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

With CTE AS
(Select
　ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select　*　From CTE;

Os resultados obtidos são os seguintes:

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　|　South-Hall|　　45000
　　7　|　James　|　　24　| Houston |　　10000
(7　rows)

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

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

Os resultados obtidos são os seguintes:

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

A seguir, criamos uma tabela semelhante à tabela COMPANY chamada COMPANY1 A tabela, usando a sentença DELETE e a cláusula WITH para deletar os dados da tabela COMPANY SALARY(salário) O campo maior ou igual a 30000, e inserir os dados deletados na tabela COMPANY1 A tabela, para implementar a transferência de dados da tabela COMPANY para COMPANY1 Na tabela:

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);

Os resultados obtidos são os seguintes:

INSERT　0　3

Neste momento, a tabela CAMPANY e CAMPANY1 Os dados da tabela são os seguintes:

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　|　South-Hall　　|　　45000
(3　rows)