PostgreSQL WITH 子句

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。

WITH 子句在使用前必须先定义。

语法

WITH 查询的基础语法如下：

1WITH
2   name_for_summary_data AS (
3      SELECT Statement)
4   SELECT columns
5   FROM name_for_summary_data
6   WHERE conditions <=> (
7      SELECT column
8      FROM name_for_summary_data)
9   [ORDER BY columns]

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

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

WITH 递归

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

实例

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

1runoobdb# select * from COMPANY;
2 id | name  | age | address   | salary
3----+-------+-----+-----------+--------
4  1 | Paul  |  32 | California|  20000
5  2 | Allen |  25 | Texas     |  15000
6  3 | Teddy |  23 | Norway    |  20000
7  4 | Mark  |  25 | Rich-Mond |  65000
8  5 | David |  27 | Texas     |  85000
9  6 | Kim   |  22 | South-Hall|  45000
10  7 | James |  24 | Houston   |  10000
11(7 rows)

下面将使用 WITH 子句在上表中查询数据：

1With CTE AS
2(Select
3 ID
4, NAME
5, AGE
6, ADDRESS
7, SALARY
8FROM COMPANY )
9Select * From CTE;

得到结果如下：

1id | name  | age | address   | salary
2----+-------+-----+-----------+--------
3  1 | Paul  |  32 | California|  20000
4  2 | Allen |  25 | Texas     |  15000
5  3 | Teddy |  23 | Norway    |  20000
6  4 | Mark  |  25 | Rich-Mond |  65000
7  5 | David |  27 | Texas     |  85000
8  6 | Kim   |  22 | South-Hall|  45000
9  7 | James |  24 | Houston   |  10000
10(7 rows)

接下来让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和：

1WITH RECURSIVE t(n) AS (
2   VALUES (0)
3   UNION ALL
4   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
5)
6SELECT sum(n) FROM t;

得到结果如下：

1sum
2-------
3 25000
4(1 row)

下面我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中：

1CREATE TABLE COMPANY1(
2   ID INT PRIMARY KEY     NOT NULL,
3   NAME           TEXT    NOT NULL,
4   AGE            INT     NOT NULL,
5   ADDRESS        CHAR(50),
6   SALARY         REAL
7);
8
9
10WITH moved_rows AS (
11   DELETE FROM COMPANY
12   WHERE
13      SALARY >= 30000
14   RETURNING *
15)
16INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

得到结果如下：

1INSERT 0 3

此时，CAMPANY 表和 CAMPANY1 表的数据如下：

1runoobdb=# SELECT * FROM COMPANY;
2 id | name  | age |  address   | salary
3----+-------+-----+------------+--------
4  1 | Paul  |  32 | California |  20000
5  2 | Allen |  25 | Texas      |  15000
6  3 | Teddy |  23 | Norway     |  20000
7  7 | James |  24 | Houston    |  10000
8(4 rows)
9
10
11runoobdb=# SELECT * FROM COMPANY1;
12 id | name  | age | address | salary
13----+-------+-----+-------------+--------
14  4 | Mark  |  25 | Rich-Mond   |  65000
15  5 | David |  27 | Texas       |  85000
16  6 | Kim   |  22 | South-Hall  |  45000
17(3 rows)