PostgreSQL WITH 子句

在 PostgreSQL 中,WITH 子句提供了一種編寫輔助語句的方法,以便在更大的查詢中使用。

WITH 子句有助于將復雜的大型查詢分解為更簡單的表單,便于閱讀。這些語句通常稱為通用表表達式(Common Table Express, CTE),也可以當做一個為查詢而存在的臨時表。

WITH 子句是在多次執行子查詢時特別有用,允許我們在查詢中通過它的名稱(可能是多次)引用它。

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 子句中可以使用自身輸出的數據。

公用表表達式 (CTE) 具有一個重要的優點,那就是能夠引用其自身,從而創建遞歸 CTE。遞歸 CTE 是一個重復執行初始 CTE 以返回數據子集直到獲取完整結果集的公用表表達式。

實例

創建 COMPANY 表(下載 COMPANY SQL 文件 ),數據內容如下:

runoobdb# 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;

得到結果如下:

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 子句編寫一個查詢,查找 SALARY(工資) 字段小于 20000 的數據并計算它們的和:

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

得到結果如下:

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

下面我們建立一張和 COMPANY 表相似的 COMPANY1 表,使用 DELETE 語句和 WITH 子句刪除 COMPANY 表中 SALARY(工資) 字段大于等于 30000 的數據,并將刪除的數據插入 COMPANY1 表,實現將 COMPANY 表數據轉移到 COMPANY1 表中:

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

得到結果如下:

INSERT 0 3

此時,CAMPANY 表和 CAMPANY1 表的數據如下:

runoobdb=# 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)


runoobdb=# 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)