SELECT
功能描述
SELECT用于从表或视图中取出数据。
SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。
注意事项
- 表的所有者、拥有表SELECT权限的用户或拥有SELECT ANY TABLE权限的用户，有权读取表或视图中数据，系统管理员默认拥有此权限。
- 必须对每个在SELECT命令中使用的字段有SELECT权限。
- 使用FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE或FOR KEY SHARE要求用户除了SELECT权限外还需有UPDATE权限。
语法格式
- 查询数据
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ into_option ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [offset,] count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ into_option ]
[ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT n ]} [...] ]
[into_option];
condition和expression中可以使用targetlist中表达式的别名。
- 只能同一层引用。
- 只能引用targetlist中的别名。
- 只能是后面的表达式引用前面的表达式。
- 不能包含volatile函数。
- 不能包含Window function函数。
- 不支持在join on条件中引用别名。
- targetlist中有多个要应用的别名则报错。
缓存SELECT语句计划的场景下，WHERE IN候选子集不易过大，建议条件个数不要超过100，防止引发动态内存过高问题：
- WHERE IN 候选子集过大时，生成计划的内存占用会增大。
- 当拼接SQL构造的WHERE IN 子集不同，缓存计划的SQL模板无法复用。会生成大量不同的计划，且计划无法共享 ，占用大量内存。
- 其中子查询with_query为：
with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} ) - 其中into子句为：
into_option: { INTO var_name [, var_name] ... | INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' } export_options: { [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ][LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] } - 其中指定查询源from_item为：
{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ] [ TIMECAPSULE {TIMESTAMP | CSN} expression ] |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) |from_item unpivot_clause |from_item pivot_clause |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]} - 其中group子句为：
( ) | expression | ( expression [, ...] ) | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) | CUBE ( { expression | ( expression [, ...] ) } [, ...] ) | GROUPING SETS ( grouping_element [, ...] ) - 其中指定分区partition_clause为：
PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } | SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] )}
指定分区只适合分区表。
- 其中设置排序方式nlssort_expression_clause为：
NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' ) - 简化版查询语法，功能相当于SELECT * FROM table_name。
TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};
参数说明
- WITH [ RECURSIVE ] with_query [, ...]
用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。这种子查询语句结构称为CTE（Common Table Expression）结构，应用这种结构时，执行计划中将存在CTE SCAN的内容。
如果声明了RECURSIVE，那么允许SELECT子查询通过名称引用它自己。
其中with_query的详细格式为：with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )
- with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问子查询的结果集。
- column_name指定子查询结果集中显示的列名。
- 每个子查询可以是SELECT，VALUES，INSERT，UPDATE或DELETE语句。
- RECURSIVE只能出现在WITH后面，多个CTE的情况下，只需要在第一个CTE处声明RECURSIVE。
- 用户可以使用MATERIALIZED / NOT MATERIALIZED对CTE进行修饰。
- 如果声明为MATERIALIZED，WITH查询将被物化，生成一个子查询结果集的拷贝，在引用处直接查询该拷贝，因此WITH子查询无法和主干SELECT语句进行联合优化（如谓词下推、等价类传递等）。当使用NOT MATERIALIZED进行修饰时，如果WITH查询语义上可以作为子查询内联执行，则可以进行上述优化。
- 如果用户没有显式声明物化属性则遵守以下规则：如果CTE只在所属SELECT主干中被引用一次，且语义上支持内联执行，则会被改写为子查询内联执行，否则以CTE Scan的方式物化执行。
- plan_hint子句
以/*+ */的形式在SELECT关键字后，用于对SELECT对应的语句块生成的计划进行hint调优，详细用法请参见章节使用Plan Hint进行调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。
- ALL
声明返回所有符合条件的行，是默认行为，可以省略该关键字。
- DISTINCT [ ON ( expression [, ...] ) ]
从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。
ON ( expression [, ...] ) 只保留那些在给出的表达式上运算出相同结果的行集合中的第一行。
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 指定每个字段的属性：
[OPTIONALLY] ENCLOSED 指定引号符，指定OPTIONALLY时只对字符串数据类型起作用。
ESCAPED 指定转义符。
- DUMPFILE
- file_name
into_option三处位置： --创建自定义变量 gaussdb=# CREATE DATABASE user_var dbcompatibility 'b'; gaussdb=# \c user_var user_var=# SET b_format_behavior_compat_options = enable_set_variables; user_var=# SET @my_var := 1; --创建表t user_var=# CREATE TABLE t (a int, b text); user_var=# INSERT INTO t values(1,'a'); --在from子句之前。 user_var=# SELECT a INTO @my_var FROM t; --在锁定子句之前。 user_var=# SELECT a FROM t INTO @my_var FOR UPDATE; --在select语句结尾。 user_var=# SELECT a FROM t FOR UPDATE INTO @my_var; 导出到文件： user_var=# SELECT * FROM t; a | b ---+--- 1 | a (1 row) --导出数据到outfile文件。 user_var=# SELECT * FROM t INTO OUTFILE '/tmp/t.txt' FIELDS TERMINATED BY '~' ENCLOSED BY 't' ESCAPED BY '^' LINES STARTING BY '$' TERMINATED BY '&\n'; --文件内容：$t1t~tat&，其中LINES STARTING BY($),FIELDS TERMINATED BY(~),ENCLOSED BY(t),LINES TERMINATED BY(&\n)。 --导出数据到dumpfile文件。 user_var=# SELECT * FROM t INTO DUMPFILE '/tmp/t.txt'; --文件内容：1a --切换到初始数据库,请使用真实的初始数据库名称替换init_db。 user_var=# \c init_db; --删除数据库 gaussdb=# DROP DATABASE user_var;
- var_name
- FROM子句
为SELECT声明一个或者多个源表。
FROM子句涉及的元素如下所示。
- table_name
- alias
给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。
别名用于缩写或者在自连接中消除歧义。如果提供了别名，它就会完全隐藏表的实际名称。
当为JOIN产生的表joined_table指定别名时，如果joined_table被()包裹，即(joined_table)，非保留关键字UNPIVOT和PIVOT不允许作为别名使用。
- TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
table_name之后的TABLESAMPLE子句表示应该用指定的sampling_method来检索表中行的子集。
可选的REPEATABLE子句指定一个用于产生采样方法中随机数的种子数。种子值可以是任何非空常量值。如果查询时表没有被更改，指定相同种子和argument值的两个查询将会选择该表相同的采样。但是不同的种子值通常将会产生不同的采样。如果没有给出REPEATABLE，则会基于一个系统产生的种子为每一个查询选择一个新的随机采样。
- TIMECAPSULE { TIMESTAMP | CSN } expression
目前不支持闪回查询的表：系统表、DFS表、全局临时表、本地临时表、UNLOGGED表、视图、序列表、hashbucket表、共享表、继承表。
- TIMECAPSULE TIMESTAMP
- expression
- TIMECAPSULE CSN
关键字，闪回查询的标识，根据表的CSN闪回查询指定CSN点的结果集。其中CSN可从gs_txn_snapshot记录的snpcsn号查得。
- 闪回查询不能跨越影响表结构或物理存储的语句，否则会报错。即闪回点和当前点之间，如果执行过修改表结构或影响物理存储的语句（TRUNCATE、DDL、DCL、VACUUM FULL），则闪回失败。执行过DDL的表进行闪回操作报错：“ERROR：The table definition of %s has been changed. ”。涉及namespace、表名改变等操作的DDL执行闪回操作报错： "ERROR: recycle object %s desired does not exist;”。
- 闪回查询不支持索引查询，闪回查询仅支持seqScan进行全表扫描。
- 闪回点过旧时，因闪回版本被回收等导致无法获取旧版本会导致闪回失败，报错：Restore point too old。
- 通过时间方式指定闪回点，闪回数据和实际时间点最多偏差为3秒。
- 对表执行TRUNCATE之后，再进行闪回查询或者闪回表操作。通过时间点进行的闪回操作会报错：Snapshot too old。通过CSN进行的闪回操作会找不到数据，或者报错：Snapshot too old。
- column_alias
- PARTITION
- partition_name
- partition_value
指定的分区键值。在创建分区表时，如果指定了多个分区键，可以通过PARTITION FOR子句指定的这一组分区键的值，唯一确定一个分区。
- SUBPARTITION
- subpartition_name
- subpartition_value
- subquery
- with_query_name
- function_name
- join_type
- [ INNER ] JOIN
- LEFT [ OUTER ] JOIN
返回笛卡尔积中所有符合连接条件的行，再加上左表中通过连接条件没有匹配到右表行的那些行。这样，左边的行将扩展为生成表的全长，方法是在那些右表对应的字段位置填上NULL。请注意，只在计算匹配的时候，才使用JOIN子句的条件，外层的条件是在计算完毕之后施加的。
- RIGHT [ OUTER ] JOIN
返回所有内连接的结果行，加上每个不匹配的右边行（左边用NULL扩展）。
这只是一个符号上的方便，因为总是可以把它转换成一个LEFT OUTER JOIN，只要把左边和右边的输入互换位置即可。
- FULL [ OUTER ] JOIN
- CROSS JOIN
CROSS JOIN等效于INNER JOIN ON（TRUE） ，即没有被条件删除的行。这种连接类型只是符号上的方便，因为它们与简单的FROM和WHERE的效果相同。
必须为INNER和OUTER连接类型声明一个连接条件，即NATURAL ON，join_condition，USING (join_column [， ...]) 之一。但是它们不能出现在CROSS JOIN中。
其中CROSS JOIN和INNER JOIN生成一个简单的笛卡尔积，和在FROM的顶层列出两个项的结果相同。
- ON join_condition
连接条件，用于限定连接中的哪些行是匹配的。如：ON left_table.a = right_table.a。不建议使用int等数值类型作为join_condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。
- USING(join_column[，...])
ON left_table.a = right_table.a AND left_table.b = right_table.b ... 的简写。要求对应的列必须同名。
- NATURAL
- from item
- unpivot_clause
unpivot_clause可将列转置为行，其对应语法格式为：
UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ] ( unpivot_col_clause unpivot_for_clause unpivot_in_clause )- {INCLUDE | EXCLUDE} NULLS
该子句用于控制转置后的结果是否包含存在NULL值的行，INCLUDE NULLS将使得结果包含存在NULL值的行，而EXCLUDE NULLS将从结果集中过滤掉这些行数据。如果忽略该子句，unpivot操作默认会从结果集中剔除存在NULL值的行。
- unpivot_col_clause为：
unpivot_col_element
unpivot_col_element指定了输出的列名，这些列会保存待转置列的列值。
- unpivot_col_element为：
{ column | ( column [, column]... ) }unpivot_col_element有两种形式：column；( column [, column]... )。
- unpivot_for_clause为：
FOR { unpivot_col_element }unpivot_for_clause中的unpivot_col_element用于指定输出的列名，这些列会保存待转置列的列名或别名。
- unpivot_in_clause为：
IN ( unpivot_in_element [,unpivot_in_element...] )
unpivot_in_clause指定了待转置列，这些列的列名和列值将保存在之前指定的输出列中。
unpivot_in_element为：{ unpivot_col_element }[ AS { unpivot_alias_element } ]unpivot_col_element为指定的待转置列，若采用( column [, column]... )形式指定待转置列，( column [, column]... )中所有的column列名将通过下划线 "_" 进行拼接，并保存在输出列中。如IN ((col1, col2)) 将会生成列名 "col1_col2"，并保存在unpivot_for_clause指定的输出列中。此外，AS关键字可为待转置列指定别名，一旦指定别名，输出列中将保存别名而不再保存待转置列的列名。
- unpivot_alias_element为：
{ alias | ( alias [, alias]... )}与unpivot_col_element类似，unpivot_alias_element也有两种形式。其中，alias为指定的别名。
目前unpivot_clause存在如下约束：
- 仅支持在A兼容模式下使用。
- unpivot_clause子句内不支持与hint配合使用。
- 对于unpivot_col_clause，其unpivot_col_element指定的输出列数目需与unpivot_in_clause中unpivot_col_element的列数目相同。
- 对于unpivot_for_clause，其unpivot_col_element指定的输出列数目需与unpivot_in_clause中unpivot_alias_element的别名数目相同。
- 对于unpivot_in_clause，别名必须为常量，或者可以转换为常量的表达式。
- 对于unpivot_in_clause，常量表达式支持的函数只能是不可变（IMMUTABLE）函数。
- 对于unpivot_in_clause的所有unpivot_col_element而言，如果这些unpivot_col_element相同位置的column类型存在差异，则unpivot会尝试进行类型转换，以将这些转置列的列值转换为公共类型。类似地，对于所有unpivot_alias_element而言，如果这些unpivot_alias_element相同位置的alias类型存在差异，unpivot也会进行类似的类型转换。
例如，假定存在"IN (col1, col2)"形式的unpivot_in_clause，其中col1为int类型，而col2为float类型，则unpivot在计算过程中会尝试将col1的列值转为公共类型float。
- {INCLUDE | EXCLUDE} NULLS
- pivot_clause
pivot_clause可将行转置为列，其对应语法格式为：
PIVOT [ XML ] ( aggregate_function ( expr ) [[AS] alias ] [, aggregate_function ( expr ) [[AS] alias ] ]... pivot_for_clause pivot_in_clause )- aggregate_function ( expr ) [[AS] alias ]
aggregate_function针对给定的表达式进行聚合计算，计算结果将保存在pivot_in_clause指定的输出列中。[AS] alias（AS关键字可省略）可为aggregate_function指定别名，别名将以 "_别名" 格式附加在pivot_in_clause指定的输出列名后。
- pivot_for_clause为：
FOR { column | ( column [, column]... ) }pivot_for_clause指定了待转置行，column表示待转置行的某一列。
- pivot_in_clause为：
IN ( { { { expr | ( expr [, expr]... ) } [ [AS] alias] }... } )pivot_in_clause指定了输出结果的列名，列名可由一个expr或多个expr构成，例如，（expr1, expr2）。当列名由多个expr构成时，这些expr将按顺序通过下划线 "_" 进行连接，即（expr1, expr2）对应的输出列名为 "expr1_expr2"。除了生成输出列名外，这些expr还决定着聚合函数触发时机，当待转置行的行值与这些expr的值相同时，pivot将进行聚合函数aggregate_function的计算，并将计算结果保存在列名由这些expr构成的输出列中。假定expr1为1，expr2为2，对于行"1 2"，pivot将进行aggregate_function的计算，对于行"1 1"，则不会触发计算。
目前pivot_clause存在如下约束：
- 仅支持在A兼容模式下使用。
- pivot_clause子句内不支持与hint配合使用。
- 当指定多于一个aggregate_function时，最多允许一个aggregate_function没有别名，其余aggregate_function均需指定别名。
- XML只支持语法不支持功能。
- pivot_in_clause中的expr可以是常量，或者是可以转换为常量的表达式。若不是一元表达式，则需为expr指定别名。
- 对于pivot_in_clause中的expr，常量表达式支持的函数只能是不可变（IMMUTABLE）函数。
- 对于pivot_in_clause中的expr，当通过as为其指定别名时，非保留关键字可作为别名使用，否则不能。
- 当输出列的列名长度超过63时，后续的字符将不会被打印。
- aggregate_function ( expr ) [[AS] alias ]
- WHERE子句
WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。condition是返回值为布尔型的任意表达式，任何不满足该条件的行都不会被检索。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。
WHERE子句中可以通过指定"(+)"操作符的方法将表的连接关系转换为外连接。但是不建议用户使用这种用法，因为这并不是SQL的标准语法，在做平台迁移的时候可能面临语法兼容性的问题。同时，使用"(+)"有很多限制：
- "(+)"只能出现在where子句中。
- 如果from子句中已经有指定表连接关系，那么不能再在where子句中使用"(+)"。
- "(+)"只能作用在表或者视图的列上，不能作用在表达式上。
- 如果表A和表B有多个连接条件，那么必须在所有的连接条件中指定"(+)"，否则"(+)"将不会生效，表连接会转化成内连接，并且不给出任何提示信息。
- "(+)"作用的连接条件中的表不能跨查询或者子查询。如果"(+)"作用的表，不在当前查询或者子查询的from子句中，则会报错。如果"(+)"作用的对端的表不存在，则不报错，同时连接关系会转化为内连接。
- "(+)"作用的表达式不能直接通过"OR"连接。
- 如果"(+)"作用的列是和一个常量的比较关系， 那么这个表达式会成为JOIN条件的一部分。
- 同一个表不能对应多个外表。
- "(+)"只能出现"比较表达式"，"NOT表达式"，“ANY表达式”，“ALL表达式”，“IN表达式”，“NULLIF表达式”，“IS DISTINCT FROM表达式”，“IS OF”表达式。"(+)"不能出现在其他类型表达式中，并且这些表达式中不允许出现通过“AND”和“OR”连接的表达式。
- "(+)"只能转化为左外连接或者右外连接，不能转化为全连接，即不能在一个表达式的两个表上同时指定"(+)"
- 对于WHERE子句的LIKE操作符，当LIKE中要查询特殊字符“%”、“_”、“\”的时候需要使用反斜杠“\”来进行转义。
- 对于层次查询，在多表连接查询中，会对WHERE表达式做以下处理：
将WHERE表达式按照析取、合取动作进行分解，查看每一个子表达式是否涉及当前层查询的多个表（潜在的连接条件），如果此子表达式不是子链接且仅涉及当前层查询的多个表，则将其下推至层次查询的非递归（START WITH）和递归（CONNECT BY）子句中，作为JOIN条件最先执行；否则，将其置于递归（CONNECT BY）子句结束之后，作为过滤条件执行。
在内部实现时，是先把原WHERE表达式的语法树中的不满足下推的子表达式删除后得到最终下推的表达式，再把原WHERE表达式的parsetree中的满足下推的子表达式删除后得到最终不下推的表达式。
综上所述，有以下几点需要额外强调：
- 拆分WHERE表达式时，析取也会被拆分，因此OR连接的两个表达式也会被拆分。
- 子链接一定不被下推。
- 如果存在非当前层查询传入的列，则一定不被下推。
那么，对于下面例子：
SELECT * FROM t1,t2 WHERE t1.a=1 or t1.a=t2.a and t1.b=t2.b or t1.c in (SELECT t3.c FROM t3) START WITH t1.c=1 CONNECT BY PRIOR t2.c=t1.c;
将WHERE表达式按照析取、合取拆开后，被下推的条件如下图所示：
其等价于 "t1.a=t2.a AND t1.b=t2.b"，不被下推的条件如下图所示：
其等价于"t1.a=1 OR t1.c in (SELECT t3.c FROM t3)"。
此外，对于另外一个例子：
SELECT * FROM t3 WHERE EXISTS( SELECT * FROM t1,t2 WHERE t1.a+t2.a=t3.a START WITH t1.c=1 CONNECT BY PRIOR t2.c=t1.c; )
其中where条件存在外层查询传入的t3.a，因此该where条件不会被下推。
- START WITH子句
START WITH子句通常与CONNECT BY子句同时出现，数据进行层次递归遍历查询，START WITH代表递归的初始条件。若省略该子句，单独使用CONNECT BY子句，则表示以表中的所有行作为初始集合。该功能详见•CONNECT BY子句。
- CONNECT BY子句
CONNECT BY代表递归连接条件，和START WITH子句一起使用，实现数据遍历递归的功能。如：
gaussdb=# CREATE TABLE test(name varchar, id int, fatherid int); gaussdb=# INSERT INTO test VALUES('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2); gaussdb=# SELECT * FROM test START WITH id = 1 CONNECT PRIOR id = fatherid ORDER SIBLINGS BY id DESC; name | id | fatherid ------+----+---------- A | 1 | 0 D | 4 | 1 C | 3 | 1 B | 2 | 1 E | 5 | 2 (5 rows)
CONNECT BY条件中可以对列指定PRIOR关键字代表以这列为递归键进行递归。若在递归连接条件前加NOCYCLE，则表示遇到循环记录时停止递归（注意：含START WITH .. CONNECT BY子句的SELECT语句不支持使用FOR SHARE/UPDATE锁）。
START WITH语句的执行流程是：- 由START WITH 区域的条件选择初始的数据集。上述例子里，先把 ('A', 1, 0) 选择出来了。然后把初始的数据集设为工作集。
- 只要工作集不为空，会用工作集的数据作为输入，查询下一轮的数据，过滤条件由connect by区域指定。其中，PRIOR关键字表示当前记录，如上文例子中prior id = fatherid表示当前记录的id是下一条记录的fatherid。
- 把2中筛选出来的数据集，设为工作集，返回第二步重复操作。
同时，数据库为每一条选出来的数据添加下述的伪列，方便用户了解数据在递归或者树状结构中的位置。- LEVEL：节点的层级，根节点层级为1。
- CONNECT_BY_ISLEAF：是否为叶子节点。
除了伪列之外，还提供下述的查询函数（详见层次递归查询函数）- sys_connect_by_path(col, separator)：返回从根节点到当前行的连接路径。参数col为路径中显示的列的名称，separator为连接符。
- connect_by_root(col)：显示该节点最顶级的节点，col为输出列的名称。
如果数据集中存在循环，数据库会提供循环检测。默认行为检查到循环会直接报错，不返回任何数据。同时，提供NOCYCLE关键字，查询可以正常执行，只是碰到第一条重复的数据时，会直接退出，而不是报错。
此外，在层次查询过程中，严格按照深度优先搜索的顺序进行。如果在START WITH或CONNECT BY中使用rownum作为过滤条件，对于每条尝试被返回的记录，rownum会先加1，之后按照rownum相关条件判断；对于不满足的记录，会被丢弃且rownum会减1。
- PRIOR 关键字只能出现在CONNECT BY语句中，不能出现在START WITH语句中。
- 除targetlist外，“prior(单列)”会被解析为“prior 单列”，不感知用户自定义的名为prior的函数。
- 只能对表中的列指定PRIOR，不支持对表达式、伪列及类型转换指定PRIOR关键字，如 PRIOR (a + 1) 不被允许。
- CONNECT BY语句中，PRIOR 修饰的列不可以和 level/rownum 等伪列在同一个条件里；但是可以在不同条件里。如 (PRIOR a = level) 不允许， (PRIOR a = b) and (level = 1) 允许。不同条件指的是CONNECT BY语句最上层的 and 连接起来的条件。比如（PRIOR a = 1 or level = 1）算作一个条件，也不被允许。
- START WITH/CONNECT BY语句中禁止将伪列用于子链接，即类似于 "rownum = (子查询)" 或 "rownum in (子查询)"。
- CONNECT BY子句中不建议使用ROWNUM，如需使用请充分测试，避免结果和预期不一致。
- 在with as定义的cte上调用START WITH/CONNECT BY时，如果cte有多个，需要保证每一个cte的定义不依赖于其他cte。
- 如果数据中不存在环路，但是报错runs into cycle，需要考虑增大max_recursive_times。
- connect_by_isleaf、connect_by_iscycle和level类型均为int。
- connect_by_isleaf、connect_by_iscycle和level在层次查询下，一定会被解析为伪列。
- START WITH、CONNECT BY不支持调用投影列中定义的别名。
- START WITH调优建议：
- 根据CONNECT BY中的条件，建立对应的索引，来提高START WITH语句的性能。
- 根据 explain performance或者WDR报告中的计划识别瓶颈点，如果发现Recursive Union的递归部分的算子（内层计划）为 HASH JOIN，但是 Hash 表是针对临时表 tmp_result构建或者计划中显示hash表发生物化（batch大于1），可能是 work_mem 过小导致无法对外层数据表建立Hash表。可以通过调大work_mem参数来提高性能。
说明：GaussDB会对小数据量的表有优化，把表的结果缓存在hash表中来提高性能，此时不需要索引。但是如果数据量超过work_mem的限制，该优化会失效，此时可采用建立索引的方式尝试优化。
不建议在创建视图定义时使用递归查询，否则所得视图定义有误或出现报错。这是因为递归查询语句底层会被改写成Recursive Union语句，而视图显示的定义是通过反解析Recursive Union的查询树所得，因此，创建带有递归查询的视图之后，查看视图定义是Recursive Union语句，而不是原本的START WITH语句，甚至语句不一定合法。虽然不影响当前数据库视图的使用功能，但是使用gs_dump/gs_restore等通过查看视图定义来迁移视图的工具迁移数据时，会导致restore失败，需要手动重新创建视图。
- ORDER SIBLINGS BY子句
START WITH语句输出时，不同层的数据会依次返回。但是在每一层内部，是没有任何顺序保证的，这是因为每一轮查询的过程中，数据库会自动选择最优的执行路径。上文的例子中，保证A会被先输出，但是B、C、D之间的顺序不固定。如果用户对最终输出顺序有需求，可以用ORDER SIBLINGS BY子句，用法和ORDER BY子句一样，用于在递归过程中每层内部的排序。
ORDER SIBLINGS BY后的表达式仅支持对普通列、列名偏移量、以及对列名的非聚集、窗口函数调用的方式进行排序，不支持对列名调用START WITH相关系统函数和使用START WITH相关伪列等方式。
- GROUP BY子句
将查询结果按某一列或多列的值分组，值相等的为一组。
- CUBE ( { expression | ( expression [, ...] ) } [, ...] )
CUBE是自动对group by子句中列出的字段进行分组汇总，结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。它会为每个分组返回一行汇总信息，用户可以使用CUBE来产生交叉表值。如，在CUBE子句中给出三个表达式（n = 3），运算结果为2n = 23 = 8组。 以n个表达式的值分组的行称为常规行，其余的行称为超级聚集行。
- GROUPING SETS ( grouping_element [, ...] )
GROUPING SETS子句是GROUP BY子句的进一步扩展，它可以使用户指定多个GROUP BY选项。这样做可以通过裁剪用户不需要的数据组来提高效率。 当用户指定了所需的数据组时，数据库不需要执行完整CUBE或ROLLUP生成的聚合集合。
- 如果SELECT列表的表达式中引用了那些没有分组的字段，则会报错，除非使用了聚集函数，因为对于未分组的字段，可能返回多个数值。
- 如果SELECT列表的表达式中引用了常量，则无需在GROUP BY子句中对该常量进行分组，否则会报错。
- 当在GROUP BY子句中使用主键时，SELECT语句中的所有非聚合列都可以出现在结果集中，因为主键的唯一性确保了每个分组中的非聚合列值的确定性。
- CUBE ( { expression | ( expression [, ...] ) } [, ...] )
- HAVING子句
与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较，只有满足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可以是：
UNBOUNDED PRECEDING value PRECEDING CURRENT ROW value FOLLOWING UNBOUNDED FOLLOWING
frame_start和frame_end当前不支持value PRECEDING和value FOLLOWING。
- UNION子句
UNION计算多个SELECT语句返回行集合的并集。
UNION子句有如下约束条件：
- 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。
- 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。
- FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE和FOR KEY SHARE不能在UNION的结果或输入中声明。
- 通过GUC参数enable_union_all_order控制UNION ALL子查询是否保序：
- 开启方式：set enable_union_all_order to on。
- 关闭方式：set enable_union_all_order to off。
- 查询方式：show enable_union_all_order。
在开启enable_union_all_order参数时，在UNION声明了ALL并且主查询未排序的情况下，支持子查询保序。其他情况均不支持子查询保序。
一般表达式：
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可以附着在子表达式里。
拼接UNION ALL子句时，推荐子句个数<100，超出此值时需保证实例内存足够，以避免内存不足。
- 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语句所检索的结果集的列中。
- 在与GROUP BY子句一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。
如果要支持中文拼音排序，需要在初始化数据库时指定编码格式为UTF-8、GB18030、GB18030_2022或GBK。命令如下:
initdb –E UTF8 –D ../data –locale=zh_CN.UTF-8、initdb -E GB18030 -D ../data -locale=zh_CN.GB18030、initdb -E GB18030_2022 -D ../data -locale=zh_CN.GB18030或initdb –E GBK –D ../data –locale=zh_CN.GBK。
- LIMIT子句
LIMIT子句由两个独立的子句组成：
LIMIT { count | ALL }限制返回行数，count为指定行数，LIMIT ALL的效果和省略LIMIT子句一样。
OFFSET start count声明返回的最大行数，而start声明开始返回行之前忽略的行数。如果两个都指定了，会在开始计算count个返回行之前先跳过start行。
LIMIT子句不支持ROWNUM作为count或者offset。
- 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处理。
- 对ustore表的查询只支持FOR SHARE/FOR UPDATE，不支持FOR KEY SHARE/FOR NO KEY UPDATE。
- 对于子查询是stream计划的FOR UPDATE/SHARE语句，不支持加锁的同一行被并发更新。
- NLS_SORT
指定某字段按照特殊方式排序。目前仅支持中文拼音格式排序和不区分大小写排序。如果要支持此排序方式，在创建数据库时需要指定编码格式为“UTF8”、”GB18030”、”GB18030_2022”或“GBK”；如果指定为其他编码，如SQL_ASCII，则可能报错或者排序无效。
取值范围：
- SCHINESE_PINYIN_M，按照中文拼音排序。
- generic_m_ci，不区分大小写排序（可选，仅支持纯英文不区分大小写排序）。
- PARTITION子句
查询某个分区表中相应分区的数据。
示例
--创建SCHEMA。 gaussdb=# CREATE SCHEMA tpcds; --创建表tpcds.reason。 gaussdb=# CREATE TABLE tpcds.reason ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) ); --向表中插入多条记录。 gaussdb=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),(20,'AAAAAAAACAAAAAAA','N%reason 6'),(30,'AAAAAAAACAAAAAAA','W%reason 7'); --先通过子查询得到一张临时表temp_t，然后查询表temp_t中的所有数据。 gaussdb=# WITH temp_t(name,isdba) AS (SELECT usename,usesuper FROM pg_user) SELECT * FROM temp_t; --查询tpcds.reason表的所有r_reason_sk记录，且去除重复。 gaussdb=# SELECT DISTINCT(r_reason_sk) FROM tpcds.reason; --LIMIT子句示例：获取表中一条记录。 gaussdb=# SELECT * FROM tpcds.reason LIMIT 1; --查询所有记录，且按字母升序排列。 gaussdb=# SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc; --通过表别名，从pg_user和pg_user_status这两张表中获取数据。 gaussdb=# SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid; --FULL JOIN子句示例：将pg_user和pg_user_status这两张表的数据进行全连接显示，即数据的合集。 gaussdb=# SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid; --GROUP BY子句示例：根据查询条件过滤，并对结果进行分组。 gaussdb=# SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25; --GROUP BY CUBE子句示例：根据查询条件过滤，并对结果进行分组汇总。 gaussdb=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY CUBE(r_reason_id,r_reason_sk); --GROUP BY GROUPING SETS子句示例:根据查询条件过滤，并对结果进行分组汇总。 gaussdb=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY GROUPING SETS((r_reason_id,r_reason_sk),r_reason_sk); --UNION子句示例：将表tpcds.reason里r_reason_desc字段中的内容以W开头和以N开头的进行合并。 gaussdb=# SELECT r_reason_sk, tpcds.reason.r_reason_desc FROM tpcds.reason WHERE tpcds.reason.r_reason_desc LIKE 'W%' UNION SELECT r_reason_sk, tpcds.reason.r_reason_desc FROM tpcds.reason WHERE tpcds.reason.r_reason_desc LIKE 'N%'; --NLS_SORT子句示例：中文拼音排序。 gaussdb=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = SCHINESE_PINYIN_M'); --不区分大小写排序（可选，仅支持纯英文不区分大小写排序）: gaussdb=# SELECT * FROM tpcds.reason ORDER BY NLSSORT( r_reason_desc, 'NLS_SORT = generic_m_ci'); --创建分区表tpcds.reason_p。 gaussdb=# CREATE TABLE tpcds.reason_p ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) ) PARTITION BY RANGE (r_reason_sk) ( partition P_05_BEFORE values less than (05), partition P_15 values less than (15), partition P_25 values less than (25), partition P_35 values less than (35), partition P_45_AFTER values less than (MAXVALUE) ) ; --插入数据。 gaussdb=# 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'); --PARTITION子句示例：从tpcds.reason_p的表分区P_05_BEFORE中获取数据。 gaussdb=# SELECT * FROM tpcds.reason_p PARTITION (P_05_BEFORE); r_reason_sk | r_reason_id | r_reason_desc -------------+------------------+------------------------------------ 4 | AAAAAAAABAAAAAAA | reason 3 3 | AAAAAAAABAAAAAAA | reason 1 (2 rows) --GROUP BY子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录数。 gaussdb=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id; count | r_reason_id -------+------------------ 2 | AAAAAAAACAAAAAAA 5 | AAAAAAAABAAAAAAA (2 rows) --GROUP BY CUBE子句示例：根据查询条件过滤，并对查询结果分组汇总。 gaussdb=# SELECT * FROM tpcds.reason GROUP BY CUBE (r_reason_id,r_reason_sk,r_reason_desc); --GROUP BY GROUPING SETS子句示例：根据查询条件过滤，并对查询结果分组汇总。 gaussdb=# SELECT * FROM tpcds.reason GROUP BY GROUPING SETS ((r_reason_id,r_reason_sk),r_reason_desc); --HAVING子句示例：按r_reason_id分组统计tpcds.reason_p表中的记录，并只显示r_reason_id个数大于2的信息。 gaussdb=# SELECT COUNT(*) c,r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING c>2; c | r_reason_id ---+------------------ 5 | AAAAAAAABAAAAAAA (1 row) --IN子句示例：按r_reason_id分组统计tpcds.reason_p表中的r_reason_id个数，并只显示r_reason_id值为 AAAAAAAABAAAAAAA或AAAAAAAADAAAAAAA的个数。 gaussdb=# SELECT COUNT(*),r_reason_id FROM tpcds.reason_p GROUP BY r_reason_id HAVING r_reason_id IN('AAAAAAAABAAAAAAA','AAAAAAAADAAAAAAA'); count | r_reason_id -------+------------------ 5 | AAAAAAAABAAAAAAA (1 row) --INTERSECT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且r_reason_sk小于5的信息。 gaussdb=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' INTERSECT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<5; r_reason_sk | r_reason_id | r_reason_desc -------------+------------------+------------------------------------ 4 | AAAAAAAABAAAAAAA | reason 3 3 | AAAAAAAABAAAAAAA | reason 1 (2 rows) --EXCEPT子句示例：查询r_reason_id等于AAAAAAAABAAAAAAA，并且去除r_reason_sk小于4的信息。 gaussdb=# SELECT * FROM tpcds.reason_p WHERE r_reason_id='AAAAAAAABAAAAAAA' EXCEPT SELECT * FROM tpcds.reason_p WHERE r_reason_sk<4; r_reason_sk | r_reason_id | r_reason_desc -------------+------------------+------------------------------------ 10 | AAAAAAAABAAAAAAA | reason 2 10 | AAAAAAAABAAAAAAA | reason 5 10 | AAAAAAAABAAAAAAA | reason 4 4 | AAAAAAAABAAAAAAA | reason 3 (4 rows) --创建表store_returns、customer gaussdb=# CREATE TABLE tpcds.store_returns (sr_item_sk int, sr_customer_id varchar(50),sr_customer_sk int); gaussdb=# CREATE TABLE tpcds.customer (c_item_sk int, c_customer_id varchar(50),c_customer_sk int); --通过在where子句中指定"(+)"来实现左连接。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE t1.sr_customer_sk = t2.c_customer_sk(+) ORDER BY 1 DESC LIMIT 1; sr_item_sk | c_customer_id ------------+--------------- 18000 | (1 row) --通过在where子句中指定"(+)"来实现右连接。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE t1.sr_customer_sk(+) = t2.c_customer_sk ORDER BY 1 DESC LIMIT 1; sr_item_sk | c_customer_id ------------+------------------ | AAAAAAAAJNGEBAAA (1 row) --通过在where子句中指定"(+)"来实现左连接，并且增加连接条件。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE t1.sr_customer_sk = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1 ORDER BY 1 LIMIT 1; sr_item_sk | c_customer_id ------------+--------------- 1 | (1 row) --不支持在where子句中指定"(+)"的同时使用内层嵌套AND/OR的表达式。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE NOT (t1.sr_customer_sk = t2.c_customer_sk(+) and t2.c_customer_sk(+) < 1); ERROR: Operator "(+)" can not be used in nesting expression. LINE 1: ...tomer_id from store_returns t1, customer t2 where not(t1.sr_... ^ --where子句在不支持表达式宏指定"(+)"会报错。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE (t1.sr_customer_sk = t2.c_customer_sk(+))::bool; ERROR: Operator "(+)" can only be used in common expression. --where子句在表达式的两边都指定"(+)"会报错。 gaussdb=# SELECT t1.sr_item_sk ,t2.c_customer_id FROM tpcds.store_returns t1, tpcds.customer t2 WHERE t1.sr_customer_sk(+) = t2.c_customer_sk(+); ERROR: Operator "(+)" can't be specified on more than one relation in one join condition HINT: "t1", "t2"...are specified Operator "(+)" in one condition. --删除表。 gaussdb=# DROP TABLE tpcds.reason_p; --闪回查询示例，使用闪回功能需要设置undo_retention_time参数 --创建表tpcds.time_table gaussdb=# CREATE TABLE tpcds.time_table(idx integer, snaptime timestamp, snapcsn bigint, timeDesc character(100)); --向表tpcds.time_table中插入记录 gaussdb=# INSERT INTO tpcds.time_table select 1, now(),int8in(xidout(next_csn)), 'time1' from gs_get_next_xid_csn(); gaussdb=# INSERT INTO tpcds.time_table select 2, now(),int8in(xidout(next_csn)), 'time2' from gs_get_next_xid_csn(); gaussdb=# INSERT INTO tpcds.time_table select 3, now(),int8in(xidout(next_csn)), 'time3' from gs_get_next_xid_csn(); gaussdb=# INSERT INTO tpcds.time_table select 4, now(),int8in(xidout(next_csn)), 'time4' from gs_get_next_xid_csn(); gaussdb=# SELECT * FROM tpcds.time_table; idx | snaptime | snapcsn | timedesc -----+----------------------------+---------+------------------------------------------------------------------------------------------------------ 1 | 2021-04-25 17:50:05.360326 | 107322 | time1 2 | 2021-04-25 17:50:10.886848 | 107324 | time2 3 | 2021-04-25 17:50:16.12921 | 107327 | time3 4 | 2021-04-25 17:50:22.311176 | 107330 | time4 (4 rows) gaussdb=# DELETE tpcds.time_table; DELETE 4 --2021-04-25 17:50:22.311176应该使用tpcds.time_table中第四条snaptime字段值 gaussdb=# SELECT * FROM tpcds.time_table TIMECAPSULE TIMESTAMP to_timestamp('2021-04-25 17:50:22.311176','YYYY-MM-DD HH24:MI:SS.FF'); idx | snaptime | snapcsn | timedesc -----+----------------------------+---------+------------------------------------------------------------------------------------------------------ 1 | 2021-04-25 17:50:05.360326 | 107322 | time1 2 | 2021-04-25 17:50:10.886848 | 107324 | time2 3 | 2021-04-25 17:50:16.12921 | 107327 | time3 (3 rows) --107330 csn应该使用tpcds.time_table中第四条snapcsn字段值 gaussdb=# SELECT * FROM tpcds.time_table TIMECAPSULE CSN 107330; idx | snaptime | snapcsn | timedesc -----+----------------------------+---------+------------------------------------------------------------------------------------------------------ 1 | 2021-04-25 17:50:05.360326 | 107322 | time1 2 | 2021-04-25 17:50:10.886848 | 107324 | time2 3 | 2021-04-25 17:50:16.12921 | 107327 | time3 (3 rows) --WITH RECURSIVE查询示例：计算从1到100的累加值。 gaussdb=# WITH RECURSIVE t1(a) AS ( SLEECT 100 ), t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < (SELECT max(a) FROM t1) ) SELECT sum(n) FROM t; sum ------ 5050 (1 row) gaussdb=# DROP TABLE t; --UNPIVOT子句示例：将表p1的math列和phy列转置为（class，score）行 gaussdb=# CREATE TABLE p1(id int, math int, phy int); gaussdb=# INSERT INTO p1 values(1,20,30); gaussdb=# INSERT INTO p1 values(2,30,40); gaussdb=# INSERT INTO p1 values(3,40,50); gaussdb=# SELECT * FROM p1; id | math | phy ----+------+----- 1 | 20 | 30 2 | 30 | 40 3 | 40 | 50 (3 rows) gaussdb=# SELECT * FROM p1 UNPIVOT(score FOR class IN(math, phy)); id | class | score ----+-------+------- 1 | MATH | 20 1 | PHY | 30 2 | MATH | 30 2 | PHY | 40 3 | MATH | 40 3 | PHY | 50 (6 rows) --PIVOT子句示例：将表p2的（class，score）行转置为'MATH'列和 'PHY'列 gaussdb=# CREATE TABLE p2(id int, class varchar(10), score int); gaussdb=# INSERT INTO p2 SELECT * FROM p1 UNPIVOT(score FOR class IN(math, phy)); gaussdb=# SELECT * FROM p2; id | class | score ----+-------+------- 1 | MATH | 20 1 | PHY | 30 2 | MATH | 30 2 | PHY | 40 3 | MATH | 40 3 | PHY | 50 (6 rows) gaussdb=# SELECT * FROM p2 PIVOT(max(score) FOR class IN ('MATH', 'PHY')); id | 'MATH' | 'PHY' ----+--------+------- 1 | 20 | 30 3 | 40 | 50 2 | 30 | 40 (3 rows) gaussdb=# DROP TABLE p1; gaussdb=# DROP TABLE p2; --删除表。 gaussdb=# DROP TABLE tpcds.reason; --删除SCHEMA。 gaussdb=# DROP SCHEMA tpcds CASCADE;
