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]

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

你可以在 WITH 中使用数据修改语句(INSERT、UPDATE 或 DELETE)。这允许你在同一个查询中执行多个不同的操作。

递归 WITH

---

递归 WITH 或分层查询是 CTE 的一种形式，其中 CTE 可以引用自身，即 WITH 查询可以引用其自己的输出，因此名称递归。

考虑表 COMPANY 有记录如下：

testdb# 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;

上面给出的 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 COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

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

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

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

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

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

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

INSERT 0 3

现在，表 COMPANY 和 COMPANY1 中的记录如下：

testdb=# 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)


testdb=# 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)