PostgreSQL - WITH Clause

在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或Hierarchical查询，是CTE可以引用的CTE形式对于它自身，即一个WITH查询可以引用它自己的输出，因此名称是递归的.

示例

考虑表公司，记录如下 :

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. WITHETE中的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)