PostgreSQL - WITH 子句

---

---

在PostgreSQL中，WITH查詢提供了一種編寫輔助語句以用於更大查詢的方法。它有助於將複雜的大型查詢分解成更簡單的形式，使其更易於閱讀。這些語句通常稱為公用表表達式（CTE），可以認為是隻為一個查詢存在的臨時表。

WITH查詢作為CTE查詢，在子查詢被多次執行時特別有用。它在臨時表的位置同樣有用。它只計算一次聚合，並允許我們在查詢中透過其名稱引用它（可能多次）。

必須在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可以引用自身，即WITH查詢可以引用其自身的輸出，因此得名遞迴。

示例

考慮表COMPANY，其記錄如下：

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

上面給出的PostgreSQL語句將產生以下結果：

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子句編寫一個查詢，以查詢小於20000的工資總和，如下所示：

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

上面給出的PostgreSQL語句將產生以下結果：

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

讓我們使用資料修改語句以及WITH子句編寫一個查詢，如下所示。

首先，建立一個類似於COMPANY表的COMPANY1表。此示例中的查詢有效地將行從COMPANY移動到COMPANY1。WITH中的DELETE刪除COMPANY中指定的行，透過其RETURNING子句返回其內容；然後主查詢讀取該輸出並將其插入到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);

上面給出的PostgreSQL語句將產生以下結果：

INSERT 0 3

現在，COMPANY和COMPANY1表中的記錄如下：

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


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