?
Ce document utilise Manuel du site Web PHP chinois Libérer
7.2. 表表達(dá)式
一個(gè)表表達(dá)式計(jì)算一個(gè)表，它包含一個(gè)FROM子句，該子句可以根據(jù)需要選用WHERE，GROUP BY和HAVING子句。大部分表表達(dá)式只是指向磁盤上的一個(gè)所謂的基本表，但是我們可以用更復(fù)雜的表表達(dá)式以各種方法修改或組合基本表。
表表達(dá)式里的WHERE，GROUP BY和HAVING子句聲明一系列對(duì)源自FROM子句的表的轉(zhuǎn)換操作。所有這些轉(zhuǎn)換最后生成一個(gè)虛擬表，傳遞給選擇列表計(jì)算輸出行。
7.2.1. FROM子句
FROM Clause從一個(gè)逗號(hào)分隔的表引用列表中生成一個(gè)虛擬表
FROM table_reference [， table_reference [， ...]]
表引用可以是一個(gè)表名字(可能有模式修飾)或者是一個(gè)生成的表，比如子查詢、表連接，或它們的復(fù)雜組合。如果在FROM子句中列出了多于一個(gè)表，那么它們被交叉連接(見下文)形成一個(gè)派生表，該表可以進(jìn)行WHERE，GROUP BY和HAVING子句的轉(zhuǎn)換處理，并最后生成表表達(dá)式的結(jié)果。
如果一個(gè)表引用的是一個(gè)簡單的父表的名字，那么將包括其所有子表的行，除非你在該表名字前面加ONLY關(guān)鍵字(這樣任何子表都會(huì)被忽略)。
7.2.1.1. 連接表
一個(gè)連接表是根據(jù)特定的連接規(guī)則從兩個(gè)其它表(真實(shí)表或生成表)中派生的表。我們支持內(nèi)連接、外連接、交叉連接。
連接類型
- 交叉連接
-
T1 CROSS JOIN T2
對(duì)每個(gè)來自T1和T2的行進(jìn)行組合(即笛卡爾積)，生成的表將包含這樣的行：所有T1里面的字段后面跟著所有T2里面的字段。如果兩表分別有N和M行，連接成的表將有N*M行。
FROM T1 CROSS JOIN T2 等效于FROM T1 ,T2 。它還等效于FROM T1 INNER JOIN T2 ON TRUE(見下文)。
- 條件連接
-
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 T2INNER和OUTER對(duì)所有連接類型都是可選的。INNER為缺省。LEFT，RIGHT和FULL隱含外連接。
連接條件在ON或USING子句里聲明，或者用關(guān)鍵字NATURAL隱含地聲明。 連接條件判斷來自兩個(gè)源表中的那些行是"匹配"的，這些我們將在下面詳細(xì)解釋。
ON子句是最常見的連接條件的類型：它接收一個(gè)和WHERE子句相同的布爾表達(dá)式。 如果兩個(gè)分別來自T1和T2的行在ON表達(dá)式上運(yùn)算的結(jié)果為真，那么它們就算是匹配的行。
USING是個(gè)一個(gè)連接條件的縮寫語法：它接收一個(gè)用逗號(hào)分隔的字段名列表， 這些字段必須是連接表共有的并且其值必須相同。最后，JOIN USING會(huì)將每 一對(duì)相等的輸入字段輸出為一個(gè)字段，其后跟著所有其它字段。因此，USING (a， b， c)等效 于ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)，只不過 是如果使用了ON，那么在結(jié)果里a，b和c字段都會(huì)有兩個(gè)， 而用USING的時(shí)候就只會(huì)有一個(gè)。(如果SELECT *被用的話他們會(huì)首先出現(xiàn))。
最后，NATURAL是USING的一種縮寫形式：它自動(dòng)形成一個(gè)由兩個(gè)表中 同名的字段組成的USING列表(同名字段只出現(xiàn)一次)。
條件連接可能的類型是：
- INNER JOIN
內(nèi)連接。對(duì)于T1中的每一行R1，如果能在T2中找到一個(gè)或多個(gè)滿 足連接條件的行，那么這些滿足條件的每一行都在連接表中生成一行。
- LEFT OUTER JOIN
左外連接。首先執(zhí)行一次內(nèi)連接。然后為每一個(gè)T1中無法在T2中找 到匹配的行生成一行，該行中對(duì)應(yīng)T2的列用NULL補(bǔ)齊。因此，生成的連接表里無條件地包含來自T1里的每一行至少一個(gè)副本。
- RIGHT OUTER JOIN
右外連接。首先執(zhí)行一次內(nèi)連接。然后為每一個(gè)T2中無法在T1中找到匹配的行生成一行，該行中對(duì)應(yīng)T1的列用NULL補(bǔ)齊。因此， 生成的連接表里無條件地包含來自T2里的每一行至少一個(gè)副本。
- FULL OUTER JOIN
全連接。首先執(zhí)行一次內(nèi)連接。然后為每一個(gè)T1與T2中找不到匹配的 行生成一行，該行中無法匹配的列用NULL補(bǔ)齊。因此，生成的連接表里無條 件地包含T1和T2里的每一行至少一個(gè)副本。
如果T1和T2之一或全部是可以連接的表，那么所有類型的連接都可以串連或嵌套在一起。 你可以在JOIN子句周圍使用圓括弧來控制連接順序， 如果沒有圓括弧，那么JOIN子句從左向右嵌套。
為了解釋這些問題，假設(shè)我們有一個(gè)表t1
num | name -----+------ 1 | a 2 | b 3 | c
和 t2:
num | value -----+------- 1 | xxx 3 | yyy 5 | zzz
然后我們用不同的連接方式可以獲得各種結(jié)果：
=> 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聲明的連接條件也可以包含與連接不直接相關(guān)的條件。這種功能 可能對(duì)某些查詢很有用，但是需要我們仔細(xì)想清楚。比如：
=> 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)
請(qǐng)注意，放置在WHERE條款的限制 產(chǎn)生不同的結(jié)果：
=> 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)
這是因?yàn)樵?tt class="LITERAL">ON子句連接之前處理，而WHERE子句在連接之后處理。
7.2.1.2. 表和列別名
你可以給表或復(fù)雜的表引用起一個(gè)臨時(shí)的表別名，以便被其余的查詢引用，稱為tablealias.
要?jiǎng)?chuàng)建一個(gè)表別名，可以這樣：
FROM table_reference AS alias
或
FROM table_reference alias
AS關(guān)鍵字沒有特別的含義。alias可以是任意標(biāo)識(shí)符。
表別名的典型應(yīng)用是給長表名賦予比較短的標(biāo)識(shí)，好讓連接子句更易讀一些。比如：
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
別名變成了與當(dāng)前查詢有關(guān)的表的名字，之后就不允許再用最初的名字了。因此這是無效的
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
表別名主要是為了方便標(biāo)記，但對(duì)于自連接卻是必須的。比如：
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
另外，要引用子查詢的結(jié)果也必須使用別名(參見節(jié)Section 7.2.1.3)。
圓括弧用于解決歧義。下面的第一個(gè)語句把別名b賦予第二個(gè)my_table表； 而第二個(gè)語句則把別名b賦予了連接的結(jié)果。
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 [， ...]] )
如果聲明的字段別名比表里實(shí)際的字段少，那么后面的字段就沒有別名。這個(gè)語法對(duì)于自連接或子查詢特別有用。
如果用這些形式中的任何一種給一個(gè)JOIN子句的輸出結(jié)果附加一個(gè)別名， 那么該別名就在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的外面是看不到的。
7.2.1.3. 子查詢
子查詢的結(jié)果(派生表)必須包圍在圓括弧里并 且必須賦予一個(gè)別名(參閱Section 7.2.1.2)。比如：
FROM (SELECT * FROM table1) AS alias_name
這個(gè)例子等效于FROM table1 AS alias_name。更有趣的例子是在子 查詢里面有分組或聚集的時(shí)候，這個(gè)時(shí)候子查詢不能歸納成一個(gè)簡單的連接。
子查詢也可以是一個(gè)VALUES列表：
FROM (VALUES ('anne'， 'smith')， ('bob'， 'jones')， ('joe'， 'blow'))
AS names(first， last)
這種情況同樣也必須要取一個(gè)別名。還可以為VALUES列表中的字段取別名， 并且被認(rèn)為是一個(gè)好習(xí)慣。更多信息參見Section 7.7。
7.2.1.4. 表函數(shù)
表函數(shù)是那些生成一個(gè)行集合的函數(shù)，這個(gè)集合可以是由基本數(shù)據(jù)類型(標(biāo)量類型)組成， 也可以是由復(fù)合數(shù)據(jù)類型(表的行)組成。他們的用法類似一個(gè)表、視圖或FROM子 句里的子查詢。表函數(shù)返回的字段可以像一個(gè)表、視圖、或者子查詢字段那 樣包含在SELECT，JOIN或者WHERE子句里。
如果表函數(shù)返回基本數(shù)據(jù)類型，那么單一結(jié)果字段的名字與函數(shù)名相同。如果表函數(shù)返回復(fù)合 數(shù)據(jù)類型，那么多個(gè)結(jié)果字段的名字和該類型的每個(gè)屬性的名字相同。
可以在FROM子句中為表函數(shù)取一個(gè)別名，也可以不取別名。如果一個(gè) 函數(shù)在FROM子句中沒有別名，那么將使用函數(shù)名作為結(jié)果表的名字。
一些例子：
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;
有時(shí)侯，把一個(gè)函數(shù)定義成根據(jù)不同的調(diào)用方法可以返回不同的字段是很有用的。 為了支持這個(gè)，表函數(shù)可以聲明為返回偽類型record。如果在查詢里使用這樣 的函數(shù)，那么我們必須在查詢中聲明預(yù)期的行結(jié)構(gòu)，這樣系統(tǒng)才知道如何分析和 規(guī)劃該查詢。讓我們看看下面的例子：
SELECT *
FROM dblink('dbname=mydb'， 'SELECT proname， prosrc FROM pg_proc')
AS t1(proname name， prosrc text)
WHERE proname LIKE 'bytea%';
dblink函數(shù)執(zhí)行一個(gè)遠(yuǎn)程的查詢(參閱contrib/dblink)。它聲明 為返回record，因?yàn)樗赡軙?huì)被用于任何類型的查詢。實(shí)際的字段集必 須在調(diào)用它的查詢中聲明，這樣分析器才知道類似*這樣的東西應(yīng)該擴(kuò)展成什么樣子。
7.2.2. WHERE子句
WHERE Clause子句的語法是
WHERE search_condition
這里的search_condition是一個(gè)返 回類型為boolean的值表達(dá)式(參閱Section 4.2)。
在完成對(duì)FROM子句的處理之后，生成的每一行都會(huì)按照search_condition進(jìn)行檢查。 如果結(jié)果是真，那么該行保留在輸出表中，否則(結(jié)果是假或NULL)就把它拋棄。 搜索條件通常至少要引用一列在FROM子句里生成的列，這不是必須的， 但如果不這樣的話，WHERE子句就沒什么意義了。
Note: 內(nèi)連接的連接條件既可以寫在WHERE子句里也可以寫在JOIN子句里。比如，下面的表表達(dá)式是等效的：
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你想用哪個(gè)只是風(fēng)格問題。FROM子句里的JOIN語法可能不那么容易移植到其它產(chǎn)品中。即使它是在SQL標(biāo)準(zhǔn) 對(duì)于外連接而言，我們?cè)谌魏吻闆r下都沒有選擇：連接條件必須在FROM子句中完成。 外連接的ON或USING子句不等于WHERE條件，因?yàn)樗袛嘧罱K 結(jié)果中行的增(那些不匹配的輸入行)和刪。
這里是一些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中刪除。請(qǐng)注意我們把標(biāo)量子查詢當(dāng)做一個(gè)值表達(dá)式來用。 就像其它查詢一樣，子查詢里也可以使用復(fù)雜的表表達(dá)式。同時(shí)還請(qǐng)注意fdt是 如何引用子查詢的。把c1修飾成fdt.c1只有在c1是該子查詢生成的列名字時(shí)才是必須的， 但修飾列名字可以增加語句的準(zhǔn)確性(即使有時(shí)不是必須的)。這個(gè)例子就演示了字段名字 范圍如何從外層查詢擴(kuò)展到它的內(nèi)層查詢。
7.2.3. GROUP BY和HAVING子句
在通過了WHERE過濾器之后，生成的輸出表可以繼續(xù)用GROUP BY子句進(jìn)行 分組，然后用HAVING子句選取一些分組行
SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [， grouping_column_reference]...
GROUP BY Clause子句用于把那些所有列出的grouping_column_reference值都相 同的行聚集在一起，縮減為一行，這樣就可以刪除輸出里的重復(fù)和/或計(jì)算應(yīng)用 于這些組的聚集。這些字段的列出順序無關(guān)緊要。比如：
=> 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)
在第二個(gè)查詢里，我們不能寫成SELECT * FROM test1 GROUP BY x，因?yàn)樽侄?tt class="LITERAL">y里 沒有哪個(gè)值可以和每個(gè)組關(guān)聯(lián)起來。被分組的字段可以在選擇列表中引 用是因?yàn)樗鼈兠總€(gè)組都有單一的數(shù)值。
如果一個(gè)表被分了組，不在GROUP BY列中除了在總表達(dá)式不能被引用，那么就只能引用聚集表達(dá)式中的字段和分組中的字段。一個(gè)帶聚集表達(dá)式的例子是：
=> SELECT x， sum(y) FROM test1 GROUP BY x; x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)
這里的sum是一個(gè)聚集函數(shù)，它在組上計(jì)算總和。 有關(guān)可用的聚集函數(shù)的更多信息可以在節(jié)Section 9.18中找到。
Tip: 沒有有效的聚合表達(dá)式分組可以計(jì)算一列中不同值的設(shè)置。 這個(gè)可以通過DISTINCT子句來實(shí)現(xiàn)(參考Section 7.3.3).
這里是另外一個(gè)例子：它計(jì)算每種產(chǎn)品的總銷售額(而不是所有產(chǎn)品的總銷售額)。
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;
在這個(gè)例子里，字段product_id，p.name和p.price必須在GROUP BY子句里， 因?yàn)樗鼈兌荚诓樵冞x擇列表里被引用了。根據(jù)產(chǎn)品表具體設(shè)置的不同， 名字和價(jià)格可能和產(chǎn)品ID完全無關(guān)，因此理論上額外的分組可能是不必要的， 但是這些尚未實(shí)現(xiàn)。s.units字段不必在GROUP BY列表里，因?yàn)樗皇窃谝粋€(gè)聚集表達(dá)式(sum(...))里使用，它代表一組產(chǎn)品的銷售總額。 對(duì)于每種產(chǎn)品，這個(gè)查詢都返回一個(gè)該產(chǎn)品的總銷售額。
在嚴(yán)格的SQL里，GROUP BY只能對(duì)源表的列進(jìn)行分組，但PostgreSQL把這 個(gè)擴(kuò)展為既允許GROUP BY對(duì)選擇列表中的字段進(jìn)行分組，也允許對(duì)值表達(dá)式進(jìn)行分組，而不僅僅是簡單的字段。
如果一個(gè)表已經(jīng)用GROUP BY子句分了組，然后你又只對(duì)其中的某些組感興趣， 那么就可以用HAVING子句篩選分組。必須像WHERE子句，從結(jié)果中消除組，語法是
SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression
在HAVING子句中的表達(dá)式可以引用分組的表達(dá)式和未分組的表達(dá)式(后者必須涉及一個(gè)聚集函數(shù))。
例子:
=> 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)
然后是一個(gè)更現(xiàn)實(shí)的例子：
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子句在尚未分組之前根據(jù)s.date字段選擇數(shù)據(jù)行 (表達(dá)式只是對(duì)那些最近四周發(fā)生的銷售為真)。而HAVING子句在分組之后 選擇那些銷售總額超過5000的組。請(qǐng)注意聚集表達(dá)式不需要在查詢中的所有地方都一樣。
如果一個(gè)查詢調(diào)用了聚合函數(shù)，但沒有GROUP BY子句，分組仍然發(fā)生： 結(jié)果是單一組行（或者如果單一行被HAVING所淘汰，那么也許沒有行）。 同樣，它包含一個(gè)HAVING子句，甚至沒有任何聚合函數(shù)的調(diào)用或GROUP BY子句。
7.2.4. 窗口函數(shù)處理
如果查詢包含窗口函數(shù)(參考Section 3.5，Section 9.19和Section 4.2.8)， 這些函數(shù)在執(zhí)行了分組、聚合和HAVING過濾之后被評(píng)估。 也就是說，如果查詢使用任何的聚合、GROUP BY或HAVING，那么 由窗口函數(shù)發(fā)現(xiàn)的行是該組行而不是從FROM/WHERE得到的原始表行。
當(dāng)多個(gè)窗口函數(shù)被使用的時(shí)候，所有的窗口函數(shù)依照語法地等效于在它們的窗口定義被單一數(shù)據(jù) 所評(píng)估的PARTITION BY和ORDER BY子句中。 因此它們將看到同樣的排序，即使ORDER BY不唯一確定一個(gè)排序。 然而，不確保所做出的關(guān)于評(píng)價(jià)的功能有不同的PARTITION BY或ORDER BY 規(guī)范。（在這種情況下，一個(gè)排序步驟通常需要在窗口函數(shù)評(píng)價(jià)和排序不被保證保存看似跟ORDER BY等效的行命令。）
目前，窗口函數(shù)總是需要分類數(shù)據(jù)，所以查詢輸出將按照一個(gè)或另一個(gè)窗口函數(shù)PARTITION BY/ORDER BY子句。它不是說依賴于此。 如果你想要確保結(jié)果是按特定的方式分類那么使用顯式的頂級(jí)ORDER BY子句。