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 檔 ），數據內容如下：

zaixiandb# 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 表的數據如下：

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


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