PostgreSQL WITH 子句

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

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

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

WITH 子句在使用前必須先定義。

語法

WITH 查詢的基礎(chǔ)語法如下：

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 可以與現(xiàn)有的表名相同，并且具有優(yōu)先級。

可以在 WITH 中使用數(shù)據(jù) INSERT, UPDATE 或 DELETE 語句，允許您在同一個查詢中執(zhí)行多個不同的操作。

WITH 遞歸

在 WITH 子句中可以使用自身輸出的數(shù)據(jù)。

公用表表達式 (CTE) 具有一個重要的優(yōu)點，那就是能夠引用其自身，從而創(chuàng)建遞歸 CTE。遞歸 CTE 是一個重復(fù)執(zhí)行初始 CTE 以返回數(shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達式。

實例

創(chuàng)建 COMPANY 表 ，數(shù)據(jù)內(nèi)容如下：

w3cschooldb# 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 子句在上表中查詢數(shù)據(jù)：

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

得到結(jié)果如下：

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 關(guān)鍵字和 WITH 子句編寫一個查詢，查找 SALARY(工資) 字段小于 20000 的數(shù)據(jù)并計算它們的和：

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

得到結(jié)果如下：

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

下面我們建立一張和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 語句和 WITH 子句刪除 COMPANY 表中 SALARY(工資) 字段大于等于 30000 的數(shù)據(jù)，并將刪除的數(shù)據(jù)插入 COMPANY1 表，實現(xiàn)將 COMPANY 表數(shù)據(jù)轉(zhuǎn)移到 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

得到結(jié)果如下：

INSERT 0 3

此時，CAMPANY 表和 CAMPANY1 表的數(shù)據(jù)如下：

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


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