7.2. 表表达式
表表达式计算一个表，它包含一个FROM子句， 该子句可以根据需要选用WHERE, GROUP BY,HAVING 子句。大部分表表达式只是指向磁盘上的一个所谓的基本表， 但是我们可以用更复杂的表达式以各种方法修改或组合基本表。
表表达式里的WHERE, GROUP BY, HAVING 子句声明一系列对源自FROM子句的表的转换操作。 所有这些转换最后生成一个虚拟表，传递给选择列表计算输出行。
FROM 子句 从一个逗号分隔的表引用列表中生成一个虚拟表。
FROM table_reference [, table_reference [, ...]]
表引用可以是一个表名字(可能有模式修饰)或者是一个生成的表， 比如子查询、JOIN构造、或这些东西的复杂组合。 如果在FROM子句中列出了多于一个表引用， 那么它们交叉连接(也就是形成了它们的行的笛卡尔积，见下文)。 FROM列表的结果是一个中间的虚拟表， 该表可以进行WHERE, GROUP BY, HAVING子句的转换处理，并最后生成表表达式的结果。
如果一个表引用是一个简单的父表的名字，那么将包括其所有后代子表的行， 除非你在该表名字前面加ONLY关键字(这样任何子表都会被忽略)。
除了在表名字前面加ONLY，你可以在表名字后面写* 明确指定包括后代表。写*不是必须的，因为这个行为是默认的 （除非你已经改变了sql_inheritance配置选项里面的设置）。 然而写*可能对于强调搜索额外的表是有用的。
7.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。
注意: 当多于两个表时，不再正确的支持后面的等效，因为JOIN的绑定比逗号更紧。 例如FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition 和FROM T1, T2 INNER JOIN T3ON 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 中找到一个或多个满足连接条件的行， 那么这些满足条件的每一行都在连接表中生成一行。
LEFT OUTER JOIN
左外连接。首先执行一次内连接。然后为每一个 T1 中无法在 T2 中找到匹配的行生成一行， 该行中对应 T2 的列用 NULL 补齐。因此，生成的连接表里总是包含来自 T1 里的每一行至少一个副本。
RIGHT OUTER JOIN
右外连接。首先执行一次内连接。然后为每一个 T2 中无法在 T1 中找到匹配的行生成一行， 该行中对应 T1 的列用 NULL 补齐。因此，生成的连接表里总是包含来自 T2 里的每一行至少一个副本。
FULL OUTER JOIN
全连接。首先执行一次内连接。然后为每一个 T1 与 T2 中找不到匹配的行生成一行， 该行中无法匹配的列用 NULL 补齐。因此，生成的连接表里无条件地包含 T1 和 T2 里的每一行至少一个副本。
ON子句是最常见的连接条件的类型：它接收一个和WHERE 子句相同的布尔表达式。如果两个分别来自T1和T2 的行在ON表达式上运算的结果为真，那么它们就算是匹配的行。
USING子句是一种速记法，允许你在join两边都使用相同的连接字段名时获取便利。 它接受一个逗号分隔的共享字段名的列表，并生成一个包括每个字段相等比较的连接条件。 例如，用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列表(同名字段只出现一次)。 如果没有相同的字段名，NATURAL的行为会像CROSS JOIN。
注意: USING在连接关系的字段修改中是合理安全的， 因为只组合了列出的字段。认为NATURAL更危险一些， 因为两个关系的任何模式改变会导致出现新的匹配字段名， 这将导致连接也组合该新字段。
用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子句中时是后于连接处理的。 这对内连接来说没什么问题，但是对外连接来说问题就大了。
7.2.1.3. 子查询
子查询的结果(派生表)必须包围在圆括弧里并且必须赋予一个别名(参阅 第 7.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列表中的字段取别名， 并且被认为是一个好习惯。更多信息参见第 7.7 节。
7.2.1.4. 表函数
表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型(标量类型)组成， 也可以是由复合数据类型(表的行)组成。他们的用法类似一个表、视图、或 FROM 子句里的子查询。表函数返回的字段可以像一个表、视图、或者子查询的字段那样包含在SELECT,JOIN,WHERE子句里。
使用ROWS FROM语法也可以组合表函数，结果返回并行的字段； 这种情况下的结果行数是较大函数的结果，较小的结果填充null值匹配。
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(第 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子句， 那么column_definition 列表可以替换跟随ROWS FROM()的字段别名列表。
考虑这个例子：
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
dblink函数（dblink模块的一部分） 执行一个远程的查询。它声明为返回record，因为它可能会被用于任何类型的查询。 实际的字段集必须在调用它的查询中声明，这样分析器才知道类似* 这样的东西应该扩展成什么样子
7.2.1.5. LATERAL 子查询（这个我也不是理解！！！）
FROM子句中出现的子查询可以放在关键字LATERAL之前。 这样就允许它们引用通过前置FROM条目提供的字段。（如果没有LATERAL， 那么每个子查询都被认为是独立的并且不能交叉引用任何其他的FROM条目。）
FROM中出现的表函数也可以出现在关键字LATERAL之前， 但是对于函数来说，这个关键字是可选的；函数的参数在任何情况下都可以包含通过前置FROM 条目提供的字段。
LATERAL条目可以出现在FROM列表的顶级，或者在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当交叉引用字段对于加入的计算行是重要的时是主要有用的。 一个常见的应用是为一个set-returning函数提供一个参数值。例如，假设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()返回一个制造商制造的产品名字， 但是一些在我们表中的制造商当前没有生产任何产品，我们可以像下面这样找出这些制造商：
.2.2. WHERE子句
WHERE 子句子句的语法是：
WHERE search_condition
这里的search_condition是一个返回类型为 boolean 的值表达式(参阅第 4.2 节)。
在完成对FROM子句的处理之后，生成的每一行都会按照搜索条件进行检查。 如果结果是真，那么该行保留在输出表中，否则(也就是结果是假或NULL)就把它抛弃。 搜索条件通常至少要引用一列在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 语法可能不那么容易移植到其它产品中。即使它是在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修饰成fdt.c1 只有在c1是该子查询生成的列名字时才是必须的， 但修饰列名字可以增加语句的准确性(即使有时不是必须的)。 这个例子就演示了字段名字范围如何从外层查询扩展到它的内层查询。
7.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是一个聚合函数，它在组上计算总和。 有关可用的聚合函数的更多信息可以在第 9.20 节中找到。
提示: 没有有效的聚合表达式分组可以计算一列中不同值的设置。 这个可以通过DISTINCT子句来实现(参考第 7.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子句。
7.2.4. 窗口函数处理
如果查询包含窗口函数(参考第 3.5 节，第 9.21 节 和第 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子句。
7.3. 选择列表
7.3.3. DISTINCT
在处理完选择列表之后，生成的表可以删除重复行。直接在SELECT 后面写上DISTINCT关键字即可：
SELECT DISTINCT select_list ...
如果不用DISTINCT你可以用ALL声明保留所有行的缺省行为。
显然，如果两行里至少有一个字段值不同，那么我们认为这两行是独立的。 NULL 在这里被认为是相同的。
另外，我们还可以用表达式来判断什么样的行可以认为是独立的：
SELECT DISTINCT ON (expression [, expression ...]) select_list ...
这里的expression是一个值表达式， 它为每一行计算。如果一组行计算出的该表达式的值都相同，那么就认为这些行是重复的， 并只输出第一行。请注意这里的"第一行"是不可预料的， 除非你在足够多的字段上对该查询进行了排序，保证到达DISTINCT 过滤器时行的顺序是唯一的(DISTINCT ON将在ORDER BY排序之后处理)。
DISTINCT ON子句不是 SQL 标准的一部分，有时候被认为是一个糟糕的风格， 因为它的结果是不可判定的。如果用有可选的GROUP BY和在FROM 中的子查询可以达到目的，那么我们可以避免使用这个构造，但是通常它是更方便的方法。
7.4. 组合查询
可以对两个查询的结果进行集合操作(并、交、差)。语法是：
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， 否则所有重复行都被删除。
为了能够计算两个查询的并、交、差，这两个查询必须是"并集兼容的"， 也就是它们都返回同样数量的列，并且对应的列有兼容的数据类型， 就像第 10.5 节里描述的那样。
.5. 行排序
在查询生成输出表之后，也就是在处理完选择列表之后，你还可以对输出表进行排序。 如果没有排序，那么行将以不可预测的顺序返回(实际顺序将取决于扫描和连接规划类型和在磁盘上的顺序， 但是肯定不能依赖这些东西)。确定的顺序只能在明确地使用了排序步骤之后才能保证。
ORDER BY子句用于声明排序顺序:
SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]
sort_expression 是任何可用于选择列表的表达式，例如：
SELECT a, b FROM table1 ORDER BY a + b, c;
如果指定了多个排序表达式，那么仅在前面的表达式排序相等的情况下才使用后面的表达式做进一步排序。 每个表达式都可以跟一个可选的ASC(升序，默认) 或DESC(降序)以设置排序方向。 升序先输出小的数值，这里的"小"是以<操作符的角度定义的。 类似的是，降序是以>操作符来判断的。 [1]
NULLS FIRST和NULLS LAST选项可以决定在排序操作中在 non-null 值之前还是之后。默认情况下，空值大于任何非空值；也就是说，DESC 排序默认是NULLS FIRST，否则为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; -- 错误的
这样的限制主要是为了避免歧义。另外，如果某个排序表达式能够同时匹配输出字段名和表表达式中的字段名， 也会导致歧义(此时使用输出字段名)。当然，这种情况仅在你使用了AS 重命名输出字段并且恰好与其它表的字段同名的时候才会发生。
ORDER BY可以应用于UNION, INTERSECT,EXCEPT 组合的计算结果，不过在这种情况下，只允许按照字段的名字或编号进行排序，而不允许按照表达式进行排序。
备注
| [1] | 事实上，PostgreSQL使用默认的B-tree操作符类 为表达式的数据类型确定ASC和DESC排序顺序。 一般来说，数据类型将被转换为适合于 <和 >操作符进行排序。但是对于用户自定义的数据类型可以不必如此。 |
.6. LIMIT和OFFSET
LIMIT和OFFSET子句允许你只取出查询结果中的一部分数据行：
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]
如果给出了一个 LIMIT 计数，那么将返回不超过该数字的行(也可能更少些， 因为可能查询本身生成的总行数就比较少)。LIMIT ALL和省略LIMIT子句是一样的。
OFFSET指明在开始返回行之前忽略多少行。OFFSET 0和省略 OFFSET子句是一样的，LIMIT NULL和省略LIMIT子句 是一样的。如果OFFSET和LIMIT都出现了，那么在计算返回的LIMIT 之前先忽略OFFSET指定的行数。
使用LIMIT的同时使用ORDER BY子句把结果行约束成一个唯一的顺序是一个好主意。 否则你就会得到一个不可预料的子集。你要的可能是第十到二十行，但以什么顺序的十到二十？ 除非你声明了ORDER BY，否则顺序是未知的。
查询优化器在生成查询规划的时候会考虑LIMIT， 因此如果你给LIMIT和OFFSET的值不同， 那么你很可能得到不同的规划(产生不同的行顺序)。因此， 使用不同的LIMIT/OFFSET值选择不同的子集将生成不一致的结果， 除非你用ORDER BY强制一个可预料的顺序。这可不是Bug， 而是一个很自然的结果，因为 SQL 没有许诺把查询的结果按照任何特定的顺序发出， 除非用了ORDER BY来约束顺序。
OFFSET子句忽略的行仍然需要在服务器内部计算； 因此，一个很大的OFFSET可能还是不够有效率。
7.7. VALUES列表
可以在查询中使用由VALUES生成的"常数表"， 而无需在磁盘上实际创建这个表。语法如下：
VALUES ( expression [, ...] ) [, ...]
每个括号中的表达式列表生成表中的一行。每个列表中的项数(也就是字段数)必须相等， 并且对应的数据类型必须兼容。最终表中每个字段的数据类型将使用与UNION (参见第 10.5 节)相同的规则确定。
例如:
VALUES (1, 'one'), (2, 'two'), (3, 'three');
将得到 2 列 3 行的表。并且与下面的语句等价：
SELECT 1 AS column1, 'one' AS column2 UNION ALL SELECT 2, 'two' UNION ALL SELECT 3, 'three';
PostgreSQL默认将VALUES 所得到的表中各字段分别命名为column1, column2等等。 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命令的数据源或者子查询。
更多信息参见VALUES。
.8. WITH 查询 (通用表表达式)
WITH提供了一种在更大的查询中编写辅助语句的方式。 这个通常称为通用表表达式或CTEs的辅助语句可以认为是定义只存在于一个查询中的临时表。 每个WITH子句中的辅助语句可以是一个SELECT,INSERT, UPDATE 或 DELETE；并且WITH子句本身附加到的初级语句可以是一个SELECT, INSERT, UPDATE或DELETE。
7.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查询的一般形式总是一个non-recursive term， 然后UNION（或者UNION ALL），然后一个recursive term， 其中只有递归的术语可以包含一个对查询自己输出的引用。这样一个查询像下面那样执行：
递归查询评估
-
评估非递归的术语。使用UNION（而不是UNION ALL）去除重复的行。 包括在递归查询结果中所有剩余的行，并将它们放入临时的工作表。
-
只要工作表不为空，那么将重复这些步骤：
-
评估递归术语，为递归自我参照替换当前工作表内容。用UNION(并不是UNION ALL)， 去除重复的行和与以前结果行重复的行。包括所有在递归查询结果中剩余的行， 并将它们放入一个临时的中间表。
-
用中间表的内容替换工作表的内容，然后清空中间表。
-
注意: 严格的说，该过程是迭代而不是递归，但是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关系包含循环那么这个查询将会循环。 因为我们需要一个"深度"输出，仅改变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;
除了防止循环，该数组值通常是有用的，在它的右边作为代表用来得到任何特定行的"路径"。
在一般情况下，需要检测多个字段来识别一个循环时使用一个行数组。例如， 如果我们需要对比字段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;
提示: 在常见的情况下，当只需要检查一个字段来识别循环的时候忽略ROW()语法。 这允许使用一个简单的数组而不是一个复杂类型的数组，增加查询的效率。
提示: 递归查询评估算法产生以广度优先搜索顺序的输出。 您可以按照深度优先查询排序通过外部查询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 查询将如上评估，没有行限制的父查询可能丢失。（但是，正如上面所说， 如果查询参考只需要数量有限的行，评估可能会很早终止。）
上面的例子只显示了WITH在SELECT中的使用， 但是它也可以用同样的方式附加到INSERT, UPDATE或 DELETE。 在每种情况下它都有效的提供可以在主要的命令中引用的临时表。
7.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);
这个查询将删除一个产品所有直接或非直接的subparts。
WITH中的数据修改语句被直接执行一次，并且总是完成， 独立的主查询读取所有（或者实际上是任意）它们的输出。 注意，这和在WITH中SELECT的规则不同： 就像前一节规定的那样，SELECT的执行直到首级查询需要它的输出时才实施。
WITH中的子语句之间和与主查询之间兼容的执行。因此， 当在WITH中使用数据修改语句时，其他的指定的更新实际上是不可预知发生的。 所有的语句都在相同的快照中执行（见第 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子语句。 这样一个语句的影响将是不可预测的。
目前，任何作为在WITH中的数据修改语句目标的表，不必有扩展到多个语句的条件规则、 ALSO规则和INSTEAD规则。
324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
