#SELECT
# 通用描述
SELECT用于执行对数据库的表、视图、AC等的查询操作。
YashanDB支持丰富多样的查询操作，包括但不限于：
其中，对AC可以执行上述列示的多表连接、排序、分组等基本查询。
对于分布式系统视图（DV$开头的系统视图），上述多表连接查询并不适用，即用户在查询分布式系统视图时，只可以使用指定单表的方式。
# 语句定义
select::=
subquery::=
set_oper::=
query_block::=
with_clause::=
cte_clause::=
select_list::=
expr_clause::=
case_clause::=
else_clause::=
query_name::=
table_collection_expression::=
pivot_clause::=
join_clause::=
where_clause::=
# subquery
YashanDB的SELECT语句支持嵌套查询（对封装在()中的SELECT语句的结果进行SELECT查询）和合并查询（用集合合并多个SELECT语句的查询结果），不同方式的查询可交叉及多层级使用，例如：
SELECT * FROM (SELECT a,b FROM (SELECT cl_a a,cl_b b FROM table_a WHERE ...
UNION ALL
SELECT cl_c a,cl_d b FROM table_b WHERE ...
)
WHERE ...
)
WHERE ...
每一个完整的SELECT查询语句（包括其下级）均可称为一个子查询（subQuery）。 对于子查询中的select_list列项：
- 若这些列项显式指定了别名（例如为列c1_a定义别名a），系统以此别名作为子查询返回结果集的列名，YashanDB对显式指定别名的长度限制为64字节。
- 若这些列项未显式指定别名，系统默认以该项列名作为子查询返回结果集的列名，且超过20字节截断，此时，如子查询select_list中存在名称前20字节相同的列，将导致返回结果集产生相同名称的列而触发系统报错。
示例
SELECT * FROM
(SELECT CAST(CAST(a.area_no AS INT) AS BIGINT),
CAST(CAST(a.area_no AS VARCHAR(100)) AS VARCHAR(100))
FROM area a);
[3:1]YAS-04301 ambiguous column
# query_block
含有单个SELECT的查询语句称为一个查询块（queryBlock）。
# set_oper
集合操作，用于将两个或两个以上的查询结果集组合成单个结果集，包括如下类型：
UNION：合并且过滤掉重复值。
UNION ALL：合并且保留重复值。
INTERSECT：比较两个查询的结果，选择所有查询结果集中相同行的记录，且过滤掉重复值。
INTERSECT ALL：比较两个查询的结果，选择所有查询结果集中相同行的记录，且保留重复值。
MINUS：比较两个查询的结果，返回在第一个查询结果集中，但不是第二个查询结果集中的行的记录，且过滤掉重复值。
MINUS ALL：比较两个查询的结果，返回在第一个查询结果集中，但不是第二个查询结果集中的行的记录，且保留重复值。
进行集合操作的查询结果集的数据类型必须处于下面同一个分类中，否则函数返回Query column mismatch错误：
- 数值型：按优先度从高到低为DOUBLE、FLOAT、NUMBER、BIGINT、INT、SMALLINT、TINYINT，但BIT类型只能与同类型在同一个操作列表中。
- 字符型：按优先度从高到低为VARCHAR、CHAR。
- 日期型：按优先度从高到低为TIMESTAMP、DATE、TIME，但INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND只能与同类型在同一个参数列表中。
- 布尔型：BOOLEAN。
- CLOB
Note：
1.同一分类中数据类型不同时，低优先度类型向高优先度类型转换。
2.结果集为常量NULL（查询列为常量NULL所生成的结果集）时，不受上述分类限制。
3.所有集合运算为统一运算优先级，运算顺序以书写顺序为准，可通过双括号（）调整想要达到的运算优先级。
示例
--UNION
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
UNION
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
01 华东 Shanghai
02 华西 Chengdu
03 华南 Guangzhou
--UNION ALL
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
UNION ALL
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
01 华东 Shanghai
03 华南 Guangzhou
01 华东 Shanghai
02 华西 Chengdu
--INTERSECT
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
INTERSECT
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
01 华东 Shanghai
--INTERSECT ALL
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
INTERSECT ALL
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
01 华东 Shanghai
--MINUS
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
MINUS
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
02 华西 Chengdu
--MINUS ALL
SELECT *
FROM (SELECT * FROM area WHERE area_no IN ('01','02')
MINUS ALL
SELECT * FROM area WHERE area_no IN ('01','03')
);
AREA_NO AREA_NAME DHQ
------- ------------------ ----------
02 华西 Chengdu
# with_clause
该语句用于定义CTE（Common Table Expression），在某个子查询被多次使用时，可以直接使用CTE名称而不需要重复写该SELECT语句。
分布式部署中无法使用本语句。
# cte_clause
CTE定义，包括：
- cte_name：名称，作为其他地方引用的标识。
- column_alias：定义CTE的列项，其中列字段应该来自于其后的子查询中定义的列项。
- subquery：用于创建CTE的子查询。
示例
--WITH语句用于获取area表中'01'和'04'地区的代码和名称，在后面的查询中对branches表获取关联地区名称数据时，不需要多次写该语句，而只用和q_area做关联查询即可
WITH q_area(ano,aname) AS (SELECT area_no ano,area_name aname FROM area WHERE area_no IN ('01','04'))
SELECT bno,bname,aname FROM (
SELECT a.branch_no bno,a.branch_name bname,b.aname aname FROM branches a,q_area b WHERE a.area_no=b.ano AND a.area_no='01'
UNION
SELECT a.branch_no bno,a.branch_name bname,b.aname aname FROM branches a,q_area b WHERE a.area_no=b.ano AND a.area_no='04'
);
BNO BNAME ANAME
----- ----------------- -------------
0101 上海 华东
0102 南京 华东
0103 福州 华东
0104 厦门 华东
0401 北京 华北
0402 天津 华北
0403 大连 华北
0404 沈阳 华北
# hint
该语句用于提出给定的方案到优化器（Optimizer ），使其按照此方案生成语句的执行计划。查看hint说明。
# distinct
该语句用于对查询结果进行过滤设置，对查询结果中重复的多条记录只返回一条记录。
# select_list
该语句用于指定查询语句中要查询的列项，例如FROM后面某个表的某个列字段（Column）等。查询多项以,分隔。*表示查询FROM后面所有表|视图|AC|子查询等的所有列。
AS alias用于定义别名，AS可省略。
# expr_clause
指定具体的列项，可以为：
- query_block：将一个查询块语句的结果作为列项
- expr：表达式
- CASE语句
- udt_expr：对于Object UDT列，udt_expr格式为表别名.属性名；对于Varray UDT和Nested Table UDT列，udt_expr格式为表别名.*。详细使用方法和示例见用户自定义类型中描述。
示例
--单机部署中
SELECT SYSDATE 查询日期,
a.branch_name 机构,
b.area_name 区域,
(SELECT DHQ FROM area WHERE area_no='10') 默认总部
FROM branches a, area b
WHERE a.area_no IN (SELECT area_no FROM area )
AND a.area_no=b.area_no;
查询日期 机构 区域 默认总部
-------------------------------- --------------- --------------------- ------
2022-01-10 10:24:35 上海 华东
2022-01-10 10:24:35 南京 华东
2022-01-10 10:24:35 福州 华东
2022-01-10 10:24:35 厦门 华东
2022-01-10 10:24:35 北京 华北
2022-01-10 10:24:35 天津 华北
2022-01-10 10:24:35 大连 华北
2022-01-10 10:24:35 沈阳 华北
2022-01-10 10:24:35 成都 华西
2022-01-10 10:24:35 长沙 华中
--分布式部署中
SELECT SYSDATE 查询日期,
a.branch_name 机构,
b.area_name 区域,
'ShenZhen' 默认总部
FROM branches a, area b
WHERE a.area_no IN ('01','02','03')
AND a.area_no=b.area_no;
查询日期 机构 区域 默认总部
------------------- ------------------- ---------------------- ----------
2022-01-23 上海 华东 ShenZhen
2022-01-23 南京 华东 ShenZhen
2022-01-23 福州 华东 ShenZhen
2022-01-23 厦门 华东 ShenZhen
2022-01-23 成都 华西 ShenZhen
# case_clause
case_clause = CASE { simple_case_expression | searched_case_expression } [ else_clause ] END.
CASE表达式相当于编程语言中的IF ELSE，其写法有两种：Simple Case和Searched Case。
simple_case_expression = expr { WHEN comparison_expr THEN return_expr }.
searched_case_expression = { WHEN condition THEN return_expr }.
else_clause = ELSE else_expr.
在Simple Case写法中，系统搜寻第一个与expr等值的comparision_expr，并返回相对应的return_expr。如果没有等值的comparision_expr，并且存在ELSE子句，则返回else_expr。否则返回NULL。
在Searched Case写法中，系统从左至右依次检查condition，并返回第一个相对应的return expr。如果所有condition皆为FALSE，并且存在ELSE子句，则返回else_expr。否则返回NULL。
需要注意的是，CASE语句中出现的所有表达式（else_expr/return_expr/expr/comparison_expr）的值在同一个语句中必须同属下列几种大类之一：
- 数值型，优先度从低到高的排序为：TINYINT、SMALLINT、INT、BIGINT、NUMBER、FLOAT、DOUBLE。
- 字符型，优先度从低到高的排序为：CHAR、VARCHAR。
- 日期型，优先度从低到高的排序为：DATE、TIMESTAMP。
- 其他数据类型归成一个大类。
在每个大类中，表达式的值将先统一为优先度高的数据类型，再进行匹配或返回。因此CASE表达式返回值的数据类型为所有表达式中优先度最高的类型。
示例
SELECT employee_name 姓名, CASE sex
WHEN '0' THEN '女'
WHEN '1' THEN '男'
END 性别
FROM employees;
姓名 性别
------------- ------
Mask 男
John 男
Anna 女
Jack 男
Jim 男
--存在未列出的条件结果时，返回NULL
SELECT employee_name 姓名, CASE sex
WHEN '2' THEN '女'
WHEN '1' THEN '男'
END 性别
FROM employees;
姓名 性别
------------- ------
Mask 男
John 男
Anna
Jack 男
Jim 男
--对所有不在列出条件的情况，给定默认值
SELECT employee_name 姓名, CASE sex
WHEN '2' THEN '女'
WHEN '1' THEN '男'
ELSE '未知'
END 性别
FROM employees;
姓名 性别
------------- ---------
Mask 男
John 男
Anna 未知
Jack 男
Jim 男
# table_reference
该语句用于指定FROM语句中对查询对象的定义。
# query_table_expression
YashanDB支持对如下类型项的查询：
- query_name：表（分区）、视图、同义词（分区）、AC等
- subquery：一个子查询的结果
可为上述项分别定义别名。
示例
--单机部署中，sales_info为一张分区表，可指定分区来查询其数据
SELECT * FROM sales_info PARTITION (p_sales_info_1);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2001 01 0201 11001 30 500 0201010011
--为其创建一个同义词，也可指定分区来查询其数据
CREATE SYNONYM sy_sales_info FOR sales_info;
SELECT * FROM sy_sales_info PARTITION(p_sales_info_2);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2021 10 0101 11001 20 300
2021 05 0101 11001 40 600
--将其作为子查询结果
SELECT b.branch_name 机构,
SUM(s.amount) 销售额
FROM branches b,
(SELECT * FROM sy_sales_info PARTITION(p_sales_info_2)) s
WHERE b.branch_no=s.branch
GROUP BY b.branch_name;
机构 销售额
---------------------------------------------------------------- -----------
上海 900
--分布式部署中，sales_info为一张分区表，可指定分区来查询其数据
SELECT * FROM sales_info PARTITION (p_sales_info_1);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2001 01 0201 11001 30 500 0201010011
SELECT * FROM sales_info PARTITION(p_sales_info_2);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2021 10 0101 11001 20 300
2021 05 0101 11001 40 600
--将其作为子查询结果
SELECT b.branch_name 机构,
SUM(s.amount) 销售额
FROM branches b,
(SELECT * FROM sales_info PARTITION(p_sales_info_2)) s
WHERE b.branch_no=s.branch
GROUP BY b.branch_name;
机构 销售额
-------------- -----------
上海 900
指定切片查询
对于LSC表，可以通过指定Slice ID查询某个切片的数据，具体规则为：
- 只能对LSC表指定切片查询，否则报错。
- 对于分区表，指定切片的同时，必须指定分区，否则报错。
- Slice ID为0表示查询可变数据区（MCOL）的数据。
- 通过查询V$LSC_SLICE_STAT视图可获得某张LSC表（分区）包含的所有Slice ID。
示例
--sales_info表的三个分区中分别存在如下稳态数据
SELECT * FROM sales_info PARTITION(p_sales_info_1);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2001 01 0201 11001 30 500 0201010011
SELECT * FROM sales_info PARTITION(p_sales_info_2);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2021 10 0101 11001 20 300
2021 05 0101 11001 40 600
SELECT * FROM sales_info PARTITION(p_sales_info_3);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2000 12 0102 11001 20 300
2015 11 0101 11001 20 300
2015 03 0102 11001 20 300
--新插入sales_info表一条数据，将存储在可变数据区
INSERT INTO sales_info VALUES ('2002','03','0301','11001',40,700,'');
--查询V$LSC_SLICE_STAT视图获得Slice ID
SELECT utp.table_name, utp.partition_name, lss.slice_id
FROM USER_TAB_PARTITIONS utp, V$LSC_SLICE_STAT lss, USER_OBJECTS uo
WHERE utp.table_name = 'SALES_INFO'
AND uo.subobject_name = utp.partition_name
AND uo.object_id = lss.obj;
TABLE_NAME PARTITION_NAME SLICE_ID
---------------------- ----------------------- ------------
SALES_INFO P_SALES_INFO_1 1024
SALES_INFO P_SALES_INFO_1 0
SALES_INFO P_SALES_INFO_2 1024
SALES_INFO P_SALES_INFO_2 0
SALES_INFO P_SALES_INFO_3 1024
SALES_INFO P_SALES_INFO_3 0
--查询指定切片的数据
SELECT * FROM sales_info PARTITION(p_sales_info_1) SLICE(1024);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2001 01 0201 11001 30 500 0201010011
--查询MCOL数据
SELECT * FROM sales_info PARTITION(p_sales_info_1) SLICE(0);
YEAR MONTH BRANCH PRODUCT QUANTITY AMOUNT SALSPERSON
----- ----- ------ --------- ----------- ----------- -------------
2002 03 0301 11001 40 700
table_collection_expression
该语句可用于执行如下两种情况：
- UDT
- 表函数
UDT
可用于查询Varray UDT或Nested Table UDT数据，UDT数据类型的详细描述请参考用户自定义类型。
执行此类查询时，collection_expression为一个Varray对象或Nested Table对象，语法格式为表别名.UDT列名。
Varray对象依赖于所在表，因此应保证执行计划最先访问主表。
示例
DROP TABLE IF EXISTS city_intro;
CREATE OR REPLACE TYPE brc_array AS VARRAY(20) OF CHAR(15);
/
CREATE OR REPLACE TYPE city_table AS TABLE OF CHAR(10);
/
--创建包含Varray UDT和Nested Table UDT列的city_intro表
CREATE TABLE city_intro (id INT, branches brc_array, citys city_table)
NESTED TABLE citys STORE AS nt_citys;
--插入数据
INSERT INTO city_intro
VALUES (1,
brc_array('branch01','branch02','branch03'),
city_table('shenzhen','guangzhou','dongguan'));
--查询数据
SELECT /*+ LEADING(c) */ c.id, cbranch.*, ccity.*
FROM city_intro c,
TABLE(c.branches) cbranch,
TABLE(c.citys) ccity;
ID COLUMN_VALUE COLUMN_VALUE
------------ ----------------- -------------
1 branch01 shenzhen
1 branch02 shenzhen
1 branch03 shenzhen
1 branch01 guangzhou
1 branch02 guangzhou
1 branch03 guangzhou
1 branch01 dongguan
1 branch02 dongguan
1 branch03 dongguan
表函数
可使用表函数进行查询，表函数的具体描述可见内置函数章节中内置表函数部分描述。
执行此类查询时，collection_expression为所需表函数。
# sample_clause
SAMPLE用于从表中按比例抽取一个随机样本，后续WHERE条件将基于这个样本数据而不是整张表数据。
本语句可作用于单个物理表，或者基于单个物理表的视图，对于多表连接的视图执行SAMPLE将会报错。
分布式部署中不可使用本语句。
sample_percent
指定样本所占的百分比，其值必须为一个[0.000001, 100]区间的常量数值（支持可以隐式转换为数值的其他类型）。此百分比代表采样时每个数据行被选为样本的概率，该概率为统计学上的概念，这意味着系统并不会精确返回基于sample_percent计算出的行数的记录。
SEED seed_value
本语句定义一个种子值，系统将尝试对相同的种子值返回相同的样本，未指定本语句时系统每次返回的样本是随机的。seed_value的值必须为一个[0, 4294967295]区间的常量整数（支持可以隐式转换为数值的其他类型，支持将小数截断为整数），其中大于4294967295的数值按4294967295处理。
示例
--随机返回样本
SELECT * FROM area SAMPLE(60);
AREA_NO AREA_NAME DHQ
------- ------------------------- ---------------------
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
--指定相同seed返回相同样本
SELECT * FROM area SAMPLE(60) SEED(7);
AREA_NO AREA_NAME DHQ
------- ----------------------- ---------------------
01 华东 Shanghai
02 华西 Chengdu
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
SELECT * FROM area SAMPLE(60) SEED(10);
AREA_NO AREA_NAME DHQ
------- ---------------------- ---------------------
01 华东 Shanghai
03 华南 Guangzhou
04 华北 Beijing
SELECT * FROM area SAMPLE(60) SEED(7);
AREA_NO AREA_NAME DHQ
------- ----------------------- ---------------------
01 华东 Shanghai
02 华西 Chengdu
03 华南 Guangzhou
04 华北 Beijing
05 华中 Wuhan
# flashback_query_clause
YashanDB支持对近期历史数据的追溯查询，例如某条记录在某个时间点已被UPDATE或DELETE更改，但通过flashback_query_clause可以获得该时间点之前一段时间范围内（这取决于UNDO空间的清理规则），该条记录被更改之前的数据值。
可以使用SCN号或TIMESTAMP标签来定位到想要追溯查询的行记录。
系统在记录TIMESTAMP时使用的是北京时间。
分布式部署中不可使用本语句。
示例
--area表中存在的一条记录
SELECT * FROM area WHERE area_no='03';
AREA_NO AREA_NAME DHQ
------- ------------------ ------------
03 华南 Guangzhou
--获取当前时间
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd hh24:mi:ss') FROM dual;
TO_CHAR(SYSDATE,'YYY
----------------------------------------------------------------
2022/01/17 17:21:37
--删除此条记录并提交
DELETE FROM area WHERE area_no='03';
COMMIT;
SELECT * FROM area WHERE area_no='03';
AREA_NO AREA_NAME DHQ
------- ------------------ ------------
--利用flashback可查询到快照历史数据
SELECT * FROM area AS OF TIMESTAMP TO_TIMESTAMP('2022/01/17 17:21:37','yyyy/mm/dd hh24:mi:ss') WHERE area_no='03';
AREA_NO AREA_NAME DHQ
------- ------------------ ------------
03 华南 Guangzhou
# pivot_clause
PIVOT用于从结果集中抽出FOR后的列，根据IN里的常量表达式列表，与聚合函数列表构新的投影列。
PIVOT可以对任意结果集即单个表或多表join的结果进行操作。
column_expression
必须为简单列名，即不允许使用table.column的形式。
const_expression
必须为常量表达式。
示例
-- 抽取区域编号列，并统计对应区域的数量
SELECT * FROM (SELECT branch_name, area_no FROM branches)
PIVOT(
COUNT(branch_name)
FOR area_no
IN ('01' AS 华东, '02' AS 华西, '03' AS 华南, '04' AS 华北, '05' AS 华中)
);
华东 华西 华南 华北 华中
--------------------- --------------------- --------------------- --------------------- ---------------------
4 1 0 4 1
-- 显示华东和华西地区最大编号与城市数量
SELECT * FROM (SELECT branch_no, branch_name, area_no FROM branches)
PIVOT(
COUNT(branch_name) 城市数量,
MAX(branch_no) 最大编号
FOR area_no
IN ('01' AS 华东, '02' AS 华西)
);
华东_城市数量 华东_最大编号 华西_城市数量 华西_最大编号
--------------------- ------------------- --------------------- -------------------
4 0104 1 0201
# join_clause
该语句用于将多个表、视图、AC、子查询进行连接（Join）查询，有如下几种Join方式：
- 显示指定JOIN关键字进行连接查询，并通过指定INNER|OUTER关键字分为内连接（Inner Join）和外连接（Outer Join）两种方式，未指定时缺省为内连接。
- 在FROM后面将各表、视图、子查询以
,分隔，而在WHERE后指定条件以获取交叉连接后的查询结果，此种方式等同于内连接。 - 在WHERE后条件子句中指定(+)操作符，或者在JOIN ON后子句中指定(+)操作符，表示外连接。
# inner_cross_join_clause
内连接查询是一种交叉连接查询方式，对A和B进行内连接查询时，将A的每一行与B的每一行按照连接条件进行比较，返回的是A、B均满足条件的结果。其中：
- INNER：产生的结果是AB的交集。此时必须在后面指定ON条件。
- CROSS：产生的结果是AB的笛卡尔积。此时不可以在后面指定ON条件。
示例
--INNER JOIN,INNER关键字可省略
SELECT b.branch_name, a.area_name
FROM branches b
INNER JOIN area a
ON a.area_no = b.area_no
WHERE b.branch_no LIKE '01%';
BRANCH_NAME AREA_NAME
----------------- --------------------
上海 华东
南京 华东
福州 华东
厦门 华东
--等同于上面的内连接
SELECT b.branch_name, a.area_name
FROM branches b, area a
WHERE a.area_no=b.area_no AND b.branch_no LIKE '01%';
BRANCH_NAME AREA_NAME
----------------- --------------------
上海 华东
南京 华东
福州 华东
厦门 华东
--CROSS JOIN
SELECT b.branch_name, a.area_name
FROM branches b
CROSS JOIN area a
WHERE b.branch_no LIKE '01%';
BRANCH_NAME AREA_NAME
----------------- --------------------
上海 华东
上海 华西
上海 华南
上海 华北
上海 华中
南京 华东
南京 华西
南京 华南
南京 华北
南京 华中
福州 华东
福州 华西
福州 华南
福州 华北
福州 华中
厦门 华东
厦门 华西
厦门 华南
厦门 华北
厦门 华中
# outer_join_clause
外连接查询指定了一张主表，对主表中的记录不要求在对方表里能找到匹配记录，而是全部做为有效结果返回，在返回结果集中对于对方未找到匹配值的列项设为NULL值。外连接需要显式地指定左连接或右连接。
# outer_join_type
LEFT [OUTER] JOIN
左连接查询，当A LEFT OUTER JOIN B时，以A为主表，A的每一行记录均会出现在查询结果集中，对于在B中匹配不到的值设为NULL。
RIGHT [OUTER] JOIN
右连接查询，当A RIGHT OUTER JOIN B时，以B为主表，B的每一行记录均会出现在查询结果集中，对于在A中匹配不到的值设为NULL。
FULL [OUTER] JOIN
全连接查询，当A FULL OUTER JOIN B时，A和B的每一行记录均会出现在查询结果集中，对于在A中和B中匹配不到的值设为NULL。
示例
--左连接，对于branches表中的武汉，未在area表中匹配到对应的地区记录，则在结果集中将地区设为NULL
SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE b.branch_no LIKE '01%' OR b.branch_no LIKE '05%';
BRANCH_NAME AREA_NAME
------------------ ---------------
上海 华东
南京 华东
福州 华东
厦门 华东
武汉
长沙 华中
--右连接，对于area表中的华南地区，未在branches表中匹配到下属的城市记录，则在结果集中将城市设为NULL
SELECT b.branch_name, a.area_name
FROM branches b
RIGHT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE a.area_no IN ('01','03','05');
BRANCH_NAME AREA_NAME
------------------- -------------
厦门 华东
福州 华东
南京 华东
上海 华东
华南
长沙 华中
--全连接，对于area表中的华南地区，未在branches表中匹配到下属的城市记录，则在结果集中将城市设为NULL
SELECT b.branch_name, a.area_name
FROM branches b
FULL OUTER JOIN area a
ON a.area_no = b.area_no
WHERE a.area_no IN ('01','03','05');
BRANCH_NAME AREA_NAME
------------------- -------------
厦门 华东
福州 华东
南京 华东
上海 华东
华南
长沙 华中
# (+) operator
YashanDB支持通过(+)操作符指定外连接。
语法位置
(+)可以使用在where_clause和join_clause的condition子句的比较条件中。
使用方法
在比较条件中，(+)作用于某一个列字段上，表示如果比较条件无法满足，则对该列对应的表进行补空，与外连接同义，即比较条件对端所涉及的表（可能为一个或多个）对该表进行外连接。
使用限制
- (+)操作符只能作用于列字段上，不可以作用于表达式。
- 在where_clause条件中使用(+)操作符时，不能与ANSI标准的Join语法一起使用。
- 在join_clause条件中使用(+)操作符时，连接的两个表必须为普通表，不能为多表Join之后的中间结果。
- 不允许使用(+)操作符对两个表互相外连接。
- 某个条件中存在(+)操作符时，该条件的同级条件之间只允许使用AND关键词连接。
示例
--(+)作用于area表的area_no上，意为branches表对area表进行左外连接，结果集对area表中没有匹配上的数据进行补空。
--对于branches表中的武汉，未在area表中匹配到对应的地区记录，则在结果集中将地区设为NULL
SELECT a.area_name,b.branch_name
FROM area a, branches b
WHERE b.area_no = a.area_no(+)
AND (b.branch_no LIKE '01%' OR b.branch_no LIKE '05%');
AREA_NAME BRANCH_NAME
---------------- -------------
华东 上海
华东 南京
华东 福州
华东 厦门
武汉
华中 长沙
SELECT a.area_name, b.branch_name
FROM branches b
JOIN area a
ON b.area_no = a.area_no(+)
WHERE b.branch_no LIKE '01%' OR b.branch_no LIKE '05%';
AREA_NAME BRANCH_NAME
---------------- -------------
华东 上海
华东 南京
华东 福州
华东 厦门
武汉
华中 长沙
# where_clause
该语句用于指定查询的condition。
# hierarchical_query_clause
该语句用于指定层次（父子）关系的条件查询。通过START WITH获取根数据，通过 CONNECT BY指定父子关系，即可以将所有满足层次关系的数据全部查找出来。
分布式部署中不可使用本语句。
YashanDB提供如下虚拟列（非表结构中定义的列，但可以被当作列字段进行查询）供层次关系查询中使用：
LEVEL：LEVEL表示层次查询形成的树结构中的当前层数。该列值一直从1开始，即START WITH对应的数据的层数一直为1，之后子孙节点的LEVEL值依次递增。
CONNECT_BY_ISLEAF：CONNECT_BY_ISLEAF表示当前数据是否是层次查询形成的树结构中的叶子节点。若是叶子节点值为1，否则为0。
CONNECT_BY_ISCYCLE：CONNECT_BY_ISCYCLE表示在层次查询中当前数据是否会导致形成环，即根据层次关系，当前层数据是否存在其叶子节点也是其父节点。该列只有在同时指定NOCYCLE关键字时才有意义，当前数据会导致形成环则结果为1，否则为0。
YashanDB提供如下标识符用来指定层次关系中的某个节点属性：
- PRIOR：PRIOR操作符之后的参数将作为层次查询中的父节点。参数不能为虚拟列、层次查询函数、操作符、伪列及子查询。PRIOR通常应用于connect_by_clause中，且不能在start_with_clause中使用。
- CONNECT_BY_ROOT：CONNECT_BY_ROOT操作符之后的参数将作为在层次查询中的根节点。参数不能为虚拟列、层次查询函数、操作符、伪列及子查询。CONNECT_BY_ROOT不能在connect_by_clause和start_with_clause中使用。
YashanDB提供如下层次查询中的专用函数：
- SYS_CONNECT_BY_PATH：
SYS_CONNECT_BY_PATH(col_name, delimiter)，其中delimiter 表示分隔符，只能使用字面量。该函数将获取从根节点到当前节点的路径上所有节点名为col_name的值，中间用delimiter进行分隔开。SYS_CONNECT_BY_PATH不能在connect_by_clause、start_with_clause和group_by_clause中使用。
# connect_by_clause
通过CONNECT BY后跟的condition，指定层次关系查询条件，其中必须至少有一个条件用于指定父子关系，该条件通过PRIOR标识符识别。
# nocycle
当根据指定的条件关系获得的查询结果存在环时，如不指定NOCYCLE，系统将返回错误，指定时系统将忽略环的问题，仍返回所有数据。
# start_with_clause
通过START WITH后跟的condition，指定以满足该条件的数据作为层次关系中根节点，其LEVEL为1。
本语句可省略，则表示将所有数据均作为根节点进行层次关系查询。
使用本语句时，其前面或后面必须存在connect_by_clause，不能独立使用。
# order_siblings_by_clause
对同一父节点下的同一层次数据，通过ORDER SIBLINGS BY指定排序列和排序规则进行排序，本语句功能同order_by_clause。
使用本语句时，其前面必须存在connect_by_clause，不能独立使用。
示例
--建立包含层次关系的地区表area_info
CREATE TABLE area_info (id INT, father_id INT, area_name VARCHAR(20));
INSERT INTO area_info VALUES(1, 0, '广东');
INSERT INTO area_info VALUES(755, 1, '深圳');
INSERT INTO area_info VALUES(756, 755, '龙华');
INSERT INTO area_info VALUES(757, 755, '福田');
INSERT INTO area_info VALUES(2, 0, '浙江');
INSERT INTO area_info VALUES(571, 2, '杭州');
COMMIT;
--显示地区层次关系（指定根节点）
SELECT id, father_id, LEVEL,
CONNECT_BY_ROOT area_name AS name,
SYS_CONNECT_BY_PATH(area_name, '/') path
FROM area_info
CONNECT BY id<>757
AND PRIOR id = father_id
START WITH father_id = 0;
ID FATHER_ID LEVEL NAME PATH
----- ------------ ---------- ---------------- ------------------
1 0 1 广东 /广东
755 1 2 广东 /广东/深圳
756 755 3 广东 /广东/深圳/龙华
2 0 1 浙江 /浙江
571 2 2 浙江 /浙江/杭州
--显示地区层次关系（不指定根节点，则所有数据都将作为根节点）
SELECT id, father_id, LEVEL,
CONNECT_BY_ROOT area_name AS name,
SYS_CONNECT_BY_PATH(area_name, '/') path
FROM area_info
CONNECT BY id<>757
AND PRIOR id = father_id;
ID FATHER_ID LEVEL NAME PATH
------ ------------ ---------- ------------- ----------------
1 0 1 广东 /广东
755 1 2 广东 /广东/深圳
756 755 3 广东 /广东/深圳/龙华
755 1 1 深圳 /深圳
756 755 2 深圳 /深圳/龙华
2 0 1 浙江 /浙江
571 2 2 浙江 /浙江/杭州
571 2 1 杭州 /杭州
756 755 1 龙华 /龙华
757 755 1 福田 /福田
--显示节点是否为叶子节点及是否出现循环
SELECT id, father_id, LEVEL,
CONNECT_BY_ISLEAF AS leaf, CONNECT_BY_ISCYCLE AS iscycle
FROM area_info
CONNECT BY NOCYCLE id<>757
AND PRIOR id = father_id
START WITH father_id = 0;
ID FATHER_ID LEVEL LEAF ISCYCLE
------ ------------ --------- --------- -----------
1 0 1 false false
755 1 2 false false
756 755 3 true false
2 0 1 false false
571 2 2 true false
--按指定列对同层次数据排序
SELECT id, father_id, LEVEL,
CONNECT_BY_ROOT area_name AS name,
SYS_CONNECT_BY_PATH(area_name, '/') path
FROM area_info
CONNECT BY PRIOR id = father_id START WITH father_id = 0
ORDER SIBLINGS BY id DESC;
ID FATHER_ID LEVEL NAME PATH
------ ------------ -------- ------------ ---------------
2 0 1 浙江 /浙江
571 2 2 浙江 /浙江/杭州
1 0 1 广东 /广东
755 1 2 广东 /广东/深圳
757 755 3 广东 /广东/深圳/福田
756 755 3 广东 /广东/深圳/龙华
# group_by_clause
该语句用于对查询结果集进行按条件的分类聚集（Aggregation）。
# group by
在GROUP BY后定义分组列，多个列用,分隔，同时需满足如下规则：
- 在select_list中出现的查询列或列数据，必须为分组列或列数据的子集。
列是子集："SELECT col ,COUNT(*) FROM table GROUP BY col, col2；"
列数据是子集："SELECT LPAD(col), COUNT(*) FROM table GROUP BY col；"
- 查询列与分组列里出现函数时，函数的参数必须一致。
此语句错误："SELECT SUBSTR(col, 1,1), COUNT(*) FROM table GROUP BY SUBSTR(col, 1,2);"
- 如果同时出现了DISTINCT或ORDER BY子语句，则在它们中出现的列遵循上两条规则。
- 分组列不能为*（星号），SEQUENCE，子查询及聚集函数。
- 当分组列为数字时，与ORDER BY不同的是，本语句不会将数字释义成列的位置，而是作为字面量处理。
# having
HAVING子句约束SELECT查询语句中GROUP BY的结果，该约束应用于查询结果中的每个分组，类似WHERE条件应用于select_list。使用规则如下：
HAVING子句可以放在GROUP BY子句的前面或后面。
HAVING后的condition是一个布尔表达式，语法同WHERE子句中的filter_clause，但是它只能包含分组列、聚集函数（可以与select_list中的聚集函数不一致）、字面量和子查询（子查询中的列不需要为分组列）。其中分布式部署中不可以使用子查询。
如果没有GROUP BY，直接使用HAVING子句，表示该约束作用于整个查询结果，此时select_list和condition中不能出现分组列。
示例
--单机部署中，按年份对销售额进行汇总，HAVING后可以指定其他聚集函数，且可以指定对其他表的子查询
SELECT 1,year,month,CONCAT(year,month),SUM(amount) FROM sales_info HAVING MAX(amount)>100 GROUP BY year,month;
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ ---------------------
1 2000 12 200012 300
1 2001 01 200101 500
1 2015 03 201503 300
1 2015 11 201511 300
1 2021 05 202105 600
1 2021 10 202110 300
SELECT 1,year,month,CONCAT(year,month),SUM(amount)
FROM sales_info
GROUP BY year,month
HAVING SUM(amount)>(SELECT MIN(price*50) FROM product);
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ ---------------------
1 2021 05 202105 600
--HAVING定义分组列的条件
SELECT 1,year,month,CONCAT(year,month),SUM(amount)
FROM sales_info
GROUP BY year,month
HAVING year>'2015';
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ ---------------------
1 2021 05 202105 600
1 2021 10 202110 300
--单独使用HAVING语句，定义对整个查询结果的聚集约束条件,此时不能出现分组列
SELECT 1,SUM(amount)
FROM sales_info
HAVING MAX(amount)>100;
1 SUM(AMOUNT)
------------ ---------------------
1 2300
--分布式部署中，按年份对销售额进行汇总，HAVING后指定相同聚集函数
SELECT 1,year,month,CONCAT(year,month),SUM(amount)
FROM sales_info
HAVING SUM(amount)>300
GROUP BY year,month;
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ -----------
1 2001 01 200101 500
1 2021 05 202105 600
SELECT 1,year,month,CONCAT(year,month),SUM(amount)
FROM sales_info
GROUP BY year,month
HAVING SUM(amount)>500;
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ ---------------------
1 2021 05 202105 600
--HAVING定义分组列的条件
SELECT 1,year,month,CONCAT(year,month),SUM(amount)
FROM sales_info
GROUP BY year,month
HAVING year>'2015';
1 YEAR MONTH CONCAT(YEAR,MONTH) SUM(AMOUNT)
------------ ----- ----- ------------------ ---------------------
1 2021 05 202105 600
1 2021 10 202110 300
--单独使用HAVING语句，定义对整个查询结果的聚集约束条件,此时不能出现分组列
SELECT 1,SUM(amount)
FROM sales_info
HAVING SUM(amount)>300;
1 SUM(AMOUNT)
------------ ---------------------
1 2300
# order_by_clause
该语句用于对查询结果集按照排序键进行排序。
排序键可以为：
- expr：表达式
- position：select_list中的列项位置，默认按此项排序
- c_alias：select_list中为列项定义的别名
排序键不支持为如下类型：
- CLOB
- BLOB
- NCLOB
- BIT
- ROWID
- CURSOR
- JSON
- UDT
# nulls (first|last)
对于空值，用NULLS FIRST|NULLS LAST语句来指定空值排列在最前或最后。当未指定此语句时，对升序排列缺省为NULLS LAST，对降序排列缺省为NULLS FIRST。
# asc|desc
排序方式可指定为升序（ASC）或降序（DESC），未指定则缺省为升序。
示例
SELECT year,branch,SUM(amount) FROM sales_info GROUP BY year,branch ORDER BY SUM(amount)/SUM(quantity);
YEAR BRANCH SUM(AMOUNT)
----- ------ ---------------------
2015 0101 300
2015 0102 300
2000 0102 300
2021 0101 900
2001 0201 500
SELECT year,branch,SUM(amount) FROM sales_info GROUP BY year,branch ORDER BY 2;
YEAR BRANCH SUM(AMOUNT)
----- ------ ---------------------
2015 0101 300
2021 0101 900
2015 0102 300
2000 0102 300
2001 0201 500
# row_limiting_clause
该语句用于抓取查询结果集的指定行。
# limit
指定抓取的行数。LIMIT满足如下规则：
LIMIT可以为一个表达式，表达式的结果必须为一个数字。
如果LIMIT是负值，则将其视为0。
如果LIMIT是NULL，则不返回任何行。
如果LIMIT大于查询的行数，则返回所有行。
如果LIMIT是小数，则小数部分被截断。
# offset
指定抓取的起始行偏离数，缺省为第一行的偏离数0。OFFSET满足如下规则：
OFFSET可以为一个表达式，表达式的结果必须为一个数字。
如果OFFSET是负值，则将其视为0。
如果OFFSET是NULL，或者大于查询的行数，则不返回任何行。
如果OFFSET是小数，则小数部分被截断。
执行本语句将对优化器产生以下影响：
- 如果SQL中同时出现了order_by_clause和row_limiting_clause，优化器会将排序计划改写成TOP SORT计划。
- 如果SQL中同时出现了group_by_clause、distinct、order_by_clause、以及row_limiting_clause，优化器将基于cost可能生成TOP SORT GROUP/TOP SORT DISTINCT计划。
示例
--从branches表的第一行开始抓取前四行数据
SELECT * FROM branches LIMIT 4;
BRANCH_NO BRANCH_NAME AREA_NO ADDRESS
--------- ------------------- ------- -----------------
0001 深圳
0101 上海 01 上海市静安区
0102 南京 01 City of Nanjing
0103 福州 01
--从branches表的第四行开始抓取前四行数据
SELECT * FROM branches LIMIT 4 OFFSET 3;
BRANCH_NO BRANCH_NAME AREA_NO ADDRESS
--------- ------------------- ------- -----------------
0103 福州 01
0104 厦门 01 Xiamen
0401 北京 04
0402 天津 04
# for_update_clause
该语句用于将查询结果集对应的行记录锁定，锁定后其他用户将只能对这些行进行查询，而不能执行锁定或修改等操作，直至当前事务结束。
本语句只作用于单机HEAP/TAC表。
只能在顶层的select语句中指定FOR UPDATE，子查询中不允许出现FOR UPDATE。
本语句不可与DISTINCT，GROUP BY，FLASHBACK，聚集函数，游标表达式等组合使用。
# of column_name
用OF后的列字段名来指定锁定该列字段所在的表，而不需要将FROM后的表都锁定。 本语句一般在多表连接情况下使用，指定对多表中特定的某些表执行FOR UPDATE。省略本语句时锁定当前查询中所有涉及到的表对象对应的记录。
通过column_name标识出其所在的表对象，必须是一个真实的列字段名称，不能为别名。
可联合表、视图、同义词，或者在FROM语句中定义的别名，来指定列字段名。
# wait|no wait|skip locked
指定当要锁定的记录里至少有部分行正被其他用户锁定时的操作，缺省为一直等待到那些行被解锁后，再执行操作并返回。
- WAIT ntimes：等待指定的时间，ntimes的单位为秒，超过此时间后报错。
- NO WAIT：不等待且不执行操作，直接返回。
- SKIP LOCKED：跳过被锁定的行，继续执行其他的行。
本语句用来定义行锁时的操作，当出现表锁时，直接进入表锁等待。
示例
SELECT * FROM (SELECT TRIM(address) address FROM branches) FOR UPDATE;
SELECT * FROM area a,branches b WHERE a.area_no=b.area_no AND a.area_no='01' FOR UPDATE OF a.DHQ; --此时仅锁定area表中的对应行
SELECT * FROM (SELECT TRIM(address) address FROM branches) FOR UPDATE NOWAIT;
SELECT * FROM (SELECT TRIM(address) address FROM branches) FOR UPDATE WAIT 10;
SELECT * FROM (SELECT TRIM(address) address FROM branches) FOR UPDATE SKIP LOCKED;
下载文档
复制链接