Postgresql 简明教程

PostgreSQL - WITH Clause

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

In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

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

The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables. It computes the aggregation once and allows us to reference it by its name (may be multiple times) in the queries.

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

The WITH clause must be defined before it is used in the query.

Syntax

WITH 查询的基本语法如下所示：

The basic syntax of WITH query is as follows −

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 可以和现有表名相同，并且具有优先权。

Where name_for_summary_data is the name given to the WITH clause. The name_for_summary_data can be the same as an existing table name and will take precedence.

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

You can use data-modifying statements (INSERT, UPDATE or DELETE) in WITH. This allows you to perform several different operations in the same query.

Recursive WITH

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

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 子句从上述表中选择记录，如下所示：

Now, let us write a query using the WITH clause to select the records from the above table, as follows −

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

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

The above given PostgreSQL statement will produce the following result −

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 的薪水总和，如下所示：

Now, let us write a query using the RECURSIVE keyword along with the WITH clause, to find the sum of the salaries less than 20000, as follows −

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

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

The above given PostgreSQL statement will produce the following result −

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

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

Let us write a query using data modifying statements along with the WITH clause, as shown below.

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

First, create a table COMPANY1 similar to the table COMPANY. The query in the example effectively moves rows from COMPANY to COMPANY1. The DELETE in WITH deletes the specified rows from COMPANY, returning their contents by means of its RETURNING clause; and then the primary query reads that output and inserts it into 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 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

INSERT 0 3

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

Now, the records in the tables COMPANY and COMPANY1 are as follows −

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)