SELECT

功能描述

SELECT用于从表或视图中取出数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

注意事项

  • 表的所有者、拥有表SELECT权限的用户或拥有SELECT ANY TABLE权限的用户，有权读取表或视图中数据，系统管理员默认拥有此权限。
  • 必须对每个在SELECT命令中使用的字段有SELECT权限。
  • 使用FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE还要求UPDATE权限。

语法格式

  • 查询数据
1. [ WITH [ RECURSIVE ] with_query [, ...] ]
2. SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
3. { * | {expression [ [ AS ] output_name ]} [, ...] }
4. [ into_option ]
5. [ FROM from_item [, ...] ]
6. [ WHERE condition ]
7. [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
8. [ GROUP BY grouping_element [, ...] ]
9. [ HAVING condition [, ...] ]
10. [ WINDOW {window_name AS ( window_definition )} [, ...] ]
11. [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
12. [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
13. [ LIMIT { [offset,] count | ALL } ]
14. [ OFFSET start [ ROW | ROWS ] ]
15. [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
16. [ into_option ]
17. [ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N ]} [...] ]
18. [into_option];

SELECT - 图1 说明：

condition和expression中可以使用targetlist中表达式的别名。

  • 只能同一层引用。
  • 只能引用targetlist中的别名。
  • 只能是后面的表达式引用前面的表达式。
  • 不能包含volatile函数。
  • 不能包含Window function函数。
  • 不支持在join on条件中引用别名。
  • targetlist中有多个要应用的别名则报错。
  • 其中子查询with_query为：

1. with_query_name [ ( column_name [, ...] ) ] 2. AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} ) - 其中into子句为：

1. into_option: { 2. INTO var_name [, var_name] ... 3. | INTO OUTFILE 'file_name' 4. [CHARACTER SET charset_name] 5. export_options 6. | INTO DUMPFILE 'file_name' 7. } 8. export_options: { 9. [FIELDS 10. [TERMINATED BY 'string'] 11. [[OPTIONALLY] ENCLOSED BY 'char'] 12. [ESCAPED BY 'char' ] 13. ] 14. [LINES 15. [STARTING BY 'string'] 16. [TERMINATED BY 'string'] 17. ] 18. } - 其中指定查询源from_item为：

1. {[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] 2. [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ] 3. [ TIMECAPSULE {TIMESTAMP | CSN} expression ] 4. |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] 5. |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] 6. |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] 7. |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) 8. |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]} - 其中group子句为：

1. ( ) 2. | expression 3. | ( expression [, ...] ) 4. | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) 5. | CUBE ( { expression | ( expression [, ...] ) } [, ...] ) 6. | GROUPING SETS ( grouping_element [, ...] ) - 其中指定分区partition_clause为：

1. PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } | 2. SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] )}

SELECT - 图2 说明：

指定分区只适合分区表。 - 其中设置排序方式nlssort_expression_clause为：

1. NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' ) 2. 其中，第二个参数可选generic_m_ci，仅支持纯英文不区分大小写排序。 - 简化版查询语法，功能相当于select * from table_name。

1. TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

参数说明

  • WITH [ RECURSIVE ] with_query [, …]

用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于只在主查询中存在的临时表，其可以将复杂的查询简化。

其中with_query的详细格式为

1. with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

  • with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问子查询的结果集。
  • column_name指定子查询结果集中显示的列名。
  • 每个子查询可以是SELECT，VALUES，INSERT，UPDATE或DELETE语句。
  • 用户可以使用MATERIALIZED / NOT MATERIALIZED对CTE进行修饰。
    • 如果声明为MATERIALIZED，WITH查询将被物化，生成一个子查询结果集的拷贝，在引用处直接查询该拷贝，因此WITH子查询无法和主干SELECT语句进行联合优化（如谓词下推、等价类传递等），对于此类场景可以使用NOT MATERIALIZED进行修饰，如果WITH查询语义上可以作为子查询内联执行，则可以进行上述优化。
    • 如果用户没有显示声明物化属性则遵守以下规则：如果CTE只在所属SELECT主干中被引用一次，且语义上支持内联执行，则会被改写为子查询内联执行，否则以CTE Scan的方式物化执行。

如果声明了RECURSIVE，那么允许AS后的SELECT子查询通过名称引用自己，详细格式为：

1. non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

  • 即由非递归项、UNION、递归项组成
  • 只有递归项中可以引用自己
  • 每个查询中只允许一个递归自引用
  • plan_hint子句

以/*+ */的形式在SELECT关键字后，用于对SELECT对应的语句块生成的计划进行hint调优，详细用法请参见章节使用Plan Hint进行调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。 - ALL

声明返回所有符合条件的行，是默认行为，可以省略该关键字。 - DISTINCT [ ON ( expression [, …] ) ]

从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。

ON ( expression [, …] ) 只保留那些在给出的表达式上运算出相同结果的行集合中的第一行。

SELECT - 图3 须知：

DISTINCT ON表达式是使用与ORDER BY相同的规则进行解释的。除非使用了ORDER BY来保证需要的行首先出现，否则，“第一行” 是不可预测的。 - SELECT列表

指定查询表中列名，可以是部分列或者是全部（使用通配符*表示）。

通过使用子句AS output_name可以为输出字段取个别名，这个别名通常用于输出字段的显示。支持关键字name、value和type作为列别名。

列名可以用下面几种形式表达：

  • 手动输入列名，多个列之间用英文逗号（,）分隔。
  • 可以是FROM子句里面计算出来的字段。
  • INTO子句

将select出的结果输出到指定用户自定义变量或文件。

  • var_name

    用户自定义的变量名。详见SET章节中的var_name。 - OUTFILE

    • CHARACTER SET 指定编码格式。
    • FIELDS 指定每个字段的属性：

    TERMINATED 指定间隔符。

    [OPTIONALLY] ENCLOSED 指定引号符，指定OPTIONALLY时只对字符串数据类型起作用。

    ESCAPED 指定转义符。 - LINES 指定行属性：

    STARTING 指定行开头。

    TERMINATED 指定行结尾。 - DUMPFILE

    导出无间隔符，无换行符的单行数据到文件。 - file_name

    指定文件的绝对路径。

into_option三处位置： –在from子句之前。 openGauss=# select * into @my_var from t; –在锁定子句之前。 openGauss=# select * from t into @my_var for update; –在select语句结尾。 openGauss=# select * from t for update into @my_var;

1. 导出到文件：
2. openGauss=#  select * from t；
3. a | b
4. ---+---
5. 1 | a
6. (1 row)
7. --导出数据到outfile文件。
8. openGauss=#  select * from t into outfile '/home/openGauss/t.txt'FIELDS TERMINATED BY '~' ENCLOSED BY 't' ESCAPED BY '^' LINES STARTING BY '$' TERMINATED BY '&\n';
9. 文件内容：$t1t~tat&，其中LINES STARTING BY($),FIELDS TERMINATED BY(~),ENCLOSED BY(t),LINES TERMINATED BY(&\n)。
10. --导出数据到dumpfile文件。
11. openGauss=#  select * from t into dumpfile '/home/openGauss/t.txt';
12. 文件内容：1a

2. -   **FROM子句**

4. 为SELECT声明一个或者多个源表。

6. FROM子句涉及的元素如下所示。

8. -   table\_name

10. 表名或视图名，名称前可加上模式名，如：schema\_name.table\_name。

12. -   alias

14. 给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。

16. 别名用于缩写或者在自连接中消除歧义。如果提供了别名，它就会完全隐藏表的实际名称。

18. -   TABLESAMPLE _sampling\_method_ ( _argument_ \[, …\] ) \[ REPEATABLE ( _seed_ ) \]

20. \_table\_name\_之后的TABLESAMPLE子句表示应该用指定的\_sampling\_method\_来检索表中行的子集。

22. 可选的REPEATABLE子句指定一个用于产生采样方法中随机数的\_种子\_数。种子值可以是任何非空常量值。如果查询时表没有被更改，指定相同种子和\_argument\_值的两个查询将会选择该表相同的采样。但是不同的种子值通常将会产生不同的采样。如果没有给出REPEATABLE，则会基于一个系统产生的种子为每一个查询选择一个新的随机采样。

24. -   TIMECAPSULE { TIMESTAMP | CSN } expression

26. 查询指定CSN点或者指定时间点表的内容。

28. 目前不支持闪回查询的表：系统表、列存表、内存表、DFS表、全局临时表、本地临时表、UNLOGGED表、视图、序列表、hashbucket表、共享表、继承表、带有PARTIAL CLUSTER KEY约束的表。

30. -   TIMECAPSULE TIMESTAMP

32. 关键字，闪回查询的标识，根据date日期，闪回查找指定时间点的结果集。date日期必须是一个过去有效的时间戳。

34. -   TIMECAPSULE CSN

36. 关键字，闪回查询的标识，根据表的CSN闪回查询指定CSN点的结果集。其中CSN可从gs\_txn\_snapshot记录的snpcsn号查得。

38. > ![](/projects/opengauss-lite-5.0.0-zh/14a8eff9282c75cd93de7a24f6ed8cef.gif) **说明：**
39. >
40. > -   闪回查询不能跨越影响表结构或物理存储的语句，否则会报错。即闪回点和当前点之间，如果执行过修改表结构或影响物理存储的语句（TRUNCATE、DDL、DCL、VACUUM FULL），则闪回失败。
41. > -   闪回点过旧时，因闪回版本被回收等导致无法获取旧版本会导致闪回失败，报错：Restore point too old。
42. > -   通过时间方式指定闪回点，闪回数据和实际时间点最多偏差为3秒。
43. > -   对表执行truncate之后，再进行闪回查询或者闪回表操作。通过时间点进行的闪回操作会报错：Snapshot too old。通过CSN进行的闪回操作会找不到数据，或者报错：Snapshot too old。

45. -   column\_alias

47. 列别名

49. -   PARTITION

51. 查询分区表的某个分区的数据。

53. -   partition\_name

55. 分区名。

57. -   partition\_value

59. 指定的分区键值。在创建分区表时，如果指定了多个分区键，可以通过PARTITION FOR子句指定的这一组分区键的值，唯一确定一个分区。

61. -   SUBPARTITION

63. 查询分区表的某个二级分区的数据。

65. -   subpartition\_name

67. 二级分区名。

69. -   subpartition\_value

71. 指定的一级分区和二级分区键值。可以通过SUBPARTITION FOR子句指定的两个分区键的值，唯一确定一个二级分区。

73. -   subquery

75. FROM子句中可以出现子查询，创建一个临时表保存子查询的输出。

77. -   with\_query\_name

79. WITH子句同样可以作为FROM子句的源，可以通过WITH查询的名称对其进行引用。

81. -   function\_name

83. 函数名称。函数调用也可以出现在FROM子句中。

85. -   join\_type

87. 有5种类型，如下所示。

89. -   \[ INNER \] JOIN

91. 一个JOIN子句组合两个FROM项。可使用圆括弧以决定嵌套的顺序。如果没有圆括弧，JOIN从左向右嵌套。

93. 在任何情况下，JOIN都比逗号分隔的FROM项绑定得更紧。

95. -   LEFT \[ OUTER \] JOIN

97. 返回笛卡尔积中所有符合连接条件的行，再加上左表中通过连接条件没有匹配到右表行的那些行。这样，左边的行将扩展为生成表的全长，方法是在那些右表对应的字段位置填上NULL。请注意，只在计算匹配的时候，才使用JOIN子句的条件，外层的条件是在计算完毕之后施加的。

99. -   RIGHT \[ OUTER \] JOIN

101. 返回所有内连接的结果行，加上每个不匹配的右边行（左边用NULL扩展）。

103. 这只是一个符号上的方便，因为总是可以把它转换成一个LEFT OUTER JOIN，只要把左边和右边的输入互换位置即可。

105. -   FULL \[ OUTER \] JOIN

107. 返回所有内连接的结果行，加上每个不匹配的左边行（右边用NULL扩展），再加上每个不匹配的右边行（左边用NULL扩展）。

109. -   CROSS JOIN

111. CROSS JOIN等效于INNER JOIN ON（TRUE） ，即没有被条件删除的行。这种连接类型只是符号上的方便，因为它们与简单的FROM和WHERE的效果相同。

113. > ![](/projects/opengauss-lite-5.0.0-zh/14a8eff9282c75cd93de7a24f6ed8cef.gif) **说明：**
114. >
115. > 必须为INNER和OUTER连接类型声明一个连接条件，即NATURAL ON，join\_condition，USING (join\_column \[， …\]) 之一。但是它们不能出现在CROSS JOIN中。

118. ​ 其中CROSS JOIN和INNER JOIN生成一个简单的笛卡尔积，和在FROM的顶层列出两个项的结果相同。

120. -   ON join\_condition

122. 连接条件，用于限定连接中的哪些行是匹配的。如：ON left\_table.a = right\_table.a。不建议使用int等数值类型作为join\_condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

124. -   USING(join\_column\[，…\])

126. ON left\_table.a = right\_table.a AND left\_table.b = right\_table.b … 的简写。要求对应的列必须同名。

128. -   NATURAL

130. NATURAL是具有相同名称的两个表的所有列的USING列表的简写。

132. -   from item

134. 用于连接的查询源对象的名称。

136. -   **WHERE子句**

138. WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。condition是返回值为布尔型的任意表达式，任何不满足该条件的行都不会被检索。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

140. WHERE子句中可以通过指定"(+)“操作符的方法将表的连接关系转换为外连接。但是不建议用户使用这种用法，因为这并不是SQL的标准语法，在做平台迁移的时候可能面临语法兼容性的问题。同时，使用”(+)“有很多限制：

142. 1.  “(+)“只能出现在where子句中。
143. 2.  如果from子句中已经有指定表连接关系，那么不能再在where子句中使用”(+)"。
144. 3.  “(+)“只能作用在表或者视图的列上，不能作用在表达式上。
145. 4.  如果表A和表B有多个连接条件，那么必须在所有的连接条件中指定”(+)"，否则”(+)“将不会生效，表连接会转化成内连接，并且不给出任何提示信息。
146. 5.  “(+)“作用的连接条件中的表不能跨查询或者子查询。如果”(+)“作用的表，不在当前查询或者子查询的from子句中，则会报错。如果”(+)“作用的对端的表不存在，则不报错，同时连接关系会转化为内连接。
147. 6.  “(+)“作用的表达式不能直接通过"OR"连接。
148. 7.  如果”(+)“作用的列是和一个常量的比较关系， 那么这个表达式会成为join条件的一部分。
149. 8.  同一个表不能对应多个外表。
150. 9.  “(+)“只能出现"比较表达式”，“NOT表达式”，“ANY表达式”，“ALL表达式”，“IN表达式”，“NULLIF表达式”，“IS DISTINCT FROM表达式”，“IS OF”表达式。”(+)“不能出现在其他类型表达式中，并且这些表达式中不允许出现通过“AND”和“OR”连接的表达式。
151. 10.  “(+)“只能转化为左外连接或者右外连接，不能转化为全连接，即不能在一个表达式的两个表上同时指定”(+)”

153. > ![](/projects/opengauss-lite-5.0.0-zh/16f0a95e2d09c1b82b68f57e87ec31b6.gif) **须知：**
154. >
155. > 对于WHERE子句的LIKE操作符，当LIKE中要查询特殊字符“%”、“\_”、“\\”的时候需要使用反斜杠“\\”来进行转义。

157. -   **START WITH子句**

159. START WITH子句通常与CONNECT BY子句同时出现，数据进行层次递归遍历查询，START WITH代表递归的初始条件。若省略该子句，单独使用CONNECT BY子句，则表示以表中的所有行作为初始集合。

161. -   **CONNECT BY子句**

163. CONNECT BY代表递归连接条件，CONNECT BY条件中可以对列指定PRIOR关键字代表以这列为递归键进行递归。当前约束只能对表中的列指定PRIOR，不支持对表达式、类型转换指定PRIOR关键字。若在递归连接条件前加NOCYCLE，则表示遇到循环记录时停止递归。（注：含START WITH .. CONNECT BY子句的SELECT语句不支持使用FOR KEY SHARE/SHARE/NO KEY UPDATE/UPDATE锁）

1. openGauss=# create table test(name varchar, id int, fatherid int);
2. openGauss=# insert into test values('A', 1, 0), ('B', 2, 1), ('C', 3, 1), ('D', 4, 1), ('E', 5, 2);
3. select * from test start with id = 1 connect by prior id = fatherid order siblings by id desc;
4. name | id | fatherid
5. ------+----+----------
6. A    |  1 |        0
7. D    |  4 |        1
8. C    |  3 |        1
9. B    |  2 |        1
10. E    |  5 |        2
11. (5 rows)
12. ```

14. Start with语句的执行流程是：

16. 1.  由 start with 区域的条件选择初始的数据集。上述例子里，先把 ('A', 1, 0) 选择出来。然后把初始的数据集设置为工作集。
17. 2.  只要工作集不为空，会用工作集的数据作为输入，查询下一轮的数据，过滤条件有 conntect by 区域指定。其中，PRIOR关键字表示当前记录，比如上位例子中 prior id = fatherid 表示当前记录的 id 是下一条记录的 fatherid。
18. 3.  把2中筛选出来的数据集，设为工作集，返回第二步重复操作。

20. 同时，数据库为每一条选出来的数据添加下述的伪列，方便用户了解数据在递归或者树状结构中的位置。

22. -   LEVEL：节点的层级
23. -   CONNECT\_BY\_ISLEAF：是否为叶子结点

25. 除了伪列外，还提供下述的查询函数

27. -   sys\_connect)by\_path(col, separtor)：返回从根节点到当前行的连接路径。参数col为路径中显示的列的名称，separator为连接符。
28. -   connect\_by\_root(col)：显示该节点最顶级的节点，col为输出列的名称。

30. 当前Start with默认行为是宽度优先搜索，但可以通过和伪列配合，实现深度优先搜索。比如：

32. ```
33. openGauss=# select sys_connect_by_path(name,'-') as path,*,LEVEL from test start with id = 1 connect by fatherid=prior id order by path;
34. path  | name | id | fatherid | level
35. --------+------+----+----------+-------
36. -A     | A    |  1 |        0 |     1
37. -A-B   | B    |  2 |        1 |     2
38. -A-B-E | E    |  5 |        2 |     3
39. -A-C   | C    |  3 |        1 |     2
40. -A-D   | D    |  4 |        1 |     2
41. (5 rows)
42. ```
  • ORDER SIBLINGS BY子句

ORDER SIBLINGS BY通常和START WITH、CONNECT BY子句同时使用, 用法和ORDER BY子句一样, 用于在递归过程中的层级排序。当前不支持接函数调用，如果有函数调用，且函数参数中存在列，则取第一个列作为排序键排序。 - GROUP BY子句

将查询结果按某一列或多列的值分组，值相等的为一组。

  • GROUPING SETS ( grouping_element [, …] )

    GROUPING SETS子句是GROUP BY子句的进一步扩展，它可以使用户指定多个GROUP BY选项。 这样做可以通过裁剪用户不需要的数据组来提高效率。 当用户指定了所需的数据组时，数据库不需要执行完整CUBE或ROLLUP生成的聚合集合。 - CUBE ( { expression | ( expression [, …] ) } [, …] )

    CUBE是自动对group by子句中列出的字段进行分组汇总，结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。它会为每个分组返回一行汇总信息， 用户可以使用CUBE来产生交叉表值。比如，在CUBE子句中给出三个表达式（n = 3），运算结果为2n = 23 = 8组。 以n个表达式的值分组的行称为常规行，其余的行称为超级聚集行。例如：

    CUBE (c1,c2,c3) 等效于

    1. GROUPING SETS ( 2. (c1, c2, c3), 3. (c1, c2), 4. (c2, c3), 5. (c1, c3), 6. (c1), 7. (c2), 8. (c3), 9. () 10. ) - ROLLUP ( { expression | ( expression [, …] ) } [, …] )

    ROLLUP是生成多个分组集合的快捷功能。与CUBE子句的差异是，ROLLUP不生成基于特定列所有可能的分组集合，生成分组集合为其子集。ROLLUP假设输入列之间存在层次结构，从而生成有意义的所有分组集合。例如：

    ROLLUP(c1,c2,c3)仅生成4种分组集合，等效于：

    1. GROUPING SETS ( 2. (c1, c2, c3), 3. (c1, c2), 4. (c1), 5. (), 6. )

1. > ![](/projects/opengauss-lite-5.0.0-zh/16f0a95e2d09c1b82b68f57e87ec31b6.gif) **须知：**
2. >
3. > -   如果SELECT列表的表达式中引用了那些没有分组的字段，则会报错，除非使用了聚集函数或者它函数依赖于分组的字段，因为对于未分组的字段，可能返回多个数值。如果分组的字段是包含非分组字段的表的主键（或者主键的子集），则存在函数依赖。
4. > -   如果任何GROUPING SETS、ROLLUP、CUBE作为分组字段存在，则GROUP BY子句整体上定义了数个独立的分组集。其效果等效于在子查询间构建一个UNION ALL，子查询带有分组集作为它们的GROUP BY子句。
5. > -   当前，FOR UPDATE、FOR SHARE、FOR NO KEY UPDATE、FOR KEY SHARE不能和GROUP BY子句一起指定。
  • HAVING子句

与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较，只有满足HAVING子句中的逻辑表达式的组才会被提取出来。

SELECT - 图4 须知：

当前，FOR UPDATE、FOR SHARE、FOR NO KEY UPDATE、FOR KEY SHARE不能和HAVING子句一起指定。 - WINDOW子句

一般形式为WINDOW window_name AS ( window_definition ) [， …]，window_name是可以被随后的窗口定义所引用的名称，window_definition可以是以下的形式：

[ existing_window_name ]

[ PARTITION BY expression [, …] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, …] ]

[ frame_clause ]

frame_clause为窗函数定义一个窗口帧window frame，窗函数（并非所有）依赖于帧，window frame是当前查询行的一组相关行。frame_clause可以是以下的形式：

[ RANGE | ROWS ] frame_start

[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

frame_start和frame_end可以是：

``` 1. UNBOUNDED PRECEDING //分区第一行开始的帧

  1. value PRECEDING //当前行前value行开始或结束的帧

  2. CURRENT ROW //当前行开始或结束的帧

  3. value FOLLOWING //当前行后value行开始或结束的帧

  4. UNBOUNDED FOLLOWING //分区最后一行结束的帧 ```

  5. frame_start缺省为UNBOUNDED PRECEDING，frame_end缺省为CURRENT ROW

  6. frame_end的取值在上面列出的顺序中必须晚于frame_start的取值
  7. value不可为负数或空，可以为0

    SELECT - 图5 须知：

    对列存表的查询目前只支持row_number窗口函数，不支持frame_clause。

WINDOW子句可指定窗口函数的行为，在处理窗口定义相同的窗口函数时，使用WINDOW子句，在OVER中引用，能使SQL语句更简单，例如：

1. openGauss=# SELECT sum(count) OVER w, avg(count) OVER w 2. FROM table_count 3. WINDOW w AS (PARTITION BY name ORDER BY count DESC); - UNION子句

UNION计算多个SELECT语句返回行集合的并集。

UNION子句有如下约束条件：

  • 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。
  • 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。
  • FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE和FOR KEY SHARE不能在UNION的结果或输入中声明。

一般表达式：

select_statement UNION [ALL] select_statement

  • select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE子句的SELECT语句。
  • 如果用圆括弧包围，ORDER BY和LIMIT可以附着在子表达式里。
  • INTERSECT子句

INTERSECT计算多个SELECT语句返回行集合的交集，不含重复的记录。

INTERSECT子句有如下约束条件：

  • 同一个SELECT语句中的多个INTERSECT操作符是从左向右计算的，除非用圆括弧进行了标识。
  • 当对多个SELECT语句的执行结果进行UNION和INTERSECT操作的时候，会优先处理INTERSECT。

一般形式：

select_statement INTERSECT select_statement

select_statement可以是任何没有FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE子句的SELECT语句。 - EXCEPT子句

EXCEPT子句有如下的通用形式：

select_statement EXCEPT [ ALL ] select_statement

select_statement是任何没有FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE子句的SELECT表达式。

EXCEPT操作符计算存在于左边SELECT语句的输出而不存在于右边SELECT语句输出的行。

EXCEPT的结果不包含任何重复的行，除非声明了ALL选项。使用ALL时，一个在左边表中有m个重复而在右边表中有n个重复的行将在结果中出现max(m-n,0) 次。

除非用圆括弧指明顺序，否则同一个SELECT语句中的多个EXCEPT操作符是从左向右计算的。EXCEPT和UNION的绑定级别相同。

目前，不能给EXCEPT的结果或者任何EXCEPT的输入声明FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE和FOR KEY SHARE子句。 - MINUS子句

与EXCEPT子句具有相同的功能和用法。 - ORDER BY子句

对SELECT语句检索得到的数据进行升序或降序排序。对于ORDER BY表达式中包含多列的情况：

  • 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较，依此类推。
  • 如果对于所有声明的表达式都相同，则按随机顺序返回。

  • 在与DISTINCT关键字一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。

    SELECT - 图6 例外：

    在兼容B模式下，ORDER BY 可以支持表达式，其中引用的列必须在 DISTINCT 中。

    在兼容B模式下，以下两种方式可以让 ORDER BY 引用的列不必在 DISTINCT 中：

    • DOLPHIN 插件存在时，dolphin.sql_mode 参数未设置 sql_mode_full_group 选项。
    • DOLPHIN 插件不存在时，behavior_compat_options 参数设置 allow_orderby_undistinct_column 选项。

    注意：本例外只支持DISTINCT语法，不支持DISTINCT ON语法。 - 在与GROUP BY子句一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。

1. > ![](/projects/opengauss-lite-5.0.0-zh/16f0a95e2d09c1b82b68f57e87ec31b6.gif) **须知：**
2. >
3. > 如果要支持中文拼音排序，需要在初始化数据库时指定编码格式为UTF-8、GB18030或GBK。命令如下:
4. >
5. > ```
6. > initdb –E UTF8 –D ../data –locale=zh_CN.UTF-8、initdb -E GB18030 -D ../data -locale=zh_CN.GB18030或initdb –E GBK –D ../data –locale=zh_CN.GBK。
7. > ```
  • LIMIT子句

LIMIT子句由两个独立的子句组成：

LIMIT { count | ALL }

OFFSET start count声明返回的最大行数，而start声明开始返回行之前忽略的行数。如果两个都指定了，会在开始计算count个返回行之前先跳过start行。 - OFFSET子句

SQL：2008开始提出一种不同的语法：

OFFSET start { ROW | ROWS }

start声明开始返回行之前忽略的行数。 - FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

如果不指定count，默认值为1，FETCH子句限定返回查询结果从第一行开始的总行数。 - 锁定子句

FOR UPDATE子句将对SELECT检索出来的行进行加锁。这样避免它们在当前事务结束前被其他事务修改或者删除，即其他企图UPDATE、 DELETE、 SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE, SELECT FOR SHARE 或 SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。任何在一行上的DELETE命令也会获得FOR UPDATE锁模式，在主键列上修改值的UPDATE也会获得该锁模式。反过来，SELECT FOR UPDATE将等待已经在相同行上运行以上这些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。

FOR NO KEY UPDATE行为与FOR UPDATE类似，不过获得的锁较弱，这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。

FOR SHARE的行为类似，只是它在每个检索出来的行上要求一个共享锁，而不是一个排他锁。一个共享锁阻塞其它事务执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，不阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE。

FOR KEY SHARE行为与FOR SHARE类似，不过锁较弱，SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。

为了避免操作等待其他事务提交，可使用NOWAIT选项，如果被选择的行不能立即被锁住，将会立即汇报一个错误，而不是等待；WAIT N选项，如果被选择的行不能立即被锁住，等待N秒（其中，N为int类型，取值范围：0 <= N <= 2147483），N秒内获取锁则正常执行，否则报错。

如果在锁定子句中明确指定了表名称，则只有这些指定的表被锁定，其他在SELECT中使用的表将不会被锁定。否则，将锁定该命令中所有使用的表。

如果锁定子句应用于一个视图或者子查询，它同样将锁定所有该视图或子查询中使用到的表。

多个锁定子句可以用于为不同的表指定不同的锁定模式。

如果一个表中同时出现（或隐含同时出现）在多个子句中，则按照最强的锁处理。类似的，如果影响一个表的任意子句中出现了NOWAIT，该表将按照NOWAIT处理。

SELECT - 图7 须知：

对列存表的查询不支持for update/no key update/share/key share。 对ustore表的查询只支持for share/for update，不支持for key share/for no key update。 - NLS_SORT

指定某字段按照特殊方式排序。目前仅支持中文拼音格式排序和不区分大小写排序。如果要支持此排序方式，在创建数据库时需要指定编码格式为“UTF8”、”GB18030”或“GBK”；如果指定为其他编码，例如SQL_ASCII，则可能报错或者排序无效。

取值范围：

  • SCHINESE_PINYIN_M，按照中文拼音排序。
  • generic_m_ci，不区分大小写排序（可选，仅支持纯英文不区分大小写排序）。
  • PARTITION子句

查询某个分区表中相应分区的数据。

示例

1. --先通过子查询得到一张临时表temp_t，然后查询表temp_t中的所有数据。
2. openGauss=# WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t;

4. --创建多级菜单表
5. openGauss=# CREATE TABLE exp_menu (
6. id int,
7. name text,
8. parent_id int
9. );

11. --插入数据
12. openGauss=# INSERT INTO exp_menu VALUES (1, 'grandpa', 0),(2, 'father', 1),(3, 'son', 2);

14. --使用 WITH RECURSIVE 递归查询菜单关系
15. openGauss=# WITH RECURSIVE res AS (
16. SELECT id, name, parent_id
17. FROM exp_menu
18. WHERE id = 3
19. UNION
20. SELECT m.id,
21. m.name || ' > ' || r.name,
22. m.parent_id
23. FROM res r INNER JOIN exp_menu m ON m.id = r.parent_id
24. )
25. select * from res;
26. id |          name          | parent_id
27. ----+------------------------+-----------
28. 3 | son                    |         2
29. 2 | father > son           |         1
30. 1 | grandpa > father > son |         0
31. (3 rows)

33. --查询tpcds.reason表的所有r_reason_sk记录，且去除重复。
34. openGauss=# SELECT DISTINCT(r_reason_sk) FROM tpcds.reason;

36. --LIMIT子句示例：获取表中一条记录。
37. openGauss=# SELECT * FROM tpcds.reason LIMIT 1;

39. --查询所有记录，且按字母升序排列。
40. openGauss=# SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;

42. --通过表别名，从pg_user和pg_user_status这两张表中获取数据。
43. openGauss=# SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid;

45. --FULL JOIN子句示例：将pg_user和pg_user_status这两张表的数据进行全连接显示，即数据的合集。
46. openGauss=# SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;

48. --GROUP BY子句示例：根据查询条件过滤，并对结果进行分组。
49. openGauss=# SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;

51. --GROUP BY CUBE子句示例：根据查询条件过滤，并对结果进行分组汇总。
52. openGauss=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY CUBE(r_reason_id,r_reason_sk);

54. --GROUP BY GROUPING SETS子句示例:根据查询条件过滤，并对结果进行分组汇总。
55. openGauss=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY GROUPING SETS((r_reason_id,r_reason_sk),r_reason_sk);

57. --UNION子句示例：将表tpcds.reason里r_reason_desc字段中的内容以W开头和以N开头的进行合并。
58. openGauss=# SELECT r_reason_sk, tpcds.reason.r_reason_desc
59. FROM tpcds.reason
60. WHERE tpcds.reason.r_reason_desc LIKE 'W%'
61. UNION
62. SELECT r_reason_sk, tpcds.reason.r_reason_desc
63. FROM tpcds.reason
64. WHERE tpcds.reason.r_reason_desc LIKE 'N%';

66. --NLS_SORT子句示例：中文拼音排序。
67. openGauss=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = SCHINESE_PINYIN_M');

69. --不区分大小写排序（可选，仅支持纯英文不区分大小写排序）:
70. openGauss=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = generic_m_ci');

72. --创建分区表tpcds.reason_p
73. openGauss=# CREATE TABLE tpcds.reason_p
74. (
75. r_reason_sk integer,
76. r_reason_id character(16),
77. r_reason_desc character(100)
78. )
79. PARTITION BY RANGE (r_reason_sk)
80. (
81. partition P_05_BEFORE values less than (05),
82. partition P_15 values less than (15),
83. partition P_25 values less than (25),
84. partition P_35 values less than (35),
85. partition P_45_AFTER values less than (MAXVALUE)
86. )
87. ;

89. --插入数据。
90. openGauss=# INSERT INTO tpcds.reason_p values(3,'AAAAAAAABAAAAAAA','reason 1'),(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),(20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');

92. --PARTITION子句示例：从tpcds.reason_p的表分区P_05_BEFORE中获取数据。
93. openGauss=#  SELECT * FROM tpcds.reason_p PARTITION (P_05_BEFORE);
94. r_reason_sk |   r_reason_id    |   r_reason_desc
95. -------------+------------------+------------------------------------
96. 4 | AAAAAAAABAAAAAAA | reason 3
97. 3 | AAAAAAAABAAAAAAA | reason 1
98. (2 rows)

100. --GROUP BY子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录数。
101. openGauss=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id;
102. count |   r_reason_id
103. -------+------------------
104. 2 | AAAAAAAACAAAAAAA
105. 5 | AAAAAAAABAAAAAAA
106. (2 rows)

108. --GROUP BY CUBE子句示例：根据查询条件过滤，并对查询结果分组汇总。
109. openGauss=# SELECT * FROM tpcds.reason GROUP BY  CUBE (r_reason_id,r_reason_sk,r_reason_desc);

111. --GROUP BY GROUPING SETS子句示例：根据查询条件过滤，并对查询结果分组汇总。
112. openGauss=# SELECT * FROM tpcds.reason GROUP BY  GROUPING SETS ((r_reason_id,r_reason_sk),r_reason_desc);

114. --HAVING子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录，并只显示r_reason_id个数大于2的信息。
115. openGauss=# SELECT COUNT(*) c,r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING c>2;
116. c |   r_reason_id
117. ---+------------------
118. 5 | AAAAAAAABAAAAAAA
119. (1 row)

121. --IN子句示例：按r_reason_id分组统计tpcds.reason_p表中的r_reason_id个数，并只显示r_reason_id值为 AAAAAAAABAAAAAAA或AAAAAAAADAAAAAAA的个数。
122. openGauss=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING r_reason_id IN('AAAAAAAABAAAAAAA','AAAAAAAADAAAAAAA');
123. count |   r_reason_id
124. -------+------------------
125. 5 | AAAAAAAABAAAAAAA
126. (1 row)

128. --INTERSECT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且r_reason_sk小于5的信息。
129. openGauss=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' INTERSECT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<5;
130. r_reason_sk |   r_reason_id    |     r_reason_desc
131. -------------+------------------+------------------------------------
132. 4 | AAAAAAAABAAAAAAA | reason 3
133. 3 | AAAAAAAABAAAAAAA | reason 1
134. (2 rows)

136. --EXCEPT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且去除r_reason_sk小于4的信息。
137. openGauss=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' EXCEPT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<4;
138. r_reason_sk |   r_reason_id    |      r_reason_desc
139. -------------+------------------+------------------------------------
140. 10 | AAAAAAAABAAAAAAA | reason 2
141. 10 | AAAAAAAABAAAAAAA | reason 5
142. 10 | AAAAAAAABAAAAAAA | reason 4
143. 4 | AAAAAAAABAAAAAAA | reason 3
144. (4 rows)

146. --通过在where子句中指定"(+)"来实现左连接。
147. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+)
148. order by 1 desc limit 1;
149. sr_item_sk | c_customer_id
150. ------------+---------------
151. 18000 |
152. (1 row)

154. --通过在where子句中指定"(+)"来实现右连接。
155. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk
156. order by 1 desc limit 1;
157. sr_item_sk |  c_customer_id
158. ------------+------------------
159. | AAAAAAAAJNGEBAAA
160. (1 row)

162. --通过在where子句中指定"(+)"来实现左连接，并且增加连接条件。
163. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1 order by 1  limit 1;
164. sr_item_sk | c_customer_id
165. ------------+---------------
166. 1 |
167. (1 row)

169. --不支持在where子句中指定"(+)"的同时使用内层嵌套AND/OR的表达式。
170. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where not(t1.sr_customer_sk  = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1);
171. ERROR:  Operator "(+)" can not be used in nesting expression.
172. LINE 1: ...tomer_id from store_returns t1, customer t2 where not(t1.sr_...
173. ^
174. --where子句在不支持表达式宏指定"(+)"会报错。
175. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where (t1.sr_customer_sk  = t2.c_customer_sk(+))::bool;
176. ERROR:  Operator "(+)" can only be used in common expression.

178. --where子句在表达式的两边都指定"(+)"会报错。
179. openGauss=# select t1.sr_item_sk ,t2.c_customer_id from store_returns t1, customer t2 where t1.sr_customer_sk(+)  = t2.c_customer_sk(+);
180. ERROR:  Operator "(+)" can't be specified on more than one relation in one join condition
181. HINT:  "t1", "t2"...are specified Operator "(+)" in one condition.

183. --删除表。
184. openGauss=# DROP TABLE tpcds.reason_p;

186. --闪回查询示例
187. --创建表tpcds.time_table
188. openGauss=#  create table tpcds.time_table(idx integer, snaptime timestamp, snapcsn bigint, timeDesc character(100));
189. --向表tpcds.time_table中插入记录
190. openGauss=#  INSERT INTO tpcds.time_table select 1, now(),int8in(xidout(next_csn)), 'time1' from gs_get_next_xid_csn();
191. openGauss=#  INSERT INTO tpcds.time_table select 2, now(),int8in(xidout(next_csn)), 'time2' from gs_get_next_xid_csn();
192. openGauss=#  INSERT INTO tpcds.time_table select 3, now(),int8in(xidout(next_csn)), 'time3' from gs_get_next_xid_csn();
193. openGauss=#  INSERT INTO tpcds.time_table select 4, now(),int8in(xidout(next_csn)), 'time4' from gs_get_next_xid_csn();
194. openGauss=#  select * from tpcds.time_table;

196. idx |          snaptime          | snapcsn |                                               timedesc
197. -----+----------------------------+---------+------------------------------------------------------------------------------------------------------
198. 1 | 2021-04-25 17:50:05.360326 |  107322 | time1
199. 2 | 2021-04-25 17:50:10.886848 |  107324 | time2
200. 3 | 2021-04-25 17:50:16.12921  |  107327 | time3
201. 4 | 2021-04-25 17:50:22.311176 |  107330 | time4
202. (4 rows)
203. openGauss=#  delete tpcds.time_table;
204. DELETE 4
205. openGauss=#  SELECT * FROM tpcds.time_table TIMECAPSULE TIMESTAMP to_timestamp('2021-04-25 17:50:22.311176','YYYY-MM-DD HH24:MI:SS.FF');
206. idx |          snaptime          | snapcsn |                                               timedesc
207. -----+----------------------------+---------+------------------------------------------------------------------------------------------------------
208. 1 | 2021-04-25 17:50:05.360326 |  107322 | time1
209. 2 | 2021-04-25 17:50:10.886848 |  107324 | time2
210. 3 | 2021-04-25 17:50:16.12921  |  107327 | time3
211. (3 rows)
212. openGauss=#  SELECT * FROM tpcds.time_table TIMECAPSULE CSN 107330;
213. idx |          snaptime          | snapcsn |                                               timedesc
214. -----+----------------------------+---------+------------------------------------------------------------------------------------------------------
215. 1 | 2021-04-25 17:50:05.360326 |  107322 | time1
216. 2 | 2021-04-25 17:50:10.886848 |  107324 | time2
217. 3 | 2021-04-25 17:50:16.12921  |  107327 | time3
218. (3 rows)
  • ORDER BY子句的列不在DISTINCT子句中的示例（兼容B模式下）：
1. -- 创建B库并切换
2. CREATE DATABASE mydb_b WITH DBCOMPATIBILITY 'B';
3. \c mydb_b

5. -- ORDER BY列不在DISTINCT中的示例：根据c2列进行去重，按c1列进行排序，只输出c2列
6. CREATE TABLE my_tbl(score INT, name VARCHAR(20));
7. INSERT INTO my_tbl VALUES (100, 'alice'), (90, 'john'), (99, 'bob'), (80, 'bob');

9. -- 允许ORDER BY后面的列不在DISTINCT里
10. -- 无dolphin插件时执行：（增加 allow_orderby_undistinct_column 选项即可）
11. SET behavior_compat_options = 'allow_orderby_undistinct_column';
12. -- 有dolphin插件时执行：(将 sql_mode_full_group 选项删除即可)
13. set dolphin.sql_mode = '';
14. -- 注意：openGauss只会将去重后的数据，根据ORDER BY后的列进行排序。并不会先排序再去重，也就是无法保证重复行被去除的是哪一行，
15. -- 所以当去重列与排序列的数据值没有一一对应时，可能会由于数据的插入顺序、数据量等不一致而导致最终输出结果不一样。
16. SELECT DISTINCT name FROM my_tbl ORDER BY score;