在PostgreSQL裡,提供了一種方法寫一個大的查詢中使用的輔助報表與查詢。它有助於打破復雜和大型查詢簡單易讀的形式。這些聲明,這是通常被稱為通用表表達式或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現有的表名可以是相同的將被優先考慮。
可以使用數據修改語句(INSERT,UPDATE或DELETE)與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)
現在,讓我們寫一個查詢使用遞歸關鍵字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子句編寫一個查詢,如下所示。首先創建一個類似於表公司表COMPANY1。查詢在這個例子中,有效地移動行,從COMPANY到COMPANY1。刪除刪除指定的公司行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)