Postgresql表表达式

表表达式计算一个表。 该表表达式包含一个FROM子句，该子句可以根据需要选用WHERE，GROUP BY， 和HAVING 子句。大部分的表表达式只是指向磁盘上的一个表，一个所谓的基本表，但是我们可以用更复杂的表表达式以各种方法修改或组合基本表。

　　　　表表达式里的WHERE，GROUP BY，和 HAVING 子句声明一系列对源自 FROM 子句的表的转换操作。所有这些转换最后生成一个虚拟表，提供传递给选择列表计算查询输出行的数据行。

　　　FROM 子句

    FROM 子句 从一个用逗号分隔的表引用列表中的一个或更多个其它表中生成一个表。

    FROM table_reference [, table_reference [, ...]]
表引用可以是一个表名字（可能有模式修饰）或者是一个生成的表， 比如子查询，一个表连接，或者这些东西的复杂组合。如果在FROM子句中列出了多于一个表， 那么它们被 cross join （见下文）形成一个派生表，该表可以进行 WHERE，GROUP BY 和 HAVING 子句的转换处理，并最后生成所有表表达式的结果。

    如果一个表引用是一个简单的表名字并且它是表继承级别中的超级表， 那么该表的行包括所有它的后代子表的行，除非你在该表名字前面加ONLY关键字。 这样的话，这个引用就只生成出现在命名表中的列 — 任何在子表中追加的列都会被忽略。

    连接表

    一个连接表是根据特定的连接类型的规则从两个其它表（真实表或生成表）中派生的表。 我们支持内连接，外连接和交叉连接类型。

    连接类型

    交叉连接

    1 CROSS JOIN T2
    对每个从 T1 和 T2 来的行的组合， 生成的表将包含这样一行：它包含所有 T1 里面的字段后面跟着所有 T2 里面的字段。 如果两表分别有 N 和 M 行，连接成的表将有 N * M 行。

    FROM T1 CROSS JOIN T2 等效于 FROM T1, T2。 它还等效于 FROM T1 INNER JOIN T2 ON TRUE（见下文）。
 
    条件连接(join)

    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 对所有连接（join） 类型都是可选的。 INNER 是缺省；LEFT，RIGHT，和 FULL 隐含外连接。

    连接条件在ON或USING子句里声明， 或者用关键字NATURAL隐含地声明。连接条件判断来自两个源表中的那些行是"匹配"的，这些我们将在下面详细解释。

    ON子句是最常见的连接条件的类型：它接收一个和WHERE子句里用的一样的布尔值表达式。 如果两个分别来自T1和T2的行在ON表达式上运算的结果为真，那么它们就算是匹配的行。

    USING是个缩写的概念：它接收一个用逗号分隔的字段名字列表， 这些字段必须是连接表共有的，最终形成一个连接条件，表示这些字段对必须相同。 最后，JOIN USING 的输出会为每一对相等的输入字段输出一个字段，后面跟着来自各个表的所有其它字段。 因此，USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON，那么在结果里 a，b，和 c字段每个都会有两个， 而用USING的时候每个字段就只会有一个。

    最后，NATURAL 是 USING 的缩写形式：它形成一个 USING 列表， 该列表由那些在两个表里都出现了的字段名字组成。和USING一样，这些字段只在输出表里出现一次。

   条件连接可能的类型是：

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 的列用空值补齐。

    如果 T1 和 T2 有一个或者都是可以连接的表， 那么所有类型的连接都可以串在一起或嵌套在一起。 你可以在JOIN子句周围使用圆括弧来控制连接顺序， 如果没有圆括弧，那么JOIN子句是从左向右嵌套的。

    为了解释这些问题，假设我们有一个表 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 子句 的语法是

    WHERE search_condition
    这里的 search_condition 是任意返回一个类型为boolean的值表达式 （参阅 Section 4.2）。

    在完成对FROM子句的处理之后，生成的每一行都会对搜索条件进行检查。 如果该条件的结果是真，那么该行输出到输出表中，否则（也就是说， 如果结果是假或空）就把它抛弃。搜索条件通常至少要引用一些在FROM子句里生成的列； 这不是必须的，但如果不是这样的话，那么WHERE子句就没什么用了。

    注意: 内连接的连接条件既可以写在 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语法可能不那么容易移植到其它产品中。 对于外部连接（outer join）而言，我们在任何情况下都没有选择：它们必须在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修饰成fdt.c1只有在c1是该子查询生成的列的名字时才是必须的。 但修饰列名字可以增加语句的准确性，即使有时候不是必须的。 这个例子就演示了字段名字范围如何从外层查询扩展到它的内层查询.

   在通过了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 里没有哪个值可以和每个组相关联起来。 被分组的字段可以在选择列表中引用是因为它们每个组都有单一的数值。

    通常，如果一个表被分了组，那么没有在分组中引用的字段都不能引用，除了在聚集表达式中以外。 一个带聚集表达式的例子是：

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)
    这里的 sum 是一个聚集函数，它在整个组上计算一个数值。

    提示: 没有聚集表达式的分组实际上计算了一个字段中独立数值的集合。 我们也可以用 DISTINCT 子句实现这里是另外一个例子：它计算每种产品的总销售额。（而不是所有产品的总销售额）。

SELECT pid, p.name, (sum(s.units) * p.price) AS sales
  FROM products p LEFT JOIN sales s USING ( pid )
  GROUP BY pid, p.name, p.price;

    在这个例子里，字段pid， p.name，和p.price必须在GROUP BY子句里， 因为它们都在查询选择列表里被引用到。 （根据产品表具体的设置的不同，名字和价格可能和产品 ID 完全无关，因此理论上额外的分组可能是不必的， 但是这些尚未实现。） 字段s.units不必在GROUP BY列表里，因为它只是在一个聚集表达式（sum(...)） 里使用，它代表一组产品的销售额。对于每种产品，这个查询都返回一个该产品的所有销售额的总和。

    在严格的 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 的组的行。 请注意聚集函数不需要在查询中的所有地方都一样。

原文转自：http://www.ltesting.net