39
PostgreSQL SQL 語言：查詢
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
從數據庫中檢索數據的過程或命令叫做查詢。在 SQL 裏SELECT命令用於指定查詢。 SELECT命令的一般語法是
[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]
下麵幾個小節描述選擇列表、表表達式和排序聲明的細節。WITH查詢等高級特性將在最後討論。
一個簡單類型的查詢的形式：
SELECT * FROM table1;
假設有一個表叫做table1，這條命令將table1中檢索所有行和所有用戶定義的列（檢索的方法取決於客戶端應用。例如，psql程序將在屏幕上顯示一個 ASCII 形式的表格， 而客戶端庫將提供函數來從檢索結果中抽取單個值）。 選擇列表聲明*意味著所有表表達式提供的列。 一個選擇列表也可以選擇可用列的一個子集或者在使用它們之前對列進行計算。例如，如果table1有叫做a、b和c的列（可能還有其他），那麼你可以用下麵的查詢：
SELECT a, b + c FROM table1;
FROM table1是一種非常簡單的表表達式：它隻讀取了一個表。通常，表表達式可以是基本表、連接和子查詢組成的複雜結構。 但你也可以省略整個表表達式而把SELECT命令當做一個計算器：
SELECT 3 * 4;
如果選擇列表裏的表達式返回變化的結果，那麼這就更有用了。例如，你可以用這種方法調用函數：
SELECT random();
表表達式計算一個表。該表表達式包含一個FROM子句，該子句後麵可以根據需要選用WHERE、GROUP BY和HAVING子句。最簡單的表表達式隻是引用磁盤上的一個表，一個所謂的基本表，但是我們可以用更複雜的表表達式以多種方法修改或組合基本表。
表表達式裏可選的WHERE、GROUP BY和HAVING子句指定一係列對源自FROM子句的表的轉換操作。所有這些轉換最後生成一個虛擬表，它提供行傳遞給選擇列表計算查詢的輸出行。
2.1. FROM子句
FROM 子句從一個用逗號分隔的表引用列表中的一個或更多個其它表中生成一個表。
FROM table_reference [, table_reference [, ...]]
表引用可以是一個表名字（可能有模式限定）或者是一個生成的表， 例如子查詢、一個JOIN結構或者這些東西的複雜組合。如果在FROM子句中引用了多於一個表， 那麼它們被交叉連接（即構造它們的行的笛卡爾積，見下文）。FROM列表的結果是一個中間的虛擬表，該表可以進行由WHERE、GROUP BY和HAVING子句指定的轉換，並最後生成全局的表表達式結果。
如果一個表引用是一個簡單的表名字並且它是表繼承層次中的父表，那麼該表引用將產生該表和它的後代表中的行，除非你在該表名字前麵放上ONLY關鍵字。但是，這種引用隻會產生出現在該命名表中的列 — 在子表中增加的列都會被忽略。
除了在表名前寫ONLY，你可以在表名後麵寫上*來顯式地指定要包括所有的後代表。書寫*並不是必需的，因為該行為是默認值（除非你修改sql_inheritance配置選項的設置）。不過對於強調附加表也應該被搜索，書寫*是有用的。
2.1.1. 連接表
一個連接表是根據特定的連接類型的規則從兩個其它表（真實表或生成表）中派生的表。目前支持內連接、外連接和交叉連接。一個連接表的一般語法是：
T1 join_type T2 [ join_condition ]
所有類型的連接都可以被鏈在一起或者嵌套：T1和T2都可以是連接表。在JOIN子句周圍可以使用圓括號來控製連接順序。如果不使用圓括號，JOIN子句會從左至右嵌套。
連接類型
交叉連接
T1 CROSS JOIN T2
對來自於T1和T2的行的每一種可能的組合（即笛卡爾積），連接表將包含這樣一行：它由所有T1裏麵的列後麵跟著所有T2裏麵的列構成。如果兩個表分別有 N 和 M 行，連接表將有 N * M 行。
FROM T1 CROSS JOIN T2等效於FROM T1 INNER JOIN T2 ON TRUE（見下文）。它也等效於FROM T1,T2。
Note: 當多於兩個表出現時，後一種等效並不嚴格成立，因為JOIN比逗號綁得更緊。例如FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition和FROM T1,T2 INNER JOIN T3 ON condition並不完全相同，因為第一種情況中的condition可以引用T1，但在第二種情況中卻不行。
條件連接
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
INNER和OUTER對所有連接形式都是可選的。INNER是缺省；LEFT、RIGHT和FULL指示一個外連接。
連接條件在ON或USING子句中指定， 或者用關鍵字NATURAL隱含地指定。連接條件決定來自兩個源表中的哪些行是"匹配"的，這些我們將在後文詳細解釋。
可能的條件連接類型是：
INNER JOIN
對於 T1 的每一行 R1，生成的連接表都有一行對應 T2 中的每一個滿足和 R1 的連接條件的行。
LEFT OUTER JOIN
首先，執行一次內連接。然後，為 T1 中每一個無法在連接條件上匹配 T2 裏任何一行的行返回一個連接行，該連接行中 T2 的列用空值補齊。因此，生成的連接表裏為來自 T1 的每一行都至少包含一行。
RIGHT OUTER JOIN
首先，執行一次內連接。然後，為 T2 中每一個無法在連接條件上匹配 T1 裏任何一行的行返回一個連接行，該連接行中 T1 的列用空值補齊。因此，生成的連接表裏為來自 T2 的每一行都至少包含一行。
FULL OUTER JOIN
首先，執行一次內連接。然後，為 T1 中每一個無法在連接條件上匹配 T2 裏任何一行的行返回一個連接行，該連接行中 T2 的列用空值補齊。同樣，為 T2 中每一個無法在連接條件上匹配 T1 裏任何一行的行返回一個連接行，該連接行中 T1 的列用空值補齊。
ON子句是最常見的連接條件的形式：它接收一個和WHERE子句裏用的一樣的布爾值表達式。 如果兩個分別來自T1和T2的行在ON表達式上運算的結果為真，那麼它們就算是匹配的行。
USING是個縮寫符號，它允許你利用特殊的情況：連接的兩端都具有相同的連接列名。它接受共享列名的一個逗號分隔列表，並且為其中每一個共享列構造一個包含等值比較的連接條件。例如用USING (a, b)連接T1和T2會產生連接條件ON T1.a = T2.a AND T1.b = T2.b。
更進一步，JOIN USING的輸出會廢除冗餘列：不需要把匹配上的列都打印出來，因為它們必須具有相等的值。不過JOIN ON會先產生來自T1的所有列，後麵跟上所有來自T2的列；而JOIN USING會先為列出的每一個列對產生一個輸出列，然後先跟上來自T1的剩餘列，最後跟上來自T2的剩餘列。
最後，NATURAL是USING的縮寫形式：它形成一個USING列表， 該列表由那些在兩個表裏都出現了的列名組成。和USING一樣，這些列隻在輸出表裏出現一次。如果不存在公共列，NATURAL的行為將和CROSS JOIN一樣。
Note: USING對於連接關係中的列改變是相當安全的，因為隻有被列出的列會被組合成連接條件。NATURAL的風險更大，因為如果其中一個關係的模式改變會導致出現一個新的匹配列名，就會導致連接將新列也組合成連接條件。
為了解釋這些問題，假設我們有一個表t1：
num | name
-----+------
1 | a
2 | b
3 | c
和t2：
num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz
然後我們用不同的連接方式可以獲得各種結果：
=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)
=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
=> SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
(3 rows)
=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
2 | b |
3 | c | yyy
(3 rows)
=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
| | 5 | zzz
(3 rows)
=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)
用ON指定的連接條件也可以包含與連接不直接相關的條件。這種功能可能對某些查詢很有用，但是需要我們仔細想清楚。例如：
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | |
(3 rows)
注意把限製放在WHERE子句中會產生不同的結果：
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
(1 row)
這是因為放在ON子句中的一個約束在連接之前被處理，而放在WHERE子句中的一個約束是在連接之後被處理。這對內連接沒有關係，但是對於外連接會帶來麻煩。
2.1.2. 表和列別名
你可以給一個表或複雜的表引用指定一個臨時的名字，用於剩下的查詢中引用那些派生的表。這被叫做表別名。
要創建一個表別名，我們可以寫：
FROM table_reference AS alias
或者
FROM table_reference alias
AS關鍵字是可選的。別名可以是任意標識符。
表別名的典型應用是給長表名賦予比較短的標識符， 好讓連接子句更易讀。例如：
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
到這裏，別名成為當前查詢的表引用的新名稱 — 我們不再能夠用該表最初的名字引用它了。因此，下麵的用法是不合法的：
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- 錯誤
表別名主要用於簡化符號，但是當把一個表連接到它自身時必須使用別名，例如：
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
此外，如果一個表引用是一個子查詢，則必須要使用一個別名（見Section 2.1.3）。
圓括弧用於解決歧義。在下麵的例子中，第一個語句將把別名b賦給my_table的第二個實例，但是第二個語句把別名賦給連接的結果：
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
另外一種給表指定別名的形式是給表的列賦予臨時名字，就像給表本身指定別名一樣：
FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )
如果指定的列別名比表裏實際的列少，那麼剩下的列就沒有被重命名。這種語法對於自連接或子查詢特別有用。
如果用這些形式中的任何一種給一個JOIN子句的輸出附加了一個別名， 那麼該別名就在JOIN的作用下隱去了其原始的名字。例如：
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
是合法 SQL，但是：
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
是不合法的：表別名a在別名c外麵是看不到的。
2.1.3. 子查詢
子查詢指定了一個派生表，它必須被包圍在圓括弧裏並且必須被賦予一個表別名（參閱Section 2.1.2）。例如：
FROM (SELECT * FROM table1) AS alias_name
這個例子等效於FROM table1 AS alias_name。更有趣的情況是在子查詢裏麵有分組或聚集的時候， 子查詢不能被簡化為一個簡單的連接。
一個子查詢也可以是一個VALUES列表：
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
AS names(first, last)
再次的，這裏要求一個表別名。為VALUES列表中的列分配別名是可選的，但是選擇這樣做是一個好習慣。
2.1.4. 表函數
表函數是那些生成一個行集合的函數，這個集合可以是由基本數據類型（標量類型）組成， 也可以是由複合數據類型（表行）組成。它們的用法類似一個表、視圖或者在查詢的FROM子句裏的子查詢。表函數返回的列可以像一個表列、視圖或者子查詢那樣被包含在SELECT、JOIN或WHERE子句裏。
也可以使用ROWS FROM語法將平行列返回的結果組合成表函數； 這種情況下結果行的數量是最大一個函數結果的數量，較小的結果會用空值來填充。
function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
如果指定了WITH ORDINALITY子句，一個額外的 bigint類型的列將會被增加到函數的結果列中。這個列對 函數結果集的行進行編號，編號從 1 開始（這是對 SQL 標準語法 UNNEST ... WITH ORDINALITY的一般化）。默認情 況下，序數列被稱為ordinality，但也可以通過使用一個 AS子句給它分配一個不同的列名。
調用特殊的表函數UNNEST可以使用任意數量的數組參數， 它會返回對應的列數，就好像在每一個參數上單獨調用 UNNEST（Section 9.18）並且使用 ROWS FROM結構把它們組合起來。
UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
如果沒有指定table_alias，該函數名將被用作 表名。在ROWS FROM()結構的情況中，會使用第一個函數名。
如果沒有提供列的別名，那麼對於一個返回基數據類型的函數，列名也與該函數 名相同。對於一個返回組合類型的函數，結果列會從該類型的屬性得到名稱。
例子：
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
有時侯，定義一個能夠根據它們被調用方式返回不同列集合的表函數是很有用的。為了支持這些，表函數可以被聲明為返回偽類型record。 如果在查詢裏使用這樣的函數，那麼我們必須在查詢中指定所預期的行結構，這樣係統才知道如何分析和規劃該查詢。這種語法是這樣的：
function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )
在沒有使用ROWS FROM()語法時， column_definition列表會取代無法附著在 FROM項上的列別名列表，列定義中的名稱就起到列別名的作用。 在使用ROWS FROM()語法時， 可以為每一個成員函數單獨附著一個 column_definition列表；或者在隻有一個成員 函數並且沒有WITH ORDINALITY子句的情況下，可以在 ROWS FROM()後麵寫一個 column_definition列表來取代一個列別名列表。
考慮下麵的例子：
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
dblink函數（dblink模塊的一部分）執行一個遠程的查詢。它被聲明為返回record，因為它可能會被用於任何類型的查詢。 實際的列集必須在調用它的查詢中指定，這樣分析器才知道類似*這樣的東西應該擴展成什麼樣子。
2.1.5. LATERAL子查詢
可以在出現於FROM中的子查詢前放置關鍵詞LATERAL。這允許它們引用前麵的FROM項提供的列（如果沒有LATERAL，每一個子查詢將被獨立計算，並且因此不能被其他FROM項交叉引用）。
出現在FROM中的表函數的前麵也可以被放上關鍵詞LATERAL，但對於函數該關鍵詞是可選的，在任何情況下函數的參數都可以包含對前麵的FROM項提供的列的引用。
一個LATERAL項可以出現在FROM列表頂層，或者出現在一個JOIN樹中。在後一種情況下，如果它出現在JOIN的右部，那麼它也可以引用 在JOIN左部的任何項。
如果一個FROM項包含LATERAL交叉引用，計算過程如下：對於提供交叉引用列的FROM項的每一行，或者多個提供這些列的多個FROM項的行集合，LATERAL項將被使用該行或者行集中的列值進行計算。得到的結果行將和它們被計算出來的行進行正常的連接。對於來自這些列的源表的每一行或行集，該過程將重複。
LATERAL的一個簡單例子：
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
這不是非常有用，因為它和一種更簡單的形式得到的結果完全一樣：
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
在必須要使用交叉引用列來計算那些即將要被連接的行時，LATERAL是最有用的。一種常用的應用是為一個返回集合的函數提供一個參數值。例如，假設vertices(polygon)返回一個多邊形的頂點集合，我們可以這樣標識存儲在一個表中的多邊形中靠近的頂點：
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
這個查詢也可以被寫成：
SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
或者寫成其他幾種等價的公式（正如以上提到的，LATERAL關鍵詞在這個例子中並不是必不可少的，但是我們在這裏使用它是為了使表述更清晰）。
有時候也會很特別地把LEFT JOIN放在一個LATERAL子查詢的前麵，這樣即使LATERAL子查詢對源行不產生行，源行也會出現在結果中。例如，如果get_product_names()返回一個製造商製造的產品的名字，但是某些製造商在我們的表中目前沒有製造產品，我們可以找出哪些製造商是這樣：
SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;
2.2. WHERE子句
WHERE 子句的語法是
WHERE search_condition
這裏的search_condition是任意返回一個boolean類型值的值表達式。
在完成對FROM子句的處理之後，生成的虛擬表的每一行都會對根據搜索條件進行檢查。 如果該條件的結果是真，那麼該行被保留在輸出表中；否則（也就是說，如果結果是假或空）就把它拋棄。搜索條件通常至少要引用一些在FROM子句裏生成的列；雖然這不是必須的，但如果不引用這些列，那麼WHERE子句就沒什麼用了。
Note:
內連接的連接條件既可以寫在WHERE子句也可以寫在JOIN子句裏。例如，這些表表達式是等效的：
FROM a, b WHERE a.id = b.id AND b.val > 5
和：
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
或者可能還有：
FROM a NATURAL JOIN b WHERE b.val > 5
你想用哪個隻是一個風格問題。FROM子句裏的JOIN語法可能不那麼容易移植到其它SQL數據庫管理係統中。 對於外部連接而言沒有選擇：它們必須在FROM子句中完成。 外部連接的ON或USING子句不等於WHERE條件，因為它導致最終結果中行的增加（對那些不匹配的輸入行）和減少。
這裏是一些WHERE子句的例子：
SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
在上麵的例子裏，fdt是從FROM子句中派生的表。 那些不符合WHERE子句的搜索條件的行會被從fdt中刪除。請注意我們把標量子查詢當做一個值表達式來用。 和任何其它查詢一樣，子查詢裏可以使用複雜的表表達式。同時還請注意fdt在子查詢中也被引用。隻有在c1也是作為子查詢輸入表的生成表的列時，才必須把c1限定成fdt.c1。但限定列名字可以增加語句的清晰度，即使有時候不是必須的。這個例子展示了一個外層查詢的列名範圍如何擴展到它的內層查詢。
2.3. GROUP BY和HAVING子句
在通過了WHERE過濾器之後，生成的輸入表可以使用GROUP BY子句進行分組，然後用HAVING子句刪除一些分組行。
SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [, grouping_column_reference]...
GROUP BY 子句被用來把表中在所列出的列上具有相同值的行分組在一起。 這些列的列出順序並沒有什麼關係。其效果是把每組具有相同值的行組合為一個組行，它代表該組裏的所有行。 這樣就可以刪除輸出裏的重複和/或計算應用於這些組的聚集。例如：
=> SELECT * FROM test1;
x | y
---+---
a | 3
c | 2
b | 5
a | 1
(4 rows)
=> SELECT x FROM test1 GROUP BY x;
x
---
a
b
c
(3 rows)
在第二個查詢裏，我們不能寫成SELECT * FROM test1 GROUP BY x， 因為列y裏沒有哪個值可以和每個組相關聯起來。被分組的列可以在選擇列表中引用是因為它們在每個組都有單一的值。
通常，如果一個表被分了組，那麼沒有在GROUP BY中列出的列都不能被引用，除非在聚集表達式中被引用。 一個用聚集表達式的例子是：
=> SELECT x, sum(y) FROM test1 GROUP BY x;
x | sum
---+-----
a | 4
b | 5
c | 2
(3 rows)
這裏的sum是一個聚集函數，它在整個組上計算出一個單一值。
Tip: 沒有聚集表達式的分組實際上計算了一個列中可區分值的集合。我們也可以用DISTINCT子句實現（參閱Section 3.3）。
這裏是另外一個例子：它計算每種產品的總銷售額（而不是所有產品的總銷售額）：
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
在這個例子裏，列product_id、p.name和p.price必須在GROUP BY子句裏， 因為它們都在查詢的選擇列表裏被引用到（但見下文）。列s.units不必在GROUP BY列表裏，因為它隻是在一個聚集表達式（sum(...)）裏使用，它代表一組產品的銷售額。對於每種產品，這個查詢都返回一個該產品的所有銷售額的總和行。
如果產品表被建立起來，例如product_id是主鍵，那麼在上麵的例子中用product_id來分組就夠了，因為名稱和價格都是函數依賴於產品ID，並且關於為每個產品ID分組返回哪個名稱和價格值就不會有歧義。
在嚴格的 SQL 裏，GROUP BY隻能對源表的列進行分組，但PostgreSQL把這個擴展為也允許GROUP BY去根據選擇列表中的列分組。也允許對值表達式進行分組，而不僅是簡單的列名。
如果一個表已經用GROUP BY子句分了組，然後你又隻對其中的某些組感興趣， 那麼就可以用HAVING子句，它很象WHERE子句，用於從結果中刪除一些組。其語法是：
SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression
在HAVING子句中的表達式可以引用分組的表達式和未分組的表達式（後者必須涉及一個聚集函數）。
例子：
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
x | sum
---+-----
a | 4
b | 5
(2 rows)
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
x | sum
---+-----
a | 4
b | 5
(2 rows)
再次，一個更現實的例子：
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;
在上麵的例子裏，WHERE子句用那些非分組的列選擇數據行（表達式隻是對那些最近四周發生的銷售為真）。 而HAVING子句限製輸出為總銷售收入超過 5000 的組。請注意聚集表達式不需要在查詢中的所有地方都一樣。
如果一個查詢包含聚集函數調用，但是沒有GROUP BY子句，分組仍然會發生：結果是一個單一行（或者根本就沒有行，如果該單一行被HAVING所消除）。它包含一個HAVING子句時也是這樣，即使沒有任何聚集函數調用或者GROUP BY子句。
2.4. GROUPING SETS、CUBE和ROLLUP
使用分組集的概念可以實現比上述更加複雜的分組操作。由 FROM和WHERE子句選出的數據被按照每一個指定 的分組集單獨分組，按照簡單GROUP BY子句對每一個分組計算 聚集，然後返回結果。例如：
=> SELECT * FROM items_sold;
brand | size | sales
-------+------+-------
Foo | L | 10
Foo | M | 20
Bar | M | 15
Bar | L | 5
(4 rows)
=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
(5 rows)
GROUPING SETS的每一個子列表可以指定一個或者多個列或者表達式， 它們將按照直接出現在GROUP BY子句中同樣的方式被解釋。一個空的 分組集表示所有的行都要被聚集到一個單一分組（即使沒有輸入行存在也會被輸出） 中，這就像前麵所說的沒有GROUP BY子句的聚集函數的情況一樣。
對於分組列或表達式沒有出現在其中的分組集的結果行，對分組列或表達式的引用會 被空值所替代。要區分一個特定的輸出行來自於哪個分組，請見 Table 9-55。
PostgreSQL 中提供了一種簡化方法來指定兩種常用類型的分組集。下麵形式的子句
ROLLUP ( e1, e2, e3, ... )
表示給定的表達式列表及其所有前綴（包括空列表），因此它等效於
GROUPING SETS (
( e1, e2, e3, ... ),
...
( e1, e2 ),
( e1 ),
( )
)
這通常被用來分析曆史數據，例如按部門、區和公司範圍計算的總薪水。
下麵形式的子句
CUBE ( e1, e2, ... )
表示給定的列表及其可能的子集（即冪集）。因此
CUBE ( a, b, c )
等效於
GROUPING SETS (
( a, b, c ),
( a, b ),
( a, c ),
( a ),
( b, c ),
( b ),
( c ),
( )
)
CUBE或ROLLUP子句中的元素可以是表達式或者 圓括號中的元素子列表。在後一種情況中，對於生成分組集的目的來說，子列 表被當做單一單元來對待。例如：
CUBE ( (a, b), (c, d) )
等效於
GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
並且
ROLLUP ( a, (b, c), d )
等效於
GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
CUBE和ROLLUP可以被直接用在 GROUP BY子句中，也可以被嵌套在一個 GROUPING SETS子句中。如果一個 GROUPING SETS子句被嵌套在另一個同類子句中， 效果和把內層子句的所有元素直接寫在外層子句中一樣。
如果在一個GROUP BY子句中指定了多個分組項，那麼最終的 分組集列表是這些項的叉積。例如：
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
等效於
GROUP BY GROUPING SETS (
(a, b, c, d), (a, b, c, e),
(a, b, d), (a, b, e),
(a, c, d), (a, c, e),
(a, d), (a, e)
)
Note: 在表達式中，結構(a, b)通常被識別為一個 a 行構造器。在 GROUP BY子句中，這不會在表達式的頂層應用，並且 (a, b)會按照上麵所說的被解析為一個表達式的列表。如果出於 某種原因你在分組表達式中需要一個行構造器，請使用 ROW(a, b)。
2.5. 窗口函數處理
如果查詢包含任何窗口函數（見Section 3.5、Section 9.21和Section 4.2.8），這些函數將在任何分組、聚集和HAVING過濾被執行之後被計算。也就是說如果查詢使用了任何聚集、GROUP BY或HAVING，則窗口函數看到的行是分組行而不是來自於FROM/WHERE的原始表行。
當多個窗口函數被使用，所有在窗口定義中有句法上等效的PARTITION BY和ORDER BY子句的窗口函數被保證在數據上的同一趟掃描中計算。因此它們將會看到相同的排序順序，即使ORDER BY沒有唯一地決定一個順序。但是，對於具有不同PARTITION BY或ORDER BY定義的函數的計算沒有這種保證（在這種情況中，在多個窗口函數計算之間通常要求一個排序步驟，並且並不保證保留行的順序，即使它的ORDER BY把這些行視為等效的）。
目前，窗口函數總是要求排序好的數據，並且這樣查詢的輸出總是被根據窗口函數的PARTITION BY/ORDER BY子句的一個或者另一個排序。但是，我們不推薦依賴於此。如果你希望確保結果以特定的方式排序，請顯式使用頂層的ORDER BY子句。
如前麵的小節說明的那樣， 在SELECT命令裏的表表達式構造了一個中間的虛擬表， 方法可能有組合表、視圖、消除行、分組等等。這個表最後被選擇列表傳遞下去處理。選擇列表判斷中間表的哪個列是實際輸出。
3.1. 選擇列表項
最簡單的選擇列表類型是*，它發出表表達式生成的所有列。否則，一個選擇列表是一個逗號分隔的值表達式的列表。 例如，它可能是一個列名的列表：
SELECT a, b, c FROM ...
列名字a、b和c要麼是在FROM子句裏引用的表中列的實際名字，要麼是像Section 7.2.1.2裏解釋的那樣的別名。在選擇列表裏可用的名字空間和在WHERE子句裏的一樣， 除非你使用了分組，這時候它和HAVING子句一樣。
如果超過一個表有同樣的列名，那麼你還必須給出表名字，如：
SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
在使用多個表時，要求一個特定表的所有列也是有用的：
SELECT tbl1.*, tbl2.a FROM ...
（另見Section 2.2）。
如果將任意值表達式用於選擇列表，那麼它在概念上向返回的表中增加了一個新的虛擬列。 值表達式為結果的每一行進行一次計算，對任何列引用替換行的值。 不過選擇列表中的這個表達式並非一定要引用來自FROM子句中表表達式裏麵的列，例如它也可以是任意常量算術表達式。
3.2. 列標簽
選擇列表中的項可以被賦予名字，用於進一步的處理。 例如為了在一個ORDER BY子句中使用或者為了客戶端應用顯示。例如：
SELECT a AS value, b + c AS sum FROM ...
如果沒有使用AS指定輸出列名，那麼係統會分配一個缺省的列名。對於簡單的列引用， 它是被引用列的名字。對於函數調用，它是函數的名字。對於複雜表達式，係統會生成一個通用的名字。
隻有在新列無法匹配任何PostgreSQL關鍵詞（見Appendix C）時，AS關鍵詞是可選的。為了避免一個關鍵字的意外匹配，你可以使用雙引號來修飾列名。例如，VALUE是一個關鍵字，所以下麵的語句不會工作：
SELECT a value, b + c AS sum FROM ...
但是這個可以：
SELECT a "value", b + c AS sum FROM ...
為了防止未來可能的關鍵詞增加，我們推薦總是寫AS或者用雙引號修飾輸出列名。
Note:
輸出列的命名和在FROM子句裏的命名是不一樣的 （參閱Section 2.1.2）。 它實際上允許你對同一個列命名兩次，但是在選擇列表中分配的名字是要傳遞下去的名字。
3.3. DISTINCT
在處理完選擇列表之後，結果表可以可選的刪除重複行。我們可以直接在SELECT後麵寫上DISTINCT關鍵字來指定：
SELECT DISTINCT select_list ...
（如果不用DISTINCT你可以用ALL關鍵詞來指定獲得的所有行的缺省行為）。
顯然，如果兩行裏至少有一個列有不同的值，那麼我們認為它是可區分的。空值在這種比較中被認為是相同的。
另外，我們還可以用任意表達式來判斷什麼行可以被認為是可區分的：
SELECT DISTINCT ON (expression [, expression ...]) select_list ...
這裏expression是任意值表達式，它為所有行計算。如果一個行集合裏所有表達式的值是一樣的， 那麼我們認為它們是重複的並且因此隻有第一行保留在輸出中。請注意這裏的一個集合的"第一行"是不可預料的， 除非你在足夠多的列上對該查詢排了序，保證到達DISTINCT過濾器的行的順序是唯一的（DISTINCT ON處理是發生在ORDER BY排序後麵的）。
DISTINCT ON子句不是 SQL 標準的一部分， 有時候有人認為它是一個糟糕的風格，因為它的結果是不可判定的。 如果有選擇的使用GROUP BY和在FROM中的子查詢，那麼我們可以避免使用這個構造， 但是通常它是更方便的候選方法。
兩個查詢的結果可以用集合操作並、交、差進行組合。語法是
query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2
query1和query2都是可以使用以上所有特性的查詢。集合操作也可以嵌套和級連，例如
query1 UNION query2 UNION query3
實際執行的是：
(query1 UNION query2) UNION query3
UNION有效地把query2的結果附加到query1的結果上（不過我們不能保證這就是這些行實際被返回的順序）。此外，它將刪除結果中所有重複的行， 就象DISTINCT做的那樣，除非你使用了UNION ALL。
INTERSECT返回那些同時存在於query1和query2的結果中的行，除非聲明了INTERSECT ALL， 否則所有重複行都被消除。
EXCEPT返回所有在query1的結果中但是不在query2的結果中的行（有時侯這叫做兩個查詢的差）。同樣的，除非聲明了EXCEPT ALL，否則所有重複行都被消除。
為了計算兩個查詢的並、交、差，這兩個查詢必須是"並操作兼容的"，也就意味著它們都返回同樣數量的列， 並且對應的列有兼容的數據類型。
在一個查詢生成一個輸出表之後（在處理完選擇列表之後），還可以選擇性地對它進行排序。如果沒有選擇排序，那麼行將以未指定的順序返回。 這時候的實際順序將取決於掃描和連接計劃類型以及行在磁盤上的順序，但是肯定不能依賴這些東西。一種特定的順序隻能在顯式地選擇了排序步驟之後才能被保證。
ORDER BY子句指定了排序順序：
SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]
排序表達式可以是任何在查詢的選擇列表中合法的表達式。一個例子是：
SELECT a, b FROM table1 ORDER BY a + b, c;
當多於一個表達式被指定，後麵的值將被用於排序那些在前麵值上相等的行。每一個表達式後可以選擇性地放置一個ASC或DESC關鍵詞來設置排序方向為升序或降序。ASC順序是默認值。升序會把較小的值放在前麵，而"較小"則由<操作符定義。相似地，降序則由>操作符定義。 [1]
NULLS FIRST和NULLS LAST選項將可以被用來決定在排序順序中，空值是出現在非空值之前或者出現在非空值之後。默認情況下，排序時空值被認為比任何非空值都要大，即NULLS FIRST是DESC順序的默認值，而不是NULLS LAST的默認值。
注意順序選項是對每一個排序列獨立考慮的。例如ORDER BY x, y DESC表示ORDER BY x ASC, y DESC，而和ORDER BY x DESC, y DESC不同。
一個sort_expression也可以是列標簽或者一個輸出列的編號，如：
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
兩者都根據第一個輸出列排序。注意一個輸出列的名字必須孤立，即它不能被用在一個表達式中 — 例如，這是不正確的：
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- 錯誤
該限製是為了減少混淆。如果一個ORDER BY項是一個單一名字並且匹配一個輸出列名或者一個表表達式的列，仍然會出現混淆。在這種情況中輸出列將被使用。隻有在你使用AS來重命名一個輸出列來匹配某些其他表列的名字時，這才會導致混淆。
ORDER BY可以被應用於UNION、INTERSECT或EXCEPT組合的結果，但是在這種情況中它隻被允許根據輸出列名或編號排序，而不能根據表達式排序。
Notes
[1]
事實上，PostgreSQL為表達式的數據類型使用默認B-tree操作符類來決定ASC和DESC的排序順序。照慣例，數據類型將被建立，這樣<和>操作符負責這個排序順序，但是一個用戶定義的數據類型的設計者可以選擇做些不同的設置。
LIMIT和OFFSET允許你隻檢索查詢剩餘部分產生的行的一部分：
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]
如果給出了一個限製計數，那麼會返回數量不超過該限製的行（但可能更少些，因為查詢本身可能生成的行數就比較少）。LIMIT ALL的效果和省略LIMIT子句一樣，就像是LIMIT帶有 NULL 參數一樣。
OFFSET說明在開始返回行之前忽略多少行。OFFSET 0的效果和省略OFFSET子句是一樣的，並且LIMIT NULL的效果和省略LIMIT子句一樣，就像是OFFSET帶有 NULL 參數一樣。
如果OFFSET和LIMIT都出現了， 那麼在返回LIMIT個行之前要先忽略OFFSET行。
如果使用LIMIT，那麼用一個ORDER BY子句把結果行約束成一個唯一的順序是很重要的。否則你就會拿到一個不可預料的該查詢的行的子集。你要的可能是第十到第二十行，但以什麼順序的第十到第二十？除非你指定了ORDER BY，否則順序是不知道的。
查詢優化器在生成查詢計劃時會考慮LIMIT，因此如果你給定LIMIT和OFFSET，那麼你很可能收到不同的規劃（產生不同的行順序）。因此，使用不同的LIMIT/OFFSET值選擇查詢結果的不同子集將生成不一致的結果，除非你用ORDER BY強製一個可預測的順序。這並非bug， 這是一個很自然的結果，因為 SQL 沒有許諾把查詢的結果按照任何特定的順序發出，除非用了ORDER BY來約束順序。
被OFFSET子句忽略的行仍然需要在服務器內部計算；因此，一個很大的OFFSET的效率可能還是不夠高。
VALUES提供了一種生成"常量表"的方法，它可以被使用在一個查詢中而不需要實際在磁盤上創建一個表。語法是：
VALUES ( expression [, ...] ) [, ...]
每一個被圓括號包圍的表達式列表生成表中的一行。列表都必須具有相同數據的元素（即表中列的數目），並且在每個列表中對應的項必須具有可兼容的數據類型。分配給結果的每一列的實際數據類型使用和UNION相同的規則確定（參見Section 10.5）。
一個例子：
VALUES (1, 'one'), (2, 'two'), (3, 'three');
將會返回一個有兩列三行的表。它實際上等效於：
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
在默認情況下，PostgreSQL將column1、column2等名字分配給一個VALUES表的列。這些列名不是由SQL標準指定的，並且不同的數據庫係統的做法也不同，因此通常最好使用表別名列表來重寫這些默認的名字，像這樣：
=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
num | letter
-----+--------
1 | one
2 | two
3 | three
(3 rows)
在句法上，後麵跟隨著表達式列表的VALUES列表被視為和
SELECT select_list FROM table_expression
一樣，並且可以出現在SELECT能出現的任何地方。例如，你可以把它用作UNION的一部分，或者附加一個sort_specification（ORDER BY、LIMIT和/或OFFSET）給它。VALUES最常見的用途是作為一個INSERT命令的數據源，以及作為一個子查詢。
WITH提供了一種方式來書寫在一個大型查詢中使用的輔助語句。這些語句通常被稱為公共表表達式或CTE，它們可以被看成是定義隻在一個查詢中存在的臨時表。在WITH子句中的每一個輔助語句可以是一個SELECT、INSERT、UPDATE或DELETE，並且WITH子句本身也可以被附加到一個主語句，主語句也可以是SELECT、INSERT、UPDATE或DELETE。
8.1. WITH中的SELECT
WITH中SELECT的基本價值是將複雜的查詢分解稱為簡單的部分。一個例子：
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
它隻顯示在高銷售區域每種產品的銷售總額。WITH子句定義了兩個輔助語句regional_sales和top_regions，其中regional_sales的輸出用在top_regions中而top_regions的輸出用在主SELECT查詢。這個例子可以不用WITH來書寫，但是我們必須要用兩層嵌套的子SELECT。使用這種方法要更簡單些。
可選的RECURSIVE修飾符將WITH從單純的句法便利變成了一種在標準SQL中不能完成的特性。通過使用RECURSIVE，一個WITH查詢可以引用它自己的輸出。一個非常簡單的例子是計算從1到100的整數合的查詢：
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
一個遞歸WITH查詢的通常形式總是一個非遞歸項，然後是UNION（或者UNION ALL），再然後是一個遞歸項，其中隻有遞歸項能夠包含對於查詢自身輸出的引用。這樣一個查詢可以被這樣執行：
遞歸查詢求值
計算非遞歸項。對UNION（但不對UNION ALL），拋棄重複行。把所有剩餘的行包括在遞歸查詢的結果中，並且也把它們放在一個臨時的工作表中。
隻要工作表不為空，重複下列步驟：
計算遞歸項，用當前工作表的內容替換遞歸自引用。對UNION（不是UNION ALL），拋棄重複行以及那些與之前結果行重複的行。將剩下的所有行包括在遞歸查詢的結果中，並且也把它們放在一個臨時的中間表中。
用中間表的內容替換工作表的內容，然後清空中間表。
Note:
嚴格來說，這個處理是迭代而不是遞歸，但是RECURSIVE是SQL標準委員會選擇的術語。
在上麵的例子中，工作表在每一步隻有一個行，並且它在連續的步驟中取值從1到100。在第100步，由於WHERE子句導致沒有輸出，因此查詢終止。
遞歸查詢通常用於處理層次或者樹狀結構的數據。一個有用的例子是這個用於找到一個產品的直接或間接部件的查詢，隻要給定一個顯示了直接包含關係的表：
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
在使用遞歸查詢時，確保查詢的遞歸部分最終將不返回元組非常重要，否則查詢將會無限循環。在某些時候，使用UNION替代UNION ALL可以通過拋棄與之前輸出行重複的行來達到這個目的。不過，經常有循環不涉及到完全重複的輸出行：它可能隻需要檢查一個或幾個域來看相同點之前是否達到過。處理這種情況的標準方法是計算一個已經訪問過值的數組。例如，考慮下麵這個使用link域搜索表graph的查詢：
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
)
SELECT * FROM search_graph;
如果link關係包含環，這個查詢將會循環。因為我們要求一個"depth"輸出，僅僅將UNION ALL 改為UNION不會消除循環。反過來在我們順著一個特定鏈接路徑搜索時，我們需要識別我們是否再次到達了一個相同的行。我們可以項這個有循環傾向的查詢增加兩個列path和cycle：
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[g.id],
false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
path || g.id,
g.id = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;
除了阻止環，數組值對於它們自己的工作顯示到達任何特定行的"path"也有用。
在通常情況下如果需要檢查多於一個域來識別一個環，請用行數組。例如，如果我們需要比較域f1和f2：
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[ROW(g.f1, g.f2)],
false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
path || ROW(g.f1, g.f2),
ROW(g.f1, g.f2) = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;
Tip: 在通常情況下隻有一個域需要被檢查來識別一個環，可以省略ROW()語法。這允許使用一個簡單的數組而不是一個組合類型數組，可以獲得效率。
Tip: 遞歸查詢計算算法使用寬度優先搜索順序產生它的輸出。你可以通過讓外部查詢ORDER BY一個以這種方法構建的"path"，用來以深度優先搜索順序顯示結果。
當你不確定查詢是否可能循環時，一個測試查詢的有用技巧是在父查詢中放一個LIMIT。例如，這個查詢沒有LIMIT時會永遠循環：
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
這會起作用，因為PostgreSQL的實現隻計算WITH查詢中被父查詢實際取到的行。不推薦在生產中使用這個技巧，因為其他係統可能以不同方式工作。同樣，如果你讓外層查詢排序遞歸查詢的結果或者把它們連接成某種其他表，這個技巧將不會起作用，因為在這些情況下外層查詢通常將嚐試取得WITH查詢的所有輸出。
WITH查詢的一個有用的特性是在每一次父查詢的執行中它們隻被計算一次，即使它們被父查詢或兄弟WITH查詢引用了超過一次。因此，在多個地方需要的昂貴計算可以被放在一個WITH查詢中來避免冗餘工作。另一種可能的應用是阻止不希望的多個函數計算產生副作用。但是，從另一方麵來看，優化器不能將來自父查詢的約束下推到WITH查詢中而不是一個普通子查詢。WITH查詢通常將會被按照所寫的方式計算，而不抑製父查詢以後可能會拋棄的行（但是，如上所述，如果對查詢的引用隻請求有限數目的行，計算可能會提前停止）。
以上的例子隻展示了和SELECT一起使用的WITH，但是它可以被以相同的方式附加在INSERT、UPDATE或DELETE上。在每一種情況中，它實際上提供了可在主命令中引用的臨時表。
8.2. WITH中的數據修改語句
你可以在WITH中使用數據修改語句（INSERT、UPDATE或DELETE）。這允許你在同一個查詢中執行多個而不同操作。一個例子：
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
這個查詢實際上從products把行移動到products_log。WITH中的DELETE刪除來自products的指定行，以它的RETURNING子句返回它們的內容，並且接著主查詢讀該輸出並將它插入到products_log。
上述例子中好的一點是WITH子句被附加給INSERT，而沒有附加給INSERT的子SELECT。這是必需的，因為數據修改語句隻允許出現在附加給頂層語句的WITH子句中。不過，普通WITH可見性規則應用，這樣才可能從子SELECT中引用到WITH語句的輸出。
正如上述例子所示，WITH中的數據修改語句通常具有RETURNING子句。它是RETURNING子句的輸出，不是數據修改語句的目標表，它形成了剩餘查詢可以引用的臨時表。如果一個WITH中的數據修改語句缺少一個RETURNING子句，則它形不成臨時表並且不能在剩餘的查詢中被引用。但是這樣一個語句將被執行。一個非特殊使用的例子：
WITH t AS (
DELETE FROM foo
)
DELETE FROM bar;
這個例子將從表foo和bar中移除所有行。被報告給客戶端的受影響行的數目可能隻包括從bar中移除的行。
數據修改語句中不允許遞歸自引用。在某些情況中可以采取引用一個遞歸WITH的輸出來操作這個限製，例如：
WITH RECURSIVE included_parts(sub_part, part) AS (
SELECT sub_part, part FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_parts);
這個查詢將會移除一個產品的所有直接或間接子部件。
WITH中的數據修改語句隻被執行一次，並且總是能結束，而不管主查詢是否讀取它們所有（或者任何）的輸出。注意這和WITH中SELECT的規則不同：正如前一小節所述，直到主查詢要求SELECT的輸出時，SELECT才會被執行。
The sub-statements in WITH中的子語句被和每一個其他子語句以及主查詢並發執行。因此在使用WITH中的數據修改語句時，指定更新的順序實際是以不可預測的方式發生的。所有的語句都使用同一個snapshot執行（參見Chapter 13），因此它們不能"看見"在目標表上另一個執行的效果。這減輕了行更新的實際順序的不可預見性的影響，並且意味著RETURNING數據是在不同WITH子語句和主查詢之間傳達改變的唯一方法。其例子
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
外層SELECT可以返回在UPDATE動作之前的原始價格，而在
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
外部SELECT將返回更新過的數據。
在一個語句中試圖兩次更新同一行是不被支持的。隻會發生一次修改，但是該辦法不能很容易地（有時是不可能）可靠地預測哪一個會被執行。這也應用於刪除一個已經在同一個語句中被更新過的行：隻有更新被執行。因此你通常應該避免嚐試在一個語句中嚐試兩次修改同一個行。尤其是防止書寫可能影響被主語句或兄弟子語句修改的相同行。這樣一個語句的效果將是不可預測的。
當前，在WITH中一個數據修改語句中被用作目標的任何表不能有條件規則、ALSO規則或INSTEAD規則，這些規則會擴展成為多個語句。
最後更新：2017-08-17 14:32:30