PostgreSQL - WITH 子句

在 PostgreSQL 中，WITH 查询提供了一种编写辅助语句以用于更大查询的方法。它有助于将复杂和大型查询分解为更简单的形式，这些形式易于阅读。这些语句通常被称为公共表表达式或 CTE，可以被认为是定义了只为一个查询而存在的临时表。

WITH 查询是 CTE 查询，在多次执行子查询时特别有用。它同样有助于代替临时表。它计算一次聚合，并允许我们在查询中通过其名称（可能多次）引用它。

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 可以引用自身，即 WITH 查询可以引用其自己的输出，因此名称递归。

例子

参考表pgccc_COMPANY 的记录如下

testdb# select * from pgccc_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 pgccc_COMPANY )
Select * From CTE;

上面给出的 PostgreSQL 语句将产生以下结果

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 子句编写一个查询，以查找小于 20000 的工资总和，如下所示

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

上面给出的 PostgreSQL 语句将产生以下结果

sum
‐‐‐‐‐‐‐
25000
(1 row)

让我们使用数据修改语句和 WITH 子句编写一个查询，如下所示。

首先，创建一个类似于表 pgccc_COMPANY 的表 pgccc_COMPANY1。示例中的查询有效地将行从 pgccc_COMPANY 移动到 pgccc_COMPANY1。WITH 中的 DELETE 从pgccc_COMPANY 中删除指定的行，通过其 RETURNING 子句返回它们的内容；然后主查询读取该输出并将其插入 pgccc_COMPANY1 TABLE

CREATE TABLE pgccc_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 pgccc_COMPANY
WHERE
SALARY >= 30000
RETURNING *
)INSERT INTO pgccc_COMPANY1 (SELECT * FROM moved_rows);

上面给出的 PostgreSQL 语句将产生以下结果

INSERT 0 3

现在，表 pgccc_COMPANY 和 pgccc_COMPANY1 中的记录如下

testdb=# SELECT * FROM pgccc_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)

LNXDB=# SELECT * FROM pgccc_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)