PostgreSQL WITH 子句

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

WITH 子句有助于將復(fù)雜的大型查詢分解為更簡(jiǎn)單的表單，便于閱讀。這些語(yǔ)句通常稱(chēng)為通用表表達(dá)式（Common Table Express， CTE），也可以當(dāng)做一個(gè)為查詢而存在的臨時(shí)表。

WITH 子句是在多次執(zhí)行子查詢時(shí)特別有用，允許我們?cè)诓樵冎型ㄟ^(guò)它的名稱(chēng)(可能是多次)引用它。

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

語(yǔ)法

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

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 子句的名稱(chēng)，name_for_summary_data 可以與現(xiàn)有的表名相同，并且具有優(yōu)先級(jí)。

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

WITH 遞歸

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

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

實(shí)例

創(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)

接下來(lái)讓我們使用 RECURSIVE 關(guān)鍵字和 WITH 子句編寫(xiě)一個(gè)查詢，查找 SALARY(工資) 字段小于 20000 的數(shù)據(jù)并計(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 語(yǔ)句和 WITH 子句刪除 COMPANY 表中 SALARY(工資) 字段大于等于 30000 的數(shù)據(jù)，并將刪除的數(shù)據(jù)插入 COMPANY1 表，實(shí)現(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

此時(shí)，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)