====== SELECT ====== .. container:: refentry :name: SQL-SELECT .. container:: titlepage .. container:: refnamediv .. rubric:: SELECT :name: select SELECT, TABLE, WITH — 从一个表或视图检索行 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: synopsis [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] [ * | expression [ [ AS ] output_name ] [, ...] ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] 其中 from_item 可以是以下之一： [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ] [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] function_name ( [ argument [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] ) [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] ) [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] 并且 grouping_element 可以是以下之一： ( ) expression ( expression [, ...] ) ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) CUBE ( { expression | ( expression [, ...] ) } [, ...] ) GROUPING SETS ( grouping_element [, ...] ) 并且 with_query 是： with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete ) TABLE [ ONLY ] table_name [ * ] .. container:: refsect1 :name: id-1.9.3.171.7 .. rubric:: 描述 :name: 描述 ``SELECT``\ 从零或更多表中检索行。 ``SELECT``\ 的通常处理如下： .. container:: orderedlist 1. ``WITH``\ 列表中的所有查询都会被计算。这些查询实际 充当了在\ ``FROM``\ 列表中可以引用的临时表。在 ``FROM``\ 中被引用多次的\ ``WITH``\ 查 询只会被计算一次，除非另有说明，否则\ ``NOT MATERIALIZED``\ 。 （见下文的\ ```WITH`` 子句 `__\ ）。 2. ``FROM``\ 列表中的所有元素都会被计算（ ``FROM``\ 中的每一个元素都是一个真实表或者虚拟表）。 如果在\ ``FROM``\ 列表中指定了多于一个元素，它们会被 交叉连接在一起（见下文的 ```FROM`` 子句 `__\ ）。 3. 如果指定了\ ``WHERE``\ 子句，所有不满足该条件的行都会 被从输出中消除（见下文的\ ```WHERE`` 子句 `__\ ）。 4. 如果指定了\ ``GROUP BY``\ 子句或者如果有聚集函数，输出 会被组合成由在一个或者多个值上匹配的行构成的分组，并且在其上计算聚 集函数的结果。如果出现了\ ``HAVING``\ 子句，它会消除不 满足给定条件的分组（见下文的 ```GROUP BY`` 子句 `__\ 以及 ```HAVING`` 子句 `__\ ）。 5. 对于每一个被选中的行或者行组，会使用\ ``SELECT`` 输出表达式计算实际的输出行（见下文的 ```SELECT`` 列表 `__\ ）。 6. ``SELECT DISTINCT``\ 从结果中消除重复的行。 ``SELECT DISTINCT ON``\ 消除在所有指定表达式上匹 配的行。\ ``SELECT ALL``\ （默认）将返回所有候选行， 包括重复的行（见下文的\ ```DISTINCT`` 子句 `__\ ）。 7. 通过使用操作符\ ``UNION``\ 、 ``INTERSECT``\ 和\ ``EXCEPT``\ ，多于 一个\ ``SELECT``\ 语句的输出可以被整合形成 一个结果集。\ ``UNION``\ 操作符返回位于一个或者两 个结果集中的全部行。\ ``INTERSECT``\ 操作符返回同时 位于两个结果集中的所有行。\ ``EXCEPT``\ 操作符返回 位于第一个结果集但不在第二个结果集中的行。在所有三种情况下， 重复行都会被消除（除非指定\ ``ALL``\ ）。可以增加噪 声词\ ``DISTINCT``\ 来显式地消除重复行。注意虽然 ``ALL``\ 是\ ``SELECT``\ 自身的默认行为， 但这里\ ``DISTINCT``\ 是默认行为（见下文的 ```UNION`` 子句 `__\ 、\ ```INTERSECT`` 子句 `__\ 以及 ```EXCEPT`` 子句 `__\ ）。 8. 如果指定了\ ``ORDER BY``\ 子句，被返回的行会以指定的 顺序排序。如果没有给定\ ``ORDER BY``\ ，系统会以能最 快产生行的顺序返回它们（见下文的 ```ORDER BY`` 子句 `__\ ）。 9. 如果指定了\ ``LIMIT``\ （或\ ``FETCH FIRST``\ ） 或者\ ``OFFSET``\ 子句，\ ``SELECT`` 语句只返回结果行的一个子集（见下文的\ ```LIMIT`` 子句 `__\ ）。 10. 如果指定了\ ``FOR UPDATE``\ 、 ``FOR NO KEY UPDATE``\ 、 ``FOR SHARE`` 或者\ ``FOR KEY SHARE``\ ， ``SELECT``\ 语句会把被选中的行锁定而不让并发 更新访问它们（见下文的\ `锁定子句 `__\ ）。 你必须拥有在一个\ ``SELECT``\ 命令中使用的每一列上的 ``SELECT``\ 特权。\ ``FOR NO KEY UPDATE``\ 、 ``FOR UPDATE``\ 、 ``FOR SHARE``\ 或者\ ``FOR KEY SHARE`` 还要求（对这样选中的每一个表至少一列的）\ ``UPDATE`` 特权。 .. container:: refsect1 :name: id-1.9.3.171.8 .. rubric:: 参数 :name: 参数 .. container:: refsect2 :name: SQL-WITH .. rubric:: ``WITH`` 子句 :name: with-子句 ``WITH``\ 子句允许你指定一个或者多个在主查询中可以 其名称引用的子查询。在主查询期间子查询实际扮演了临时表或者视图 的角色。每一个子查询都可以是一个\ ``SELECT``\ 、 ``TABLE``\ 、\ ``VALUES``\ 、 ``INSERT``\ 、 ``UPDATE``\ 或者 ``DELETE``\ 语句。在\ ``WITH``\ 中书写 一个数据修改语句（\ ``INSERT``\ 、 ``UPDATE``\ 或者 ``DELETE``\ ）时，通常要包括一个 ``RETURNING``\ 子句。构成被主查询读取的临时表的是 ``RETURNING``\ 的输出，而\ *不是*\ 该语句修改的 底层表。如果省略\ ``RETURNING``\ ，该语句仍会被执行，但是它 不会产生输出，因此它不能作为一个表从主查询引用。 对于每一个\ ``WITH``\ 查询，都必须指定一个名称（无需模 式限定）。可选地，可以指定一个列名列表。如果省略该列表，会从该子查 询中推导列名。 如果指定了\ ``RECURSIVE``\ ，则允许一个 ``SELECT``\ 子查询使用名称引用自身。 这样一个子查询的形式必须是 .. code:: synopsis non_recursive_term UNION [ ALL | DISTINCT ] recursive_term 其中递归自引用必须出现在\ ``UNION``\ 的右手边。每个 查询中只允许一个递归自引用。不支持递归数据修改语句，但是 可以在一个数据查询语句中使用一个递归 ``SELECT``\ 查询的结果。例子可见 `节 `__\ 。 ``RECURSIVE``\ 的另一个效果是 ``WITH``\ 查询不需要被排序：一个查询可以引用另一个 在列表中比它靠后的查询（不过，循环引用或者互递归没有实现）。 如果没有\ ``RECURSIVE``\ ，\ ``WITH`` 查询只能引用在\ ``WITH``\ 列表中位置更前面的兄弟 ``WITH``\ 查询。 当\ ``WITH``\ 子句中有多个查询时，\ ``RECURSIVE``\ 应只编写一次，紧跟在\ ``WITH``\ 之后。 它适用于\ ``WITH``\ 子句中的所有查询，尽管它对不使用递归或前向引用的查询没有影响。 主查询以及\ ``WITH``\ 查询全部（理论上）在同一时间 被执行。这意味着从该查询的任何部分都无法看到 ``WITH``\ 中的一个数据修改语句的效果，不过可以读 取其\ ``RETURNING``\ 输出。如果两个这样的数据修改语句 尝试修改相同的行，结果将无法确定。 ``WITH``\ 查询的一个关键属性是，即使主查询多次引用它们，它们通常每次执行主查询只计算一次。 特别是，数据修改语句确保执行一次而且只执行一次，而与主查询是否读取它们的全部或任何输出无关。 但是，\ ``WITH``\ 查询可以标记为\ ``NOT MATERIALIZED``\ 以移除此保证。 在这种情况下，\ ``WITH``\ 查询可以折叠到主查询中，就好像它是主查询的\ ``FROM``\ 子句中的简单的sub-``SELECT``\ 。 如果主查询多次引用\ ``WITH``\ 查询，则会导致重复计算;但是，如果每次此类使用只需要\ ``WITH``\ 查询的总输出中的几行，\ ``NOT MATERIALIZED``\ 可以通过允许查询联合优化来节省开销。 ``NOT MATERIALIZED``\ 被忽略，如果它被附加到一个递归的\ ``WITH``\ 查询，或者不是边际效应无关的（也就是说，不是包含非易失性函数的普通的\ ``SELECT``\ ）。 默认情况下，如果查询在主查询的\ ``FROM``\ 子句中仅一次使用，则边际效应无关的\ ``WITH``\ 查询将折叠到主查询中。 这允许在语义不可见的情况下两个查询级别的联合优化。 但是，通过将\ ``WITH``\ 查询标记为\ ``MATERIALIZED``\ ，可以防止此类折叠。 这可能很有用，例如，如果\ ``WITH``\ 查询被用作优化围栏，以防止规划者选择错误计划。 PostgreSQLv12版本之前没有做过这样的折叠，所以为旧版本编写的查询可能依赖于\ ``WITH``\ 作为优化围栏。 更多信息请见\ `节 `__\ 。 .. container:: refsect2 :name: SQL-FROM .. rubric:: ``FROM`` 子句 :name: from-子句 ``FROM``\ 子句为\ ``SELECT`` 指定一个或者更多源表。如果指定了多个源表，结果将是所有源表的 笛卡尔积（交叉连接）。但是通常会增加限定条件（通过 ``WHERE``\ ）来把返回的行限制为该笛卡尔积的一个小子集。 ``FROM``\ 子句可以包含下列元素： .. container:: variablelist *``table_name``* 一个现有表或视图的名称（可以是模式限定的）。如果在表名前指定了 ``ONLY``\ ，则只会扫描该表。如果没有指定 ``ONLY``\ ，该表及其所有后代表（如果有）都会被扫描。可 选地，可以在表名后指定\ ``*``\ 来显式地指示包括后代表。 *``alias``* 一个包含别名的\ ``FROM``\ 项的替代名称。别名被用于 让书写简洁或者消除自连接中的混淆（其中同一个表会被扫描多 次）。当提供一个别名时，表或者函数的实际名称会被隐藏。例 如，给定\ ``FROM foo AS f``\ ， ``SELECT``\ 的剩余部分就必须以 ``f``\ 而不是\ ``foo``\ 来引用这个 ``FROM``\ 项。如果写了一个别名，还可以写一个列别 名列表来为该表的一个或者多个列提供替代名称。 ``TABLESAMPLE sampling_method`` ( *``argument``* [, ...] ) [ REPEATABLE ( *``seed``* ) ] *``table_name``*\ 之后的 ``TABLESAMPLE``\ 子句表示应该用指定的 *``sampling_method``* 来检索表中行的子集。这种采样优先于任何其他过滤器（例如 ``WHERE``\ 子句）。标准 PostgreSQL发布包括两种采样 方法：\ ``BERNOULLI``\ 和\ ``SYSTEM``\ ， 其他采样方法可以通过扩展安装在数据库中。 ``BERNOULLI``\ 以及\ ``SYSTEM``\ 采样方法都接受 一个\ *``参数``*\ ，它表示要采样的表 的分数，表示为一个 0 到 100 之间的百分数。这个参数可以是任意的 ``实数值``\ 表达式（其他的采样方法可能接受更多或者不同的 参数）。这两种方法都返回一个随机选取的该表采样，其中包含了指定 百分数的表行。\ ``BERNOULLI``\ 方法扫描整个表并且 用指定的几率选择或者忽略行。\ ``SYSTEM``\ 方法会做 块层的采样，每个块都有指定的机会能被选中，被选中块中的所有行都 会被返回。在指定较小的采样百分数时，\ ``SYSTEM`` 方法要比\ ``BERNOULLI``\ 方法快很多，但是前者可能 由于聚簇效应返回随机性较差的表采样。 可选的\ ``REPEATABLE``\ 子句指定一个用于产生采样方法中随机数的\ *``种子``*\ 数或表达式。种子值可以是任何非空浮点值。如果查询时表没有被更改，指定相同种子和\ *``argument``*\ 值的两个查询将会选择该表相同的采样。但是不同的种子值通常将会产生不同的采样。如果没有给出\ ``REPEATABLE``\ ，则会基于一个系统产生的种子为每一个查询选择一个新的随机采样。注意有些扩展采样方法不接受\ ``REPEATABLE``\ ，并且将总是为每一次使用产生新的采样。 *``select``* 一个子-``SELECT``\ 可以出现在 ``FROM``\ 子句中。这就好像把它的输出创建为一个 存在于该\ ``SELECT``\ 命令期间的临时表。注意 子-``SELECT``\ 必须用圆括号包围，并且 *必须*\ 为它提供一个别名。也可以在这里 使用一个\ `VALUES `__\ 命令。 *``with_query_name``* 可以通过写一个\ ``WITH``\ 查询的名称来引用它，就好像 该查询的名称是一个表名（实际上，该\ ``WITH``\ 查询会 为主查询隐藏任何具有相同名称的真实表。如果必要，你可以使用 带模式限定的方式以相同的名称来引用真实表）。可以像表一样， 以同样的方式提供一个别名。 *``function_name``* 函数调用可以出现在\ ``FROM``\ 子句中（对于返回结果 集合的函数特别有用，但是可以使用任何函数）。这就好像把该函数的 输出创建为一个存在于该\ ``SELECT``\ 命令期 间的临时表。当为该函数调用增加可选的 ``WITH ORDINALITY``\ 子句时，会在该函数 的输出列之后追加一个新的列来为每一行编号。 可以用和表一样的方式提供一个别名。如果写了一个别名，还可以写一个 列别名列表来为该函数的组合返回类型的一个或者多个属性提供替代名称， 包括由\ ``ORDINALITY``\ （如果有）增加的新列。 通过把多个函数调用包围在\ ``ROWS FROM( ... )``\ 中可以把它们 整合在单个\ ``FROM``-子句项中。这样一个项的输出是把每一个 函数的第一行串接起来，然后是每个函数的第二行，以此类推。如果有些 函数产生的行比其他函数少，则在缺失数据的地方放上空值，这样被返回 的总行数总是和产生最多行的函数一样。 如果函数被定义为返回\ ``record``\ 数据类型，那么必须出现一个 别名或者关键词\ ``AS``\ ，后面跟上形为 ``( column_name`` *``data_type``* [, ...])的列定义列表。列定义列表必须匹配该函数返回的列的实际 数量和类型。 在使用\ ``ROWS FROM( ... )``\ 语法时，如果函数之一要求一个列 定义列表，最好把该列定义列表放在\ ``ROWS FROM( ... )``\ 中该 函数的调用之后。当且仅当正好只有一个函数并且没有 ``WITH ORDINALITY``\ 子句时，才能把列定义列表放在 ``ROWS FROM( ... )``\ 结构后面。 要把\ ``ORDINALITY``\ 和列定义列表一起使用，你必须使用 ``ROWS FROM( ... )``\ 语法，并且把列定义列表放在 ``ROWS FROM( ... )``\ 里面。 *``join_type``* One of .. container:: itemizedlist - ``[ INNER ] JOIN`` - ``LEFT [ OUTER ] JOIN`` - ``RIGHT [ OUTER ] JOIN`` - ``FULL [ OUTER ] JOIN`` - ``CROSS JOIN`` 对于\ ``INNER``\ 和\ ``OUTER``\ 连接类型，必须指定 一个连接条件，即 ``NATURAL``\ 、\ ``ON join_condition``\ 或者 ``USING (join_column`` [, ...]) 之一（只能有一种）。其含义见下文。对于 ``CROSS JOIN``\ ，上述子句不能出现。 一个\ ``JOIN``\ 子句联合两个\ ``FROM``\ 项（ 为了方便我们称之为“表”，尽管实际上它们可以是任何类型 的\ ``FROM``\ 项）。如有必要可以使用圆括号确定嵌套的顺序。 在没有圆括号时，\ ``JOIN``\ 会从左至右嵌套。在任何情 况下，\ ``JOIN``\ 的联合比分隔\ ``FROM``-列表 项的逗号更强。 ``CROSS JOIN``\ 和\ ``INNER JOIN`` 会产生简单的笛卡尔积，也就是与在\ ``FROM``\ 的顶层列出两个 表得到的结果相同，但是要用连接条件（如果有）约束该结果。 ``CROSS JOIN``\ 与\ ``INNER JOIN ON (TRUE)``\ 等效，也就是说条件不会移除任何行。这些连接类型只是一种 记号上的方便，因为没有什么是你用纯粹的\ ``FROM``\ 和 ``WHERE``\ 能做而它们不能做的。 ``LEFT OUTER JOIN``\ 返回被限制过的笛卡尔积 中的所有行（即所有通过了其连接条件的组合行），外加左手表中 没有相应的通过了连接条件的右手行的每一行的拷贝。通过在右手 列中插入空值，这种左手行会被扩展为连接表的完整行。注意在决 定哪些行匹配时，只考虑\ ``JOIN``\ 子句自身的条件。之后 才应用外条件。 相反，\ ``RIGHT OUTER JOIN``\ 返回所有连接行，外加每 一个没有匹配上的右手行（在左端用空值扩展）。这只是为了记号 上的方便，因为你可以通过交换左右表把它转换成一个\ ``LEFT OUTER JOIN``\ 。 ``FULL OUTER JOIN``\ 返回所有连接行，外加每 一个没有匹配上的左手行（在右端用空值扩展），再外加每一个没有 匹配上的右手行（在左端用空值扩展）。 ``ON join_condition`` *``join_condition``* 是一个会得到\ ``boolean``\ 类型值的表达式（类似于一个 ``WHERE``\ 子句），它说明一次连接中哪些行被认为 相匹配。 ``USING ( join_column`` [, ...] ) 形式\ ``USING ( a, b, ... )``\ 的子句是 ``ON left_table.a = right_table.a AND left_table.b = right_table.b ...``\ 的简写。还有， ``USING``\ 表示每一对相等列中只有一个会被 包括在连接输出中。 ``NATURAL`` ``NATURAL``\ 是一个\ ``USING``\ 列表的速记，该列表中提到两个表中具有匹配名称的所有的列。如果没有公共列名，则\ ``NATURAL``\ 等效于\ ``ON TRUE``\ 。 ``LATERAL`` ``LATERAL``\ 关键词可以放在一个 子-``SELECT`` ``FROM``\ 项前面。这允许该 子-``SELECT``\ 引用\ ``FROM``\ 列表中在它之 前的\ ``FROM``\ 项的列（如果没有\ ``LATERAL``\ ，每一 个子-``SELECT``\ 会被独立计算并且因此不能交叉引用 任何其他的\ ``FROM``\ 项）。 ``LATERAL``\ 也可以放在一个函数调用 ``FROM``\ 项前面，但是在这种情况下它只是一个噪声 词，因为在任何情况下函数表达式都可以引用在它之前的 ``FROM``\ 项。 ``LATERAL``\ 项可以出现在\ ``FROM``\ 列表 顶层，或者一个\ ``JOIN``\ 中。在后一种情况中，它也可以引 用其作为右手端的\ ``JOIN``\ 左手端上的任何项。 当一个\ ``FROM``\ 项包含\ ``LATERAL``\ 交叉引用 时，计算会如此进行：对提供被交叉引用列的\ ``FROM``\ 项的每 一行或者提供那些列的多个\ ``FROM``\ 项的每一个行集，使用该 行或者行集的那些列值计算\ ``LATERAL``\ 项。结果行会与 计算得到它们的行进行通常的连接。对来自哪些列的源表的每一行或者行 集都会重复这样的步骤。 列的源表必须以\ ``INNER``\ 或者\ ``LEFT``\ 的方式连接到 ``LATERAL``\ 项，否则就没有用于为 ``LATERAL``\ 项计算每一个行集的良定行集。尽管 ``X`` RIGHT JOIN LATERAL *``Y``*\ 这样的结构在语法上是合法的， 但实际上不允许用于在\ *``Y``*\ 中引用 *``X``*\ 。 .. container:: refsect2 :name: SQL-WHERE .. rubric:: ``WHERE`` 子句 :name: where-子句 可选的\ ``WHERE``\ 子句的形式 .. code:: synopsis WHERE condition 其中\ *``condition``* 是任一计算得到\ ``布尔``\ 类型结果的表达式。任何不满足 这个条件的行都会从输出中被消除。如果用一行的实际值替换其中的 变量引用后，该表达式返回真，则该行符合条件。 .. container:: refsect2 :name: SQL-GROUPBY .. rubric:: ``GROUP BY`` 子句 :name: group-by-子句 可选的\ ``GROUP BY``\ 子句的形式 .. code:: synopsis GROUP BY grouping_element [, ...] ``GROUP BY``\ 将会把所有被选择的行中共享相同分组表达式 值的那些行压缩成一个行。一个被用在 *``grouping_element``*\ 中的 *``expression``*\ 可以是输入列名、输出列 （\ ``SELECT``\ 列表项）的名称或序号或者由输入列 值构成的任意表达式。在出现歧义时，\ ``GROUP BY``\ 名称 将被解释为输入列名而不是输出列名。 如果任何\ ``GROUPING SETS``\ 、\ ``ROLLUP``\ 或者 ``CUBE``\ 作为分组元素存在，则\ ``GROUP BY``\ 子句 整体上定义了数个独立的\ *``分组集``*\ 。其效果等效于在子 查询间构建一个\ ``UNION ALL``\ ，子查询带有分组集作为它们 的\ ``GROUP BY``\ 子句。处理分组集的进一步细节请见 `节 `__\ 。 聚集函数（如果使用）会在组成每一个分组的所有行上进行计算，从而为每 一个分组产生一个单独的值（如果有聚集函数但是没有 ``GROUP BY``\ 子句，则查询会被当成是由所有选中行构成 的一个单一分组）。传递给每一个聚集函数的行集合可以通过在聚集函数调 用附加一个\ ``FILTER``\ 子句来进一步过滤，详见 `节 `__\ 。当存在一个 ``FILTER``\ 子句时，只有那些匹配它的行才会被包括在该聚 集函数的输入中。 当存在\ ``GROUP BY``\ 子句或者任何聚集函数时， ``SELECT``\ 列表表达式不能引用非分组列（除非它 出现在聚集函数中或者它函数依赖于分组列），因为这样做会导致返回 非分组列的值时会有多种可能的值。如果分组列是包含非分组列的表的主键（ 或者主键的子集），则存在函数依赖。 记住所有的聚集函数都是在\ ``HAVING``\ 子句或者 ``SELECT``\ 列表中的任何“标量”表达式之前被计算。 这意味着一个\ ``CASE``\ 表达式不能被用来跳过一个聚集表达式的 计算，见\ `节 `__\ 。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE``\ 不能和 ``GROUP BY``\ 一起指定。 .. container:: refsect2 :name: SQL-HAVING .. rubric:: ``HAVING`` 子句 :name: having-子句 可选的\ ``HAVING``\ 子句的形式 .. code:: synopsis HAVING condition 其中\ *``condition``*\ 与 ``WHERE``\ 子句中指定的条件相同。 ``HAVING``\ 消除不满足该条件的分组行。 ``HAVING``\ 与\ ``WHERE``\ 不同： ``WHERE``\ 会在应用\ ``GROUP BY``\ 之前过滤个体行，而\ ``HAVING``\ 过滤由 ``GROUP BY``\ 创建的分组行。 *``condition``*\ 中引用 的每一个列必须无歧义地引用一个分组列（除非该引用出现在一个聚集 函数中或者该非分组列函数依赖于分组列。 即使没有\ ``GROUP BY``\ 子句，\ ``HAVING`` 的存在也会把一个查询转变成一个分组查询。这和查询中包含聚集函数但没有 ``GROUP BY``\ 子句时的情况相同。所有被选择的行都被认为是一个 单一分组，并且\ ``SELECT``\ 列表和 ``HAVING``\ 子句只能引用聚集函数中的表列。如果该 ``HAVING``\ 条件为真，这样一个查询将会发出一个单一行； 否则不返回行。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE``\ 不能与 ``HAVING``\ 一起指定。 .. container:: refsect2 :name: SQL-WINDOW .. rubric:: ``WINDOW`` 子句 :name: window-子句 可选的\ ``WINDOW``\ 子句的形式 .. code:: synopsis WINDOW window_name AS ( window_definition ) [, ...] 其中\ *``window_name``* 是一个可以从\ ``OVER``\ 子句或者后续窗口定义中引用的名称。 *``window_definition``*\ 是 .. code:: synopsis [ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ] 如果指定了一个\ *``existing_window_name``*\ ， 它必须引用\ ``WINDOW``\ 列表中一个更早出现的项。新窗口将从 该项中复制它的划分子句以及排序子句（如果有）。在这种情况下，新窗口 不能指定它自己的\ ``PARTITION BY``\ 子句，并且它只能在被复制 窗口没有\ ``ORDER BY``\ 的情况下指定该子句。新窗口总是使用它 自己的帧子句，被复制的窗口不必指定一个帧子句。 ``PARTITION BY``\ 列表元素的解释以 ```GROUP BY`` 子句 `__\ 元素的方式 进行，不过它们总是简单表达式并且绝不能是输出列的名称或编号。另一个区 别是这些表达式可以包含聚集函数调用，而这在常规\ ``GROUP BY`` 子句中是不被允许的。它们被允许的原因是窗口是出现在分组和聚集之后的。 类似地，\ ``ORDER BY``\ 列表元素的解释也以 ```ORDER BY`` 子句 `__\ 元素的方式进行， 不过该表达式总是被当做简单表达式并且绝不会是输出列的名称或编号。 可选的\ *``frame_clause``*\ 为依赖帧的窗口函数 定义\ *窗口帧*\ （并非所有窗口函数都依赖于帧）。窗口帧是查询中 每一样（称为\ *当前行*\ ）的相关行的集合。 *``frame_clause``*\ 可以是 .. code:: synopsis { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] { RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ] 之一，其中\ *``frame_start``*\ 和\ *``frame_end``*\ 可以是 .. code:: synopsis UNBOUNDED PRECEDING offset PRECEDING CURRENT ROW offset FOLLOWING UNBOUNDED FOLLOWING 之一，并且\ *``frame_exclusion``*\ 可以是 .. code:: synopsis EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS 之一。如果省略\ *``frame_end``*\ ，它会被默认为\ ``CURRENT ROW``\ 。限制是： *``frame_start``*\ 不能是\ ``UNBOUNDED FOLLOWING``\ ， *``frame_end``*\ 不能是\ ``UNBOUNDED PRECEDING``\ ， 并且\ *``frame_end``*\ 的选择在上面of *``frame_start``*\ 以及\ *``frame_end``* 选项的列表中不能早于 *``frame_start``*\ 的选择 — 例如 ``RANGE BETWEEN CURRENT ROW AND offset`` PRECEDING是不被允许的。 默认的帧选项是\ ``RANGE UNBOUNDED PRECEDING``\ ，它和 ``RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW``\ 相同。它把帧设置为从分区开始直到当前行的最后一个\ *平级*\ 行（被该窗口的\ ``ORDER BY``\ 子句认为等价于当前行的行，如果没有\ ``ORDER BY``\ 则所有的行都是平级的）。通常， ``UNBOUNDED PRECEDING``\ 表示从分区第一行开始的帧，类似地 ``UNBOUNDED FOLLOWING``\ 表示以分区最后一行结束的帧，不论是处于\ ``RANGE``\ 、\ ``ROWS``\ 或者\ ``GROUPS``\ 模式中。在\ ``ROWS``\ 模式中， ``CURRENT ROW``\ 表示以当前行开始或者结束的帧。而在 ``RANGE``\ 或者\ ``GROUPS``\ 模式中它表示当前行在\ ``ORDER BY``\ 排序中的第一个 或者最后一个平级行开始或者结束的帧。 *``offset``* ``PRECEDING``\ 和\ *``offset``* ``FOLLOWING``\ 选项的含义会随着帧模式而变化。在\ ``ROWS``\ 模式中，\ *``offset``*\ 是一个整数，表示帧开始或者结束于当前行之前或者之后的那么多行处。在\ ``GROUPS``\ 模式中，\ *``offset``*\ 是一个整数，表示真开始或者结束于当前行的平级组之前或者之后那么多个平级组处，其中\ *平级组*\ 是一组根据窗口的\ ``ORDER BY``\ 子句等效的行。在\ ``RANGE``\ 模式中，\ *``offset``*\ 选项的使用要求在窗口定义中正好有一个\ ``ORDER BY``\ 列。那么该帧包含的行的排序列值不超过\ *``offset``*\ 且小于（对于\ ``PRECEDING``\ ）或者大于（对于\ ``FOLLOWING``\ ）当前行的排序列值。在这些情况中，\ *``offset``*\ 表达式的数据类型取决于排序列的数据类型。对于数字排序列，它通常与排序列是相同类型，但对于datetime类型的排序列它是\ ``interval``\ 。在所有这些情况中，\ *``offset``*\ 的值必须是非空和非负。此外，虽然\ *``offset``*\ 并非必须是简单常量，但它不能包含变量、聚集函数或者窗口函数。 *``frame_exclusion``*\ 选项允许从帧中排除当前行周围的行，即便根据帧的起始选项来说它们应该被包含在帧中。\ ``EXCLUDE CURRENT ROW``\ 把当前行从帧中排除。\ ``EXCLUDE GROUP``\ 把当前行和它在排序上的平级行从帧中排除。\ ``EXCLUDE TIES``\ 从帧中排除当前行的任何平级行，但是不排除当前行本身。\ ``EXCLUDE NO OTHERS``\ 只是明确地指定不排除当前行或其平级行的默认行为。 注意，如果\ ``ORDER BY``\ 排序无法把行唯一地排序，则\ ``ROWS``\ 模式可能产生不可预测的结果。\ ``RANGE``\ 以及\ ``GROUPS``\ 模式的目的是确保在\ ``ORDER BY``\ 顺序中平等的行被同样对待：一个给定平级组中的所有行将在一个帧中或者被从帧中排除。 ``WINDOW``\ 子句的目的是指定出现在查询的 ```SELECT`` 列表 `__\ 或 ```ORDER BY`` 子句 `__\ 中的 *窗口函数*\ 的行为。这些函数可以在它们的 ``OVER``\ 子句中用名称引用\ ``WINDOW`` 子句项。不过，\ ``WINDOW``\ 子句项不是必须被引用。 如果在查询中没有用到它，它会被简单地忽略。可以使用根本没有任何 ``WINDOW``\ 子句的窗口函数，因为窗口函数调用可 以直接在其\ ``OVER``\ 子句中指定它的窗口定义。不过，当多 个窗口函数都需要相同的窗口定义时， ``WINDOW``\ 子句能够减少输入。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE``\ 不能和 ``WINDOW``\ 一起被指定。 窗口函数的详细描述在 `节 `__\ 、 `节 `__\ 以及 `节 `__\ 中。 .. container:: refsect2 :name: SQL-SELECT-LIST .. rubric:: ``SELECT`` 列表 :name: select-列表 ``SELECT``\ 列表（位于关键词 ``SELECT``\ 和\ ``FROM``\ 之间）指定构成 ``SELECT``\ 语句输出行的表达式。这些表达式 可以（并且通常确实会）引用\ ``FROM``\ 子句中计算得到的列。 正如在表中一样，\ ``SELECT``\ 的每一个输出列都有一个名称。 在一个简单的\ ``SELECT``\ 中，这个名称只是被用来标记要显 示的列，但是当\ ``SELECT``\ 是一个大型查询的一个子查询时，大型查询 会把该名称看做子查询产生的虚表的列名。要指定用于输出列的名称，在该列的表达式 后面写上 ``AS`` *``output_name``*\ （ 你可以省略\ ``AS``\ ，但只能在期望的输出名称不匹配任何 PostgreSQL关键词（见\ `附录 C `__\ ）时省略。为了避免和未来增加的关键词冲突， 推荐总是写上\ ``AS``\ 或者用双引号引用输出名称）。如果你不指定列名， PostgreSQL会自动选择一个名称。如果列的表达式 是一个简单的列引用，那么被选择的名称就和该列的名称相同。在使用函数或者类型名称 的更复杂的情况中，系统可能会生成诸如 ``?column?``\ 之类的名称。 一个输出列的名称可以被用来在\ ``ORDER BY``\ 以及 ``GROUP BY``\ 子句中引用该列的值，但是不能用于 ``WHERE``\ 和\ ``HAVING``\ 子句（在其中 必须写出表达式）。 可以在输出列表中写\ ``*``\ 来取代表达式，它是被选中 行的所有列的一种简写方式。还可以写 ``table_name``.*，它 是只来自那个表的所有列的简写形式。在这些情况中无法用 ``AS``\ 指定新的名称，输出行的名称将和表列的名称相同。 根据 SQL 标准，输出列表中的表达式应该在应用\ ``DISTINCT``\ 、\ ``ORDER BY``\ 或者\ ``LIMIT``\ 之前计算。在使用\ ``DISTINCT``\ 时显然必须这样做，否则就无法搞清到底在区分什么值。不过，在很多情况下如果先计算\ ``ORDER BY``\ 和\ ``LIMIT``\ 再计算输出表达式会很方便，特别是如果输出列表中包含任何 volatile 函数或者代价昂贵的函数时尤其如此。通过这种行为，函数计算的顺序更加直观并且对于从未出现在输出中的行将不会进行计算。只要输出表达式没有被\ ``DISTINCT``\ 、\ ``ORDER BY``\ 或者\ ``GROUP BY``\ 引用，PostgreSQL实际将在排序和限制行数之后计算输出表达式（作为一个反例，\ ``SELECT f(x) FROM tab ORDER BY 1``\ 显然必须在排序之前计算\ ``f(x)``\ ）。包含有集合返回函数的输出表达式实际是在排序之后和限制行数之前被计算，这样\ ``LIMIT``\ 才能切断来自集合返回函数的输出。 .. note:: .. rubric:: 注意 :name: 注意 :class: title 9.6 版本之前的PostgreSQL不对执行输出表达式、排序、限制行数的时间顺序做任何保证，那将取决于被选中的查询计划的形式。 .. container:: refsect2 :name: SQL-DISTINCT .. rubric:: ``DISTINCT`` 子句 :name: distinct-子句 如果指定了\ ``SELECT DISTINCT``\ ，所有重复的行会被从结果 集中移除（为每一组重复的行保留一行）。\ ``SELECT ALL``\ 则 指定相反的行为：所有行都会被保留，这也是默认情况。 ``SELECT DISTINCT ON ( expression`` [, ...] ) 只保留在给定表达式上计算相等的行集合中的第一行。 ``DISTINCT ON``\ 表达式使用和 ``ORDER BY``\ 相同的规则（见上文）解释。注意，除非用 ``ORDER BY``\ 来确保所期望的行出现在第一位，每一个集 合的“第一行”是不可预测的。例如： .. code:: programlisting SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; 为每个地点检索最近的天气报告。但是如果我们不使用 ``ORDER BY``\ 来强制对每个地点的时间值进行降序排序， 我们为每个地点得到的报告的时间可能是无法预测的。 ``DISTINCT ON``\ 表达式必须匹配最左边的 ``ORDER BY``\ 表达式。\ ``ORDER BY``\ 子句通常 将包含额外的表达式，这些额外的表达式用于决定在每一个 ``DISTINCT ON``\ 分组内行的优先级。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE``\ 不能和 ``DISTINCT``\ 一起使用。 .. container:: refsect2 :name: SQL-UNION .. rubric:: ``UNION`` 子句 :name: union-子句 ``UNION``\ 子句具有下面的形式： .. code:: synopsis select_statement UNION [ ALL | DISTINCT ] select_statement *``select_statement``* 是任何没有\ ``ORDER BY``\ 、\ ``LIMIT``\ 、 ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE``\ 子句的 ``SELECT``\ 语句（如果子表达式被包围在圆括号内， ``ORDER BY``\ 和\ ``LIMIT``\ 可以被附着到其上。如果没有 圆括号，这些子句将被应用到\ ``UNION``\ 的结果而不是右手边 的表达式上）。 ``UNION``\ 操作符计算所涉及的 ``SELECT``\ 语句所返回的行的并集。如果一行 至少出现在两个结果集中的一个内，它就会在并集中。作为 ``UNION``\ 两个操作数的 ``SELECT``\ 语句必须产生相同数量的列并且 对应位置上的列必须具有兼容的数据类型。 ``UNION``\ 的结果不会包含重复行，除非指定了 ``ALL``\ 选项。\ ``ALL``\ 会阻止消除重复（因此， ``UNION ALL``\ 通常显著地快于\ ``UNION``\ ， 尽量使用\ ``ALL``\ ）。可以写\ ``DISTINCT``\ 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个\ ``SELECT``\ 语句中的多个 ``UNION``\ 操作符会从左至右计算。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和 ``FOR KEY SHARE``\ 不能用于\ ``UNION``\ 结果或者 ``UNION``\ 的任何输入。 .. container:: refsect2 :name: SQL-INTERSECT .. rubric:: ``INTERSECT`` 子句 :name: intersect-子句 ``INTERSECT``\ 子句具有下面的形式： .. code:: synopsis select_statement INTERSECT [ ALL | DISTINCT ] select_statement *``select_statement``* 是任何没有\ ``ORDER BY``, ``LIMIT``\ 、\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 以及\ ``FOR KEY SHARE``\ 子句的 ``SELECT``\ 语句。 ``INTERSECT``\ 操作符计算所涉及的 ``SELECT``\ 语句返回的行的交集。如果 一行同时出现在两个结果集中，它就在交集中。 ``INTERSECT``\ 的结果不会包含重复行，除非指定了 ``ALL``\ 选项。如果有\ ``ALL``\ ，一个在左表中有 *``m``*\ 次重复并且在右表中有\ *``n``* 次重复的行将会在结果中出现 min(*``m``*,\ *``n``*) 次。 ``DISTINCT``\ 可以写\ ``DISTINCT``\ 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个\ ``SELECT``\ 语句中的多个 ``INTERSECT``\ 操作符会从左至右计算。 ``INTERSECT``\ 的优先级比 ``UNION``\ 更高。也就是说， ``A UNION B INTERSECT C``\ 将被读成\ ``A UNION (B INTERSECT C)``\ 。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和 ``FOR KEY SHARE``\ 不能用于\ ``INTERSECT``\ 结果或者 ``INTERSECT``\ 的任何输入。 .. container:: refsect2 :name: SQL-EXCEPT .. rubric:: ``EXCEPT`` 子句 :name: except-子句 ``EXCEPT``\ 子句具有下面的形式： .. code:: synopsis select_statement EXCEPT [ ALL | DISTINCT ] select_statement *``select_statement``* 是任何没有\ ``ORDER BY``\ 、\ ``LIMIT``\ 、\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 以及\ ``FOR KEY SHARE``\ 子句的 ``SELECT``\ 语句。 ``EXCEPT``\ 操作符计算位于左 ``SELECT``\ 语句的结果中但不在右 ``SELECT``\ 语句结果中的行集合。 ``EXCEPT``\ 的结果不会包含重复行，除非指定了 ``ALL``\ 选项。如果有\ ``ALL``\ ，一个在左表中有 *``m``*\ 次重复并且在右表中有 *``n``*\ 次重复的行将会在结果集中出现 max(*``m``*-*``n``*,0) 次。 ``DISTINCT``\ 可以写\ ``DISTINCT``\ 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个\ ``SELECT``\ 语句中的多个 ``EXCEPT``\ 操作符会从左至右计算。 ``EXCEPT``\ 的优先级与 ``UNION``\ 相同。 当前，\ ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、 ``FOR SHARE``\ 和 ``FOR KEY SHARE``\ 不能用于\ ``EXCEPT``\ 结果或者 ``EXCEPT``\ 的任何输入。 .. container:: refsect2 :name: SQL-ORDERBY .. rubric:: ``ORDER BY`` 子句 :name: order-by-子句 可选的\ ``ORDER BY``\ 子句的形式如下： .. code:: synopsis ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ``ORDER BY``\ 子句导致结果行被按照指定的表达式排序。 如果两行按照最左边的表达式是相等的，则会根据下一个表达式比较它们， 依次类推。如果按照所有指定的表达式它们都是相等的，则它们被返回的 顺序取决于实现。 每一个\ *``expression``* 可以是输出列（\ ``SELECT``\ 列表项）的名称或 者序号，它也可以是由输入列值构成的任意表达式。 序号指的是输出列的顺序（从左至右）位置。这种特性可以为不具有唯一 名称的列定义一个顺序。这不是绝对必要的，因为总是可以使用 ``AS``\ 子句为输出列赋予一个名称。 也可以在\ ``ORDER BY``\ 子句中使用任意表达式，包括没 有出现在\ ``SELECT``\ 输出列表中的列。因此， 下面的语句是合法的： .. code:: programlisting SELECT name FROM distributors ORDER BY code; 这种特性的一个限制是一个应用在\ ``UNION``\ 、 ``INTERSECT``\ 或\ ``EXCEPT``\ 子句结果上的 ``ORDER BY``\ 只能指定输出列名称或序号，但不能指定表达式。 如果一个\ ``ORDER BY``\ 表达式是一个既匹配输出列名称又匹配 输入列名称的简单名称，\ ``ORDER BY``\ 将把它解读成输出列名 称。这与在同样情况下\ ``GROUP BY``\ 会做出的选择相反。这种 不一致是为了与 SQL 标准兼容。 可以为\ ``ORDER BY``\ 子句中的任何表达式之后增加关键词 ``ASC``\ （上升）\ ``DESC``\ （下降）。如果没有指定， ``ASC``\ 被假定为默认值。或者，可以在\ ``USING`` 子句中指定一个特定的排序操作符名称。一个排序操作符必须是某个 B-树操作符族的小于或者大于成员。\ ``ASC``\ 通常等价于 ``USING <``\ 而\ ``DESC``\ 通常等价于 ``USING >``\ （但是一种用户定义数据类型的创建者可以 准确地定义默认排序顺序是什么，并且它可能会对应于其他名称的操作符）。 如果指定\ ``NULLS LAST``\ ，空值会排在非空值之后；如果指定 ``NULLS FIRST``\ ，空值会排在非空值之前。如果都没有指定， 在指定或者隐含\ ``ASC``\ 时的默认行为是\ ``NULLS LAST``\ ， 而指定或者隐含\ ``DESC``\ 时的默认行为是 ``NULLS FIRST``\ （因此，默认行为是空值大于非空值）。 当指定\ ``USING``\ 时，默认的空值顺序取决于该操作符是否为 小于或者大于操作符。 注意顺序选项只应用到它们所跟随的表达式上。例如 ``ORDER BY x, y DESC``\ 和 ``ORDER BY x DESC, y DESC``\ 是不同的。 字符串数据会被根据引用到被排序列上的排序规则排序。根据需要可以通过在 *``expression``*\ 中包括一个 ``COLLATE``\ 子句来覆盖，例如 ``ORDER BY mycolumn COLLATE "en_US"``\ 。 .. container:: refsect2 :name: SQL-LIMIT .. rubric:: ``LIMIT`` 子句 :name: limit-子句 ``LIMIT``\ 子句由两个独立的子句构成： .. code:: synopsis LIMIT { count | ALL } OFFSET start *``count``*\ 指定要返回 的最大行数，而\ *``start``* 指定在返回行之前要跳过的行数。在两者都被指定时，在开始计算要返回的 *``count``*\ 行之前会跳过 *``start``*\ 行。 如果\ *``count``*\ 表达式计算 为 NULL，它会被当成\ ``LIMIT ALL``\ ，即没有限制。如果 *``start``*\ 计算为 NULL，它会被当作\ ``OFFSET 0``\ 。 SQL:2008 引入了一种不同的语法来达到相同的结果， PostgreSQL也支持它： .. code:: synopsis OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY 在这种语法中，标准要求\ *``start``*\ 或\ *``count``*\ 是一个文本常量、一个参数或者一个变量名。而作为一种 PostgreSQL的扩展，还允许其他的表达式，但通常需要被封闭在圆括号中以避免歧义。如果在一个 ``FETCH``\ 子句中省略 *``count``*\ ，它的默认值为 1。 ``ROW``\ 和\ ``ROWS``\ 以及 ``FIRST``\ 和\ ``NEXT``\ 是噪声，它们不影响 这些子句的效果。根据标准，如果都存在，\ ``OFFSET``\ 子句 必须出现在\ ``FETCH``\ 子句之前。但是 PostgreSQL更宽松，它允许两种顺序。 在使用\ ``LIMIT``\ 时，用一个\ ``ORDER BY``\ 子句把 结果行约束到一个唯一顺序是个好办法。否则你讲得到该查询结果行的 一个不可预测的子集 — 你可能要求从第 10 到第 20 行，但是在 什么顺序下的第 10 到第 20 呢？除非指定\ ``ORDER BY``\ ，你 是不知道顺序的。 查询规划器在生成一个查询计划时会考虑\ ``LIMIT``\ ，因此 根据你使用的\ ``LIMIT``\ 和\ ``OFFSET``\ ，你很可能 得到不同的计划（得到不同的行序）。所以，使用不同的 ``LIMIT``/``OFFSET``\ 值来选择一个查询结果的 不同子集\ *将会给出不一致的结果*\ ，除非你 用\ ``ORDER BY``\ 强制一种可预测的结果顺序。这不是一个 缺陷，它是 SQL 不承诺以任何特定顺序（除非使用 ``ORDER BY``\ 来约束顺序）给出一个查询结果这一事实造 成的必然后果。 如果没有一个\ ``ORDER BY``\ 来强制选择一个确定的子集， 重复执行同样的\ ``LIMIT``\ 查询甚至可能会返回一个表中行 的不同子集。同样，这也不是一种缺陷，再这样一种情况下也无法 保证结果的确定性。 .. container:: refsect2 :name: SQL-FOR-UPDATE-SHARE .. rubric:: 锁定子句 :name: 锁定子句 ``FOR UPDATE``\ 、\ ``FOR NO KEY UPDATE``\ 、 ``FOR SHARE``\ 和\ ``FOR KEY SHARE`` 是\ *锁定子句*\ ，它们影响\ ``SELECT`` 把行从表中取得时如何对它们加锁。 锁定子句的一般形式： .. code:: synopsis FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] 其中\ *``lock_strength``*\ 可以是 .. code:: synopsis UPDATE NO KEY UPDATE SHARE KEY SHARE 为了防止该操作等待其他事务提交，可使用\ ``NOWAIT``\ 或者 ``SKIP LOCKED``\ 选项。使用\ ``NOWAIT``\ 时， 如果选中的行不能被立即锁定，该语句会报告错误而不是等待。使用 ``SKIP LOCKED``\ 时，无法被立即锁定的任何选中行都 会被跳过。跳过已锁定行会提供数据的一个不一致的视图，因此这不适合 于一般目的的工作，但是可以被用来避免多个用户访问一个类似队列的表 时出现锁竞争。注意\ ``NOWAIT``\ 和 ``SKIP LOCKED``\ 只适合行级锁 — 所要求的 ``ROW SHARE``\ 表级锁仍然会以常规的方式（见 `章 `__\ ）取得。如果想要不等待的表级锁，你可以先 使用带\ ``NOWAIT``\ 的\ `LOCK `__\ 。 如果在一个锁定子句中提到了特定的表，则只有来自于那些表的 行会被锁定，任何\ ``SELECT``\ 中用到的 其他表还是被简单地照常读取。一个没有表列表的锁定子句会影响 该语句中用到的所有表。如果一个锁定子句被应用到一个视图或者 子查询，它会影响在该视图或子查询中用到的所有表。不过，这些 子句不适用于主查询引用的\ ``WITH``\ 查询。如果你希望 在一个\ ``WITH``\ 查询中发生行锁定，应该在该 ``WITH``\ 查询内指定一个锁定子句。 如果有必要对不同的表指定不同的锁定行为，可以写多个锁定子句。 如果同一个表在多于一个锁定子句中被提到（或者被隐式的影响到）， 那么会按照所指定的最强的锁定行为来处理它。类似地，如果在任何 影响一个表的子句中指定了\ ``NOWAIT``\ ，就会按照 ``NOWAIT``\ 的行为来处理该表。否则如果 ``SKIP LOCKED``\ 在任何影响该表的子句中被指定， 该表就会被按照\ ``SKIP LOCKED``\ 来处理。 如果被返回的行无法清晰地与表中的行保持一致，则不能使用锁定子句。 例如锁定子句不能与聚集一起使用。 当一个锁定子句出现在一个\ ``SELECT``\ 查询的顶层时， 被锁定的行正好就是该查询返回的行。在连接查询的情况下，被锁定 的行是那些对返回的连接行有贡献的行。此外，自该查询的快照起满足 查询条件的行将被锁定，如果它们在该快照后被更新并且不再满足 查询条件，它们将不会被返回。如果使用了\ ``LIMIT``\ ，只要 已经返回的行数满足了限制，锁定就会停止（但注意被 ``OFFSET``\ 跳过的行将被锁定）。类似地，如果在一个游标 的查询中使用锁定子句，只有被该游标实际取出或者跳过的行才将被 锁定。 当一个锁定子句出现在一个子-``SELECT``\ 中时，被锁定 行是那些该子查询返回给外层查询的行。这些被锁定的行的数量可能比 从子查询自身的角度看到的要少，因为来自外层查询的条件可能会被用 来优化子查询的执行。例如： .. code:: programlisting SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5; 将只锁定具有\ ``col1 = 5``\ 的行（虽然在子查询中并没有写上 该条件）。 早前的发行无法维持一个被之后的保存点升级的锁。例如，这段代码： .. code:: programlisting BEGIN; SELECT * FROM mytable WHERE key = 1 FOR UPDATE; SAVEPOINT s; UPDATE mytable SET ... WHERE key = 1; ROLLBACK TO s; 在\ ``ROLLBACK TO``\ 之后将无法维持 ``FOR UPDATE``\ 锁。在 9.3 中已经修复这个问题。 .. caution:: .. rubric:: 小心 :name: 小心 :class: title 一个运行在\ ``READ COMMITTED``\ 事务隔离级别并且使用\ ``ORDER BY``\ 和锁定子句的\ ``SELECT``\ 命令有可能返回无序的行。 这是因为\ ``ORDER BY``\ 会被首先应用。该命令对结果排序，但是可能 接着在尝试获得一个或者多个行上的锁时阻塞。一旦\ ``SELECT``\ 解除 阻塞，某些排序列值可能已经被修改，从而导致那些行变成无序的（尽管它们根 据原始列值是有序的）。根据需要，可以通过在子查询中放置 ``FOR UPDATE/SHARE``\ 来解决之一问题，例如 .. code:: programlisting SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1; 注意这将导致锁定\ ``mytable``\ 的所有行，而顶层的 ``FOR UPDATE``\ 只会锁定实际被返回的行。这可能会导致显著的 性能差异，特别是把\ ``ORDER BY``\ 与\ ``LIMIT``\ 或者其他 限制组合使用时。因此只有在并发更新排序列并且要求严格的排序结果时才推 荐使用这种技术。 在\ ``REPEATABLE READ``\ 或者\ ``SERIALIZABLE`` 事务隔离级别上这可能导致一个序列化失败（\ ``SQLSTATE`` 是\ ``'40001'``\ ），因此在这些隔离级别下不可能收到无序行。 .. container:: refsect2 :name: SQL-TABLE .. rubric:: ``TABLE`` 命令 :name: table-命令 命令 .. code:: programlisting TABLE name 等价于 .. code:: programlisting SELECT * FROM name 它可以被用作一个顶层命令，或者用在复杂查询中以节省空间。只有 ``WITH``\ 、 ``UNION``\ 、\ ``INTERSECT``\ 、\ ``EXCEPT``\ 、 ``ORDER BY``\ 、\ ``LIMIT``\ 、\ ``OFFSET``\ 、 ``FETCH``\ 以及\ ``FOR``\ 锁定子句可以用于 ``TABLE``\ 。不能使用\ ``WHERE``\ 子句和任何形式 的聚集。 .. container:: refsect1 :name: id-1.9.3.171.9 .. rubric:: 示例 :name: 示例 把表\ ``films``\ 与表 ``distributors``\ 连接： .. code:: programlisting SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d, films f WHERE f.did = d.did title | did | name | date_prod | kind -------------------+-----+--------------+------------+---------- The Third Man | 101 | British Lion | 1949-12-23 | Drama The African Queen | 101 | British Lion | 1951-08-11 | Romantic ... 要对所有电影的\ ``len``\ 列求和并且用 ``kind``\ 对结果分组： .. code:: programlisting SELECT kind, sum(len) AS total FROM films GROUP BY kind; kind | total ----------+------- Action | 07:34 Comedy | 02:58 Drama | 14:28 Musical | 06:42 Romantic | 04:38 要对所有电影的\ ``len``\ 列求和、对结果按照 ``kind``\ 分组并且显示总长小于 5 小时的分组： .. code:: programlisting SELECT kind, sum(len) AS total FROM films GROUP BY kind HAVING sum(len) < interval '5 hours'; kind | total ----------+------- Comedy | 02:58 Romantic | 04:38 下面两个例子都是根据第二列（\ ``name``\ ）的内容来排序结果： .. code:: programlisting SELECT * FROM distributors ORDER BY name; SELECT * FROM distributors ORDER BY 2; did | name -----+------------------ 109 | 20th Century Fox 110 | Bavaria Atelier 101 | British Lion 107 | Columbia 102 | Jean Luc Godard 113 | Luso films 104 | Mosfilm 103 | Paramount 106 | Toho 105 | United Artists 111 | Walt Disney 112 | Warner Bros. 108 | Westward 接下来的例子展示了如何得到表\ ``distributors``\ 和 ``actors``\ 的并集，把结果限制为那些在每个表中以 字母 W 开始的行。只想要可区分的行，因此省略了关键词 ``ALL``\ 。 .. code:: programlisting distributors: actors: did | name id | name -----+-------------- ----+---------------- 108 | Westward 1 | Woody Allen 111 | Walt Disney 2 | Warren Beatty 112 | Warner Bros. 3 | Walter Matthau ... ... SELECT distributors.name FROM distributors WHERE distributors.name LIKE 'W%' UNION SELECT actors.name FROM actors WHERE actors.name LIKE 'W%'; name ---------------- Walt Disney Walter Matthau Warner Bros. Warren Beatty Westward Woody Allen 这个例子展示了如何在\ ``FROM``\ 子句中使用函数， 分别使用和不使用列定义列表： .. code:: programlisting CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors(111); did | name -----+------------- 111 | Walt Disney CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors_2(111) AS (f1 int, f2 text); f1 | f2 -----+------------- 111 | Walt Disney 这里是带有增加的序数列的函数的例子： .. code:: programlisting SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; unnest | ordinality --------+---------- a | 1 b | 2 c | 3 d | 4 e | 5 f | 6 (6 rows) 这个例子展示了如何使用简单的\ ``WITH``\ 子句： .. code:: programlisting WITH t AS ( SELECT random() as x FROM generate_series(1, 3) ) SELECT * FROM t UNION ALL SELECT * FROM t x -------------------- 0.534150459803641 0.520092216785997 0.0735620250925422 0.534150459803641 0.520092216785997 0.0735620250925422 注意该\ ``WITH``\ 查询只被计算一次，这样我们得到的两个 集合具有相同的三个随机值。 这个例子使用\ ``WITH RECURSIVE``\ 从一个只显示 直接下属的表中寻找雇员 Mary 的所有下属（直接的或者间接的）以及他们的间接层数： .. code:: programlisting WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS ( SELECT 1, employee_name, manager_name FROM employee WHERE manager_name = 'Mary' UNION ALL SELECT er.distance + 1, e.employee_name, e.manager_name FROM employee_recursive er, employee e WHERE er.employee_name = e.manager_name ) SELECT distance, employee_name FROM employee_recursive; 注意这种递归查询的典型形式：一个初始条件，后面跟着 ``UNION``\ ，然后是查询的递归部分。要确保 查询的递归部分最终将不返回任何行，否则该查询将无限循环（ 更多例子见\ `节 `__\ ）。 这个例子使用\ ``LATERAL``\ 为\ ``manufacturers`` 表的每一行应用一个集合返回函数\ ``get_product_names()``\ ： .. code:: programlisting SELECT m.name AS mname, pname FROM manufacturers m, LATERAL get_product_names(m.id) pname; 当前没有任何产品的制造商不会出现在结果中，因为这是一个内连接。 如果我们希望把这类制造商的名称包括在结果中，我们可以： .. code:: programlisting SELECT m.name AS mname, pname FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true; .. container:: refsect1 :name: id-1.9.3.171.10 .. rubric:: 兼容性 :name: 兼容性 当然，\ ``SELECT``\ 语句是兼容 SQL 标准的。 但是也有一些扩展和缺失的特性。 .. container:: refsect2 :name: id-1.9.3.171.10.3 .. rubric:: 省略的\ ``FROM``\ 子句 :name: 省略的from子句 PostgreSQL允许我们省略 ``FROM``\ 子句。一种简单的使用是计算简单表达式 的结果： .. code:: programlisting SELECT 2+2; ?column? ---------- 4 某些其他SQL数据库需要引入一个假的 单行表放在该\ ``SELECT``\ 的 ``FROM``\ 子句中才能做到这一点。 注意，如果没有指定一个\ ``FROM``\ 子句，该查询 就不能引用任何数据库表。例如，下面的查询是非法的： .. code:: programlisting SELECT distributors.* WHERE distributors.name = 'Westward'; PostgreSQL在 8.1 之前的发行 会接受这种形式的查询，并且为该查询引用的每一个表在 ``FROM``\ 子句中隐式增加一个项。现在已经不再允许 这样做。 .. container:: refsect2 :name: id-1.9.3.171.10.4 .. rubric:: 空\ ``SELECT``\ 列表 :name: 空select列表 ``SELECT``\ 之后的输出表达式列表可以为空， 这会产生一个零列的结果表。对 SQL 标准来说这不是合法的 语法。PostgreSQL允许 它是为了与允许零列表保持一致。不过在使用 ``DISTINCT``\ 时不允许空列表。 .. container:: refsect2 :name: id-1.9.3.171.10.5 .. rubric:: 省略\ ``AS``\ 关键词 :name: 省略as关键词 在 SQL 标准中，只要新列名是一个合法的列名（就是说与任何保留关键词不同）， 就可以省略输出列名之前的可选关键词\ ``AS``\ 。 PostgreSQL要稍微严格些：只要新列名匹配 任何关键词（保留或者非保留）就需要\ ``AS``\ 。推荐的习惯是使用 ``AS``\ 或者带双引号的输出列名来防止与未来增加的关键词可能的冲突。 在\ ``FROM``\ 项中，标准和 PostgreSQL都允许省略非保留 关键词别名之前的\ ``AS``\ 。但是由于语法的歧义，这无法 用于输出列名。 .. container:: refsect2 :name: id-1.9.3.171.10.6 .. rubric:: ``ONLY``\ 和继承 :name: only和继承 在书写\ ``ONLY``\ 时，SQL 标准要求在表名周围加上圆括号，例如 ``SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ...``\ 。PostgreSQL 认为这些圆括号是可选的。 PostgreSQL允许写一个拖尾的\ ``*``\ 来 显式指定包括子表的非-``ONLY``\ 行为。而标准则不允许 这样。 （这些点同等地适用于所有支持\ ``ONLY``\ 选项的 SQL 命令）。 .. container:: refsect2 :name: id-1.9.3.171.10.7 .. rubric:: ``TABLESAMPLE``\ 子句限制 :name: tablesample子句限制 当前只在常规表和物化视图上接受\ ``TABLESAMPLE``\ 子句。 根据 SQL 标准，应该可以把它应用于任何\ ``FROM``\ 项。 .. container:: refsect2 :name: id-1.9.3.171.10.8 .. rubric:: ``FROM``\ 中的函数调用 :name: from中的函数调用 PostgreSQL允许一个函数调用被直接写作 ``FROM``\ 列表的一个成员。在 SQL 标准中，有必要把这样一个函数 调用包裹在一个子-``SELECT``\ 中。也就是说，语法 ``FROM func``\ (...) *``alias``* 近似等价于 ``FROM LATERAL (SELECT func``\ (...)) *``alias``*\ 。 注意该\ ``LATERAL``\ 被认为是隐式的，这是因为标准对于 ``FROM``\ 中的一个\ ``UNNEST()``\ 项要求 ``LATERAL``\ 语义。PostgreSQL会把 ``UNNEST()``\ 和其他集合返回函数同样对待。 .. container:: refsect2 :name: id-1.9.3.171.10.9 .. rubric:: ``GROUP BY``\ 和\ ``ORDER BY``\ 可用的名字空间 :name: group-by和order-by可用的名字空间 在 SQL-92 标准中，一个\ ``ORDER BY``\ 子句只能使用输出 列名或者序号，而一个\ ``GROUP BY``\ 子句只能使用基于输 入列名的表达式。PostgreSQL扩展了 这两种子句以允许它们使用其他的选择（但如果有歧义时还是使用标准的 解释）。PostgreSQL也允许两种子句 指定任意表达式。注意出现在一个表达式中的名称将总是被当做输入列名而 不是输出列名。 SQL:1999 及其后的标准使用了一种略微不同的定义，它并不完全向后兼容 SQL-92。不过，在大部分的情况下， PostgreSQL会以与 SQL:1999 相同的 方式解释\ ``ORDER BY``\ 或\ ``GROUP BY``\ 表达式。 .. container:: refsect2 :name: id-1.9.3.171.10.10 .. rubric:: 函数依赖 :name: 函数依赖 只有当一个表的主键被包括在\ ``GROUP BY``\ 列表中时， PostgreSQL才识别函数依赖（允许 从\ ``GROUP BY``\ 中省略列）。SQL 标准指定了应该要识别 的额外情况。 .. container:: refsect2 :name: id-1.9.3.171.10.11 .. rubric:: ``LIMIT``\ 和\ ``OFFSET`` :name: limit和offset ``LIMIT``\ 和\ ``OFFSET``\ 子句是 PostgreSQL-特有的语法，在 MySQL也被使用。SQL:2008 标准已经 引入了具有相同功能的子句\ ``OFFSET ... FETCH {FIRST|NEXT} ...``\ （如上文 ```LIMIT`` 子句 `__\ 中所示）。这种语法 也被IBM DB2使用（ Oracle编写的应用常常使用自动生成的 ``rownum``\ 列来实现这些子句的效果，这在 PostgreSQL 中是没有的）。 .. container:: refsect2 :name: id-1.9.3.171.10.12 .. rubric:: ``FOR NO KEY UPDATE``\ 、\ ``FOR UPDATE``\ 、\ ``FOR SHARE``\ 、\ ``FOR KEY SHARE`` :name: for-no-key-updatefor-updatefor-sharefor-key-share 尽管 SQL 标准中出现了\ ``FOR UPDATE``\ ，但标准只允许它作为 ``DECLARE CURSOR``\ 的一个选项。 PostgreSQL允许它出现在任何 ``SELECT``\ 查询以及子-``SELECT``\ 中，但这是 一种扩展。\ ``FOR NO KEY UPDATE``\ 、\ ``FOR SHARE`` 以及\ ``FOR KEY SHARE``\ 变体以及\ ``NOWAIT`` 和\ ``SKIP LOCKED``\ 选项没有在标准中出现。 .. container:: refsect2 :name: id-1.9.3.171.10.13 .. rubric:: ``WITH``\ 中的数据修改语句 :name: with中的数据修改语句 PostgreSQL允许把\ ``INSERT``\ 、 ``UPDATE``\ 以及\ ``DELETE``\ 用作\ ``WITH`` 查询。这在 SQL 标准中是找不到的。 .. container:: refsect2 :name: id-1.9.3.171.10.14 .. rubric:: 非标准子句 :name: 非标准子句 ``DISTINCT ON ( ... )``\ 是 SQL 标准的扩展。 ``ROWS FROM( ... )``\ 是 SQL 标准的扩展。 ``WITH``\ 的\ ``MATERIALIZED`` 和 ``NOT MATERIALIZED`` 选项是SQL标准的扩展。