Postgresql 简明教程

PostgreSQL - WITH Clause

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

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

WITH 子句必须在查询中使用之前定义。

Syntax

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）。这允许你在同一查询中执行多个不同的操作。

Recursive WITH

Example

考虑 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)

现在，让我们编写一个查询，使用 WITH 子句和 RECURSIVE 关键字来查找小于 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)