在 PostgreSQL 中,WITH 子句提供了一种编写辅助语句的方法,以便在更大的查询中使用。
WITH 子句有助于将复杂的大型查询分解为更简单的表单,便于阅读。这些语句通常称为通用表表达式(Common Table Express, CTE),也可以当做一个为查询而存在的临时表。
WITH 子句是在多次执行子查询时特别有用,允许我们在查询中通过它的名称(可能是多次)引用它。
WITH 子句在使用前必须先定义。

语法

WITH 查询的基础语法如下:

  1. WITH
  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 文件 ),数据内容如下:

  1. runoobdb# 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 子句在上表中查询数据:

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

得到结果如下:

  1. id | 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 的数据并计算它们的和:

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

得到结果如下:

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

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

  1. CREATE 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. WITH moved_rows AS (
  9. DELETE FROM COMPANY
  10. WHERE
  11. SALARY >= 30000
  12. RETURNING *
  13. )
  14. INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

得到结果如下:

  1. INSERT 0 3

此时,CAMPANY 表和 CAMPANY1 表的数据如下:

  1. runoobdb=# 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. runoobdb=# SELECT * FROM COMPANY1;
  10. id | name | age | address | salary
  11. ----+-------+-----+-------------+--------
  12. 4 | Mark | 25 | Rich-Mond | 65000
  13. 5 | David | 27 | Texas | 85000
  14. 6 | Kim | 22 | South-Hall | 45000
  15. (3 rows)