标签：PostgreSQL 查询 详解 子句 JOIN WHERE id SELECT

PostgreSQL的查询详解

1. 表表达式

表表达式计算一个表。该表表达式包含一个 FROM 子句，该子句后面可以根据需要选用 WHERE、GROUP BY 和 HAVING 子句。最简单的表表达式只是引用磁盘上的一个表，一个所谓的基本表，但是我们可以用更复杂的表表达式以多种方法修改或组合基本表。

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

1.1 FROM 子句

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

FROM table_reference [, table_reference [, …]]

表引用可以是一个表名字（可能有模式限定）或者是一个生成的表， 例如子查询、一个 JOIN 结构或者这些东西的复杂组合。如果在 FROM 子句中引用了多于一个表， 那么它们被交叉连接（即构造它们的行的笛卡尔积，见下文）。FROM 列表的结果是一个中间的虚拟表，该表可以进行由 WHERE、GROUP BY 和 HAVING 子句指定的转换，并最后生成全局的表表达式结果。

如果一个表引用是一个简单的表名字并且它是表继承层次中的父表，那么该表引用将产生该表和它的后代表中的行，除非你在该表名字前面放上 ONLY 关键字。但是，这种引用只会产生出现在该命名表中的列 — 在子表中增加的列都会被忽略。

除了在表名前写 ONLY，你可以在表名后面写上 * 来显式地指定要包括所有的后代表。没有实际的理由再继续使用这种语法，因为搜索后代表现在总是默认行为。不过，为了保持与旧版本的兼容性，仍然支持这种语法。

1.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 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 JOIN 的行为将和 JOIN ... ON TRUE 一样产生交叉集连接。

  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 子句中的一个约束是在连接之后被处理。这对内连接没有关系，但是对于外连接会带来麻烦。

1.1.2 表和列别名

你可以给一个表或复杂的表引用指定一个临时的名字，用于剩下的查询中引用那些派生的表。这被叫做表别名。

要创建一个表别名，我们可以写：

FROM table_reference AS alias

或者

FROM table_reference alias

AS 关键字是可选的。*alias* 可以是任意标识符。

表别名的典型应用是给长表名赋予比较短的标识符， 好让连接子句更易读。例如：

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;

此外，如果一个表引用是一个子查询，则必须要使用一个别名（见子查询）。

圆括弧用于解决歧义。在下面的例子中，第一个语句将把别名 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 外面是看不到的。

1.1.3 子查询

子查询指定了一个派生表，它必须被包围在圆括弧里并且*必须*被赋予一个表别名（参阅表和列别名）。例如：

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 列表中的列分配别名是可选的，但是选择这样做是一个好习惯。更多信息可参见VALUES 列表。

1.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（数组函数和操作符）并且使用 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;

有时侯，定义一个能够根据它们被调用方式返回不同列集合的表函数是很有用的。 为了支持这些，表函数可以被声明为返回没有 OUT 参数的伪类型 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，因为它可能会被用于任何类型的查询。 实际的列集必须在调用它的查询中指定，这样分析器才知道类似 * 这样的东西应该扩展成什么样子。

此示例使用 ROWS FROM：

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

  p  |  q  | s
-----+-----+---
  40 | foo | 1
 100 | bar | 2
     |     | 3

它将两个函数连接到一个 FROM 目标中。 json_to_recordset() 被指示返回两列，第一个 integer 和第二个 text。generate_series() 的结果直接使用。 ORDER BY 子句将列值排序为整数。

1.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;

1.2 WHERE 子句

WHERE子句的语法是：

WHERE search_condition

这里的 ***search_condition\*** 是任意返回一个 boolean 类型值的值表达式（参阅值表达式）。

在完成对 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 语法可能不那么容易移植到其它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。但限定列名字可以增加语句的清晰度，即使有时候不是必须的。这个例子展示了一个外层查询的列名范围如何扩展到它的内层查询。

1.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 是一个聚集函数，它在整个组上计算出一个单一值。有关可用的聚集函数的更多信息可以在聚集函数。

没有聚集表达式的分组实际上计算了一个列中可区分值的集合。我们也可以用 DISTINCT 子句实现（参阅DISTINCT）。

这里是另外一个例子：它计算每种产品的总销售额（而不是所有产品的总销售额）：

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 子句。

1.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 子句的聚集函数的情况一样。

对于分组列或表达式没有出现在其中的分组集的结果行，对分组列或表达式的引用会被空值所替代。要区分一个特定的输出行来自于哪个分组，请见表“分组操作”。

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)
)

当一次指定多个分组项时，最终的分组集合可能包含重复项。例如：

  GROUP BY ROLLUP (a, b), ROLLUP (a, c)

等同于

  GROUP BY GROUPING SETS (
      (a, b, c),
      (a, b),
      (a, b),
      (a, c),
      (a),
      (a),
      (a, c),
      (a),
      ()
  )

如果这些重复项是不希望出现的，可以在 GROUP BY 上直接使用 DISTINCT 子句来删除它们。因此：

  GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

等同于

  GROUP BY GROUPING SETS (
      (a, b, c),
      (a, b),
      (a, c),
      (a),
      ()
  )

这与使用 SELECT DISTINCT 不同，因为输出行仍然可能包含重复项。如果任何一个未分组的列包含 NULL，则无法区分其与分组该列时使用的 NULL。

在表达式中，结构 (a, b) 通常被识别为一个行构造器。在 GROUP BY 子句中，这不会在表达式的顶层应用，并且 (a, b) 会按照上面所说的被解析为一个表达式的列表。如果出于 某种原因你在分组表达式中*需要*一个行构造器，请使用 ROW(a, b)。

1.5 窗口函数处理

如果查询包含任何窗口函数（见 第 3.5 节、窗口函数和窗口函数调用），这些函数将在任何分组、聚集和 HAVING 过滤被执行之后被计算。也就是说如果查询使用了任何聚集、GROUP BY 或 HAVING，则窗口函数看到的行是分组行而不是来自于 FROM/WHERE 的原始表行。

当多个窗口函数被使用，所有在窗口定义中有句法上等效的 PARTITION BY 和 ORDER BY 子句的窗口函数被保证在数据上的同一趟扫描中计算。因此它们将会看到相同的排序顺序，即使 ORDER BY 没有唯一地决定一个顺序。但是，对于具有不同 PARTITION BY 或 ORDER BY 定义的函数的计算没有这种保证（在这种情况中，在多个窗口函数计算之间通常要求一个排序步骤，并且并不保证保留行的顺序，即使它的 ORDER BY 把这些行视为等效的）。

目前，窗口函数总是要求排序好的数据，并且这样查询的输出总是被根据窗口函数的 PARTITION BY/ORDER BY 子句的一个或者另一个排序。但是，我们不推荐依赖于此。如果你希望确保结果以特定的方式排序，请显式使用顶层的 ORDER BY 子句。

2. 选择列表

2.1 选择列表项

最简单的选择列表类型是 *，它发出表表达式生成的所有列。否则，一个选择列表是一个逗号分隔的值表达式的列表（和在值表达式里定义的一样）。 例如，它可能是一个列名的列表：

SELECT a, b, c FROM ...

列名字 a、b 和 c 要么是在 FROM 子句里引用的表中列的实际名字，要么是像表和列别名里解释的那样的别名。在选择列表里可用的名字空间和在 WHERE 子句里的一样， 除非你使用了分组，这时候它和 HAVING 子句一样。

如果超过一个表有同样的列名，那么你还必须给出表名字，如：

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

在使用多个表时，要求一个特定表的所有列也是有用的：

SELECT tbl1.*, tbl2.a FROM ...

更多有关 table_name.* 记号的内容请参考在查询中使用组合类型。

如果将任意值表达式用于选择列表，那么它在概念上向返回的表中增加了一个新的虚拟列。 值表达式为结果的每一行进行一次计算，对任何列引用替换行的值。 不过选择列表中的这个表达式并非一定要引用来自 FROM 子句中表表达式里面的列，例如它也可以是任意常量算术表达式。

2.2 列标签

选择列表中的项可以被赋予名字，用于进一步的处理。 例如为了在一个 ORDER BY 子句中使用或者为了客户端应用显示。例如：

SELECT a AS value, b + c AS sum FROM ...

如果没有使用 AS 指定输出列名，那么系统会分配一个缺省的列名。对于简单的列引用， 它是被引用列的名字。对于函数调用，它是函数的名字。对于复杂表达式，系统会生成一个通用的名字。

AS 关键词通常是可选的，但在某些情况下，如果想要的列名与PostgreSQL关键词相匹配，你必须写上 AS 或双引号的列名，以避免歧义。( 附录 C 显示了哪些关键词需要用 AS 作为列标签)。例如，FROM 就是这样的一个关键词，所以下面的语句是行不通的：

SELECT a from, b + c AS sum FROM ...

但是以下任意一种会起作用：

SELECT a AS from, b + c AS sum FROM ...
SELECT a "from", b + c AS sum FROM ...

为了防止未来可能的关键词增加，我们推荐总是写 AS 或者用双引号修饰输出列名以保证最大的安全性。

输出列的命名和在 FROM 子句里的命名是不一样的 （参阅表和列别名）。 它实际上允许你对同一个列命名两次，但是在选择列表中分配的名字是要传递下去的名字。

2.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 中的子查询，那么我们可以避免使用这个构造， 但是通常它是更方便的候选方法。

3. 组合查询(UNION, INTERSECT, EXCEPT)

两个查询的结果可以用集合操作并、交、差进行组合。语法是

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

query1 和 query2 都是可以使用以上所有特性的查询。

UNION 有效地把 query2 的结果附加到 query1 的结果上（不过我们不能保证这就是这些行实际被返回的顺序）。此外，它将删除结果中所有重复的行， 就象 DISTINCT 做的那样，除非你使用了 UNION ALL。

INTERSECT 返回那些同时存在于 query1 和 query2 的结果中的行，除非声明了 INTERSECT ALL，否则所有重复行都被消除。

EXCEPT 返回所有在 query1 的结果中但是不在 query2 的结果中的行（有时侯这叫做两个查询的差）。同样的，除非声明了 EXCEPT ALL，否则所有重复行都被消除。

为了计算两个查询的并、交、差，这两个查询必须是“并操作兼容的”，也就意味着它们都返回同样数量的列， 并且对应的列有兼容的数据类型，如UNION、CASE和相关结构中描述的那样。

集合操作可以组合使用，例如：

  query1 UNION query2 EXCEPT query3

等价于：

  (query1 UNION query2) EXCEPT query3

如下所示，您可以使用括号来控制计算顺序。如果没有括号，则 UNION 和 EXCEPT 从左到右进行关联，但 INTERSECT 的绑定比这两个运算符更紧密。因此

  query1 UNION query2 INTERSECT query3

意思是

  query1 UNION (query2 INTERSECT query3)

你也可以用括号包裹一个单独的 ***query\***。如果 ***query\*** 需要使用后续部分中讨论的任何子句（例如 LIMIT），那么这一点非常重要。没有括号，将会得到语法错误，否则子句将被解析为适用于集合操作的输出，而不是它的输入之一。例如，

  SELECT a FROM b UNION SELECT x FROM y LIMIT 10

是可以接受的，但它的意思是

  (SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

不是

  SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)

4. 行排序(ORDER BY)

在一个查询生成一个输出表之后（在处理完选择列表之后），还可以选择性地对它进行排序。如果没有选择排序，那么行将以未指定的顺序返回。这时候的实际顺序将取决于扫描和连接计划类型以及行在磁盘上的顺序，但是肯定不能依赖这些东西。一种特定的顺序只能在显式地选择了排序步骤之后才能被保证。

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 组合的结果，但是在这种情况中它只被允许根据输出列名或编号排序，而不能根据表达式排序。

---

事实上，PostgreSQL为表达式的数据类型使用默认B-tree操作符类来决定 ASC 和 DESC 的排序顺序。照惯例，数据类型将被建立，这样 < 和 > 操作符负责这个排序顺序，但是一个用户定义的数据类型的设计者可以选择做些不同的设置。

5. LIMIT和OFFSET

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 的效率可能还是不够高。

6. VALUES 列表

VALUES 提供了一种生成“常量表”的方法，它可以被使用在一个查询中而不需要实际在磁盘上创建一个表。语法是：

VALUES ( expression [, …] ) [, …]

每一个被圆括号包围的表达式列表生成表中的一行。列表都必须具有相同数据的元素（即表中列的数目），并且在每个列表中对应的项必须具有可兼容的数据类型。分配给结果的每一列的实际数据类型使用和 UNION 相同的规则确定（参见UNION、CASE和相关结构）。

一个例子：

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 命令的数据源，以及作为一个子查询。

7. WITH 查询（公共表表达式）

WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在 WITH 子句中的每一个辅助语句可以是一个 SELECT、INSERT、UPDATE 或 DELETE，并且 WITH 子句本身可以被附加到一个主语句，主语句也可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE。

7.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。使用这种方法要更简单些。

7.2 递归查询

可选的 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），再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行：

递归查询求值

1. 计算非递归项。对 UNION（但不对 UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。
2. 只要工作表不为空，重复下列步骤：
   1. 计算递归项，用当前工作表的内容替换递归自引用。对 UNION（不是 UNION ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。
   2. 用中间表的内容替换工作表的内容，然后清空中间表。

虽然 RECURSIVE 允许递归指定查询，但在内部此类查询是迭代计算的。

在上面的例子中，工作表在每一步只有一个行，并且它在连续的步骤中取值从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

7.2.1 搜索顺序

当使用递归查询计算树形遍历时，你可能希望以深度优先或广度优先的顺序对结果进行排序。这可以通过计算与其他数据列一起的排序列来实现，并在最后用它来对结果进行排序。请注意，这实际上并不能控制查询评估访问行的顺序；这在SQL中总是取决于实现。这种方法只是提供了一种方便的方法来对结果进行排序。

为了创建深度优先顺序，我们针对每个结果行计算已访问的行数组。例如，考虑使用 link 字段对表 tree 进行搜索的以下查询：

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree;

为了增加深度优先的排序信息，你可以这样写：

  WITH RECURSIVE search_tree(id, link, data, path) AS (
      SELECT t.id, t.link, t.data, ARRAY[t.id]
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, path || t.id
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY path;

在需要使用多个字段来识别行的一般情况下，使用一组行记录。例如，如果我们需要跟踪 f1 和 f2 字段，则使用一个行记录数组：

  WITH RECURSIVE search_tree(id, link, data, path) AS (
      SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY path;

在通常情况下只需要跟踪一个字段，可以省略 ROW() 语法。这样可以使用简单的数组而不是复合类型数组，提高效率。

为了创建一个广度优先的顺序，你可以添加一个跟踪搜索深度的列，例如：

  WITH RECURSIVE search_tree(id, link, data, depth) AS (
      SELECT t.id, t.link, t.data, 0
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, depth + 1
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY depth;

添加数据列作为二级排序列以获得稳定的排序。

递归查询评估算法以广度优先搜索顺序生成其输出。但是，这是一种实施细节，可能不能依赖它。每个级别内行的顺序肯定是未定义的，因此在任何情况下都可能需要一些显式排序。

有内置的语法可以计算深度或广度优先排序列。例如：

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  ) SEARCH DEPTH FIRST BY id SET ordercol
  SELECT * FROM search_tree ORDER BY ordercol;

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  ) SEARCH BREADTH FIRST BY id SET ordercol
  SELECT * FROM search_tree ORDER BY ordercol;

这个语法会在内部扩展为类似于上面手动编写的表单。SEARCH 子句指定要搜索深度优先还是广度优先，要跟踪进行排序的列的列表以及包含可用于排序的结果数据的列名称。该列将隐式地添加到CTE的输出行中。

7.2.2 循环检测

在使用递归查询时，确保查询的递归部分最终将不返回元组非常重要，否则查询将会无限循环。在某些时候，使用 UNION 替代 UNION ALL 可以通过抛弃与之前输出行重复的行来达到这个目的。不过，经常有循环不涉及到完全重复的输出行：它可能只需要检查一个或几个域来看相同点之前是否达到过。处理这种情况的标准方法是计算一个已经访问过值的数组。例如，考虑下面这个使用 link 域搜索表 graph 的查询：

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    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 不会消除循环。反过来在我们顺着一个特定链接路径搜索时，我们需要识别我们是否再次到达了一个相同的行。我们可以向这个有循环倾向的查询增加两个列 is_cycle 和 path：

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[g.id]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      g.id = ANY(path),
      path || g.id
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

除了阻止环，数组值对于它们自己的工作显示到达任何特定行的“path”也有用。

在通常情况下如果需要检查多于一个域来识别一个环，请用行数组。例如，如果我们需要比较域 f1 和 f2：

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[ROW(g.f1, g.f2)]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      ROW(g.f1, g.f2) = ANY(path),
      path || ROW(g.f1, g.f2)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

在通常情况下只有一个域需要被检查来识别一个环，可以省略 ROW() 语法。这允许使用一个简单的数组而不是一个组合类型数组，可以获得效率。

有一种内置的语法可以简化环路检测。上面的查询也可以这样编写：

  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
  ) CYCLE id SET is_cycle USING path
  SELECT * FROM search_graph;

内部将被重写成上述形式。CYCLE 子句首先指定要跟踪的列以检测循环，然后是一个列名，它将显示是否检测到循环，最后是将跟踪路径的其他列的名称。 循环和路径列将隐含地添加到 CTE 的输出行中。

循环路径列的计算方法与上一节中显示的深度优先排序列相同。一个查询可以同时有 SEARCH 和 CYCLE 子句，但是深度优先的搜索规范和周期检测规范会产生多余的计算，所以只使用 CYCLE 子句并按路径列排序会更有效率。如果想要进行广度优先排序，那么同时指定 SEARCH 和 CYCLE 会很有用。

当你不确定查询是否可能循环时，一个测试查询的有用技巧是在父查询中放一个 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 查询的所有输出。

7.2.3 公共表表达式物化

WITH 查询的一个有用的特性是在每一次父查询的执行中它们通常只被计算一次，即使它们被父查询或兄弟 WITH 查询引用了超过一次。因此，在多个地方需要的昂贵计算可以被放在一个 WITH 查询中来避免冗余工作。另一种可能的应用是阻止不希望的多个函数计算产生副作用。但是，从另一方面来看，优化器不能将来自父查询的约束下推到乘法引用 WITH 查询，因为当他应该只影响一个时它可能会影响所有使用 WITH 查询的输出的使用。乘法引用 WITH 查询通常将会被按照所写的方式计算，而不抑制父查询以后可能会抛弃的行（但是，如上所述，如果对查询的引用只请求有限数目的行，计算可能会提前停止）。

但是，如果 WITH 查询是非递归和边际效应无关的（就是说，它是一个 SELECT 包含没有可变函数），则它可以合并到父查询中，允许两个查询级别的联合优化。默认情况下，这发生在如果父查询仅引用 WITH 查询一次的时候，而不是它引用 WITH 查询多于一次时。你可以超越控制这个决策，通过指定 MATERIALIZED 来强制分开计算 WITH 查询，或者通过指定 NOT MATERIALIZED 来强制它被合并到父查询中。后一种选择存在重复计算 WITH 查询的风险，但它仍然能提供净节省，如果 WITH 查询的每个使用只需要 WITH 查询的完整输出的一小部分。

这些规则的一个简单示例是

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

这个 WITH 查询将被合并，生成相同的执行计划为

SELECT * FROM big_table WHERE key = 123;

特别是，如果在 key 上有一个索引，它可能只用于获取具有 key = 123 的行。另一方面，在

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

WITH 查询将被物化，生成一个 big_table 的临时拷贝，然后与其自身联合，这样将不能从索引上获得任何好处。如果写成下面的形式，这个查询将被执行得更有效率。

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

所以父查询的限制可以直接应用于 big_table 的扫描。

一个 NOT MATERIALIZED 可能不理想的例子为

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

在这里，WITH 查询的物化确保 very_expensive_function 每个表行只计算一次，而不是两次。

以上的例子只展示了和 SELECT 一起使用的 WITH，但是它可以被以相同的方式附加在 INSERT、UPDATE 或 DELETE 上。在每一种情况中，它实际上提供了可在主命令中引用的临时表。

7.2.4 WITH 中的数据修改语句

你可以在 WITH 中使用大部分数据修改语句（INSERT、UPDATE 或 DELETE，但不是 MERGE）。这允许你在同一个查询中执行多个不同操作。一个例子：

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 才会被执行。

WITH 中的子语句和其他子语句以及主查询被并发执行。因此在使用 WITH 中的数据修改语句时，无法预知实际更新顺序。所有的语句都使用同一个snapshot执行（参见并发控制），因此它们不能“看见”目标表上另一个执行的效果。这减轻了行更新的实际顺序的不可预见性的影响，并且意味着 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 规则。

标签：PostgreSQL,查询,详解,子句,JOIN,WHERE,id,SELECT
From： https://www.cnblogs.com/zreo2home/p/18353408