PostgreSQL--with子句

在PostgreSQL中，WITH查詢提供了一種編寫輔助語句的方法，以便在更大的查詢中使用。它有助于将複雜的大型查詢分解為更簡單的表單，便于閱讀。這些語句通常稱為公共表表達式(Common Table Expressions)或cte，可以認為它們定義了僅為一個查詢而存在的臨時表。

WITH查詢是CTE查詢，在多次執行子查詢時特别有用。它同樣有助于替代臨時表。它計算聚合一次，并允許我們在查詢中通過它的名稱(可能是多次)引用它。支援在select、delete、update、insert語句中。

WITH子句必須在查詢中使用之前定義。

WITH查詢的基本文法如下：

 其中name_for_summary_data是WITH子句的名稱。name_for_summary_data可以與現有的表名相同，并且具有優先級。

可以在WITH中使用資料修改語句(插入、更新或删除)。這允許你在同一個查詢中執行多個不同的操作。

遞歸的with語句

遞歸查詢或層次查詢是CTE的一種形式，CTE可以引用自身，即，WITH查詢可以引用它自己的輸出，是以可以遞歸。

示例：考慮表company有以下記錄：

 現在，讓我們編寫一個查詢，使用WITH子句從上面的表中選擇記錄，如下所示：

 上面給出的PostgreSQL語句将産生以下結果：

現在，讓我們使用RECURSIVE關鍵字和with子句編寫一個查詢，查找工資總額小于20000的工資總和，如下所示：

這裡的n表示的是column_list(如果不寫，預設是as後括号中傳回的列)

上面給出的PostgreSQL語句将産生以下結果：

讓我們使用資料修改語句和with子句編寫一個查詢，如下所示：

首先，建立一個類似于表COMPANY的表COMPANY1。示例中的查詢有效地将行從COMPANY移動到COMPANY1。WITH中的delete從company中删除指定的行，通過returning子句傳回它們的内容;然後主查詢讀取輸出并将其插入COMPANY1表：

 面給出的PostgreSQL語句将産生以下結果：

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

CTE的一些優點：

1.提高複雜查詢的可讀性。你可以使用cte以更有組織和可讀性的方式組織複雜的查詢。

2.能夠建立遞歸查詢。遞歸查詢是引用自身的查詢。當你想查詢層次資料(如組織結構圖或材料清單)時，遞歸查詢非常友善。

3.與視窗函數一起使用。你可以結合使用CTEs和視窗函數來建立一個初始結果集，并使用另一個select語句來進一步處理這個結果集。

參考位址：https://www.tutorialspoint.com/postgresql/postgresql_with_clause.htm