数据查询语句

数据查询是数据库的核心操作,DM_SQL语言提供了功能丰富的查询方式,满足实际应用需求。几乎所有的数据库操作均涉及到查询,因此熟练掌握查询语句的使用是数据库从业人员必须掌握的技能。

在DM_SQL语言中,有的定义语法中也包含查询语句,如视图定义语句、游标定义语句等。为了区别,我们将这类出现在其它定义语句中的查询语句称查询说明。

每种查询都有适用的场景,使用得当会大大提高查询效率。为方便用户的使用,本章对DM_SQL语言支持的查询方式进行讲解,测例中所用基表及各基表中预先装入的数据参见第2章,各例的建表者均为用户SYSDBA。

查询语句的语法如下:

<查询表达式>::= 
		<simple_select>|
      <select_clause> <ORDER BY子句> <LIMIT限制条件> <FOR UPDATE 子句>  |
 	   <select_clause> <ORDER BY子句> [<FOR UPDATE 子句>] [<LIMIT限制条件>]	|
		<select_clause> <LIMIT子句> <ORDER BY 子句> [<FOR UPDATE 子句>]	|
		<select_clause> <FOR UPDATE 子句> [<LIMIT限制条件>]	|
		<select_clause> <LIMIT限制条件>[<FOR UPDATE 子句>]
<simple_select> ::=
		<query_exp_with>|
		<select_clause>  <UNION| EXCEPT | MINUS | INTERSECT > [ALL | DISTINCT | UNIQUE] [CORRESPONDING [BY (<列名> {,<列名>})]] <select_clause>
<select_clause>::=
		<simple_select>|
		(<查询表达式>)|
		(<select_clause>)
<ORDER BY 子句>::= ORDER [SIBLINGS] BY< order_by_list>
<order_by_list>::= < order_by_item >{,<order_by_item>}
<order_by_item>::=  <exp> [ASC | DESC] [NULLS FIRST|LAST]
<exp >::=<无符号整数> | <列说明> | <值表达式>
<FOR UPDATE 子句> ::= 
		FOR READ ONLY| 
		FOR UPDATE [OF <选择列表>] [NOWAIT | WAIT N | SKIP LOCKED]
<LIMIT限制条件> ::=<LIMIT子句>|< ROW_LIMIT子句>
<LIMIT子句>::=LIMIT<  <记录数> | <<记录数>,<记录数>>| <<记录数> OFFSET <偏移量>> >
<记录数>::=<整数>
<偏移量>::=<整数>
< ROW_LIMIT子句>::= [OFFSET <offset> <ROW | ROWS> ] [<FETCH说明>]
<FETCH说明>::= FETCH <FIRST | NEXT> <大小> [PERCENT] < ROW | ROWS > <ONLY| WITH TIES>
<query_exp_with>::=[<WITH 子句>] SELECT	[<HINT 子句>]	[ALL | DISTINCT | UNIQUE] [<TOP子句>] <选择列表>[<bulk_or_single_into_null>] <select_tail>
<选择列表> ::= [[<模式名>.]<基表名> | <视图名> .] * | <值表达式> [[AS] <列别名>]
             {,[[<模式名>.]<基表名> | <视图名>.] * | <值表达式> [[AS] <列别名>]}
<WITH 子句> ::= [<WITH FUNCTION子句>] [WITH CTE子句]   请参考第4.4节 WITH 子句
<HINT 子句> ::=/*+ hint{hint}*/   
<TOP子句>::=
	TOP <n> | 
	<<n1>,<n2>>| 
	<n> PERCENT| 
	<n> WITH TIES| 
	<n> PERCENT WITH TIES
<n>::=整数(>=0)
<bulk_or_single_into_null>::=<bulk_or_single_into>  <变量名 >{,<变量名>}
<bulk_or_single_into>::= <INTO>| <BULK COLLECT INTO>
<select_tail>::=
 <FROM子句>
	[<WHERE 子句>]
[<层次查询子句>]
	[<GROUP BY子句>]
	[<HAVING子句>]
<FROM子句>::= FROM <表引用>{,<表引用>}
<表引用>::=<普通表>|<连接表>
<普通表>::=<普通表1>|<普通表2>|<普通表3>|<ARRAY<数组>>
<普通表1>::=<对象名> [<SAMPLE子句>][[AS <别名>] <PIVOT子句>][[AS <别名>] <UNPIVOT子句>] [<闪回查询>] [[AS] <别名>]
<普通表 2>::=(<查询表达式>)[[AS <别名>] <PIVOT子句>][[AS <别名>] <UNPIVOT子句>] [<闪回查询>][[AS] <表别名> [<新生列>]]
<普通表3>::=[<模式名>.]<<基表名>|<视图名>>(<选择列>)[[AS <别名>] <PIVOT子句>] [[AS <别名>] <UNPIVOT子句>] [<闪回查询>] [[AS] <表别名> [<派生列表>]]
<对象名>::=<本地对象> | <索引> | <分区表>
<本地对象>::=[<模式名>.]<基表名|视图名>
<索引>::=[<模式名>.]<基表名> INDEX <索引名>  
<分区表>::=
		[<模式名>.]<基表名> PARTITION (<一级分区名>) |
        [<模式名>.]<基表名> PARTITION FOR (<表达式>,{<表达式>})|
		[<模式名>.]<基表名> SUBPARTITION (<子分区名>)|
        [<模式名>.]<基表名> SUBPARTITION FOR (<表达式>,{<表达式>})
<选择列>::=<列名>[{,<列名> }]
<派生列表>::=(<列名>[{,<列名>}])
<SAMPLE子句>::=
		SAMPLE(<表达式>) |
		SAMPLE(<表达式>) SEED (<表达式>) |
		SAMPLE BLOCK(<表达式>)  |
		SAMPLE BLOCK(<表达式>) SEED (<表达式>)
<闪回查询>::= <闪回查询子句>|<闪回版本查询子句>请参考第17章 闪回查询
<闪回查询子句>::=
		WHEN <TIMESTAMP time_exp> | 
		AS OF <TIMESTAMP time_exp> |
		AS OF <SCN lsn>
<闪回版本查询子句>::=VERSIONS BETWEEN <TIMESTAMP time_exp1 AND time_exp2> | <SCN lsn1 AND lsn2>
<连接表>::=[(]<交叉连接>|<限定连接>[)]
<交叉连接>::=<表引用> CROSS JOIN <<普通表>|(<连接表>)>
<限定连接>::=<表引用> [<PARTITION BY子句>] [NATURAL] [<连接类型>] JOIN <<普通表>|(<连接表>)> [<PARTITION BY子句>]
<连接类型>::=
		[<内外连接类型>] INNER|
		<内外连接类型> [OUTER]
<内外连接类型>::=LEFT|RIGHT|FULL
<连接条件>::=<条件匹配>|<列匹配>
<条件匹配>::=ON<搜索条件>
<列匹配>::=USING(<连接列列名>{, <连接列列名>})
<WHERE子句> ::=
	WHERE <搜索条件>| 
	< WHERE CURRENT OF子句>   
<搜索条件>::=<逻辑表达式>
< WHERE CURRENT OF子句>::=WHERE CURRENT OF <游标名>
<层次查询子句>::=
		CONNECT BY [NOCYCLE] <连接条件>[START WITH <起始条件> ] |
		START WITH <起始条件> CONNECT BY [NOCYCLE] <连接条件>
<连接条件>::=<逻辑表达式>
<起始条件>::=<逻辑表达式>
<GROUP BY子句> ::= GROUP BY <group_by项>{,<group_by项>}
<group_by项>::=<分组项>|<ROLLUP项>|<CUBE项>|<GROUPING SETS项>
<分组项>::=<值表达式>
<ROLLUP项>::=ROLLUP (<分组项>)
<CUBE项>::=CUBE (<分组项>)
<GROUPING SETS项>::=GROUPING SETS(<GROUP项>{,<GROUP项>})
<GROUP项>::=
		<分组项> |
		(<分组项>{,<分组项>})|
		()
<HAVING 子句> ::= HAVING <搜索条件>
<PARTITION BY子句> ::=PARTITION BY (<表列名>{,<表列名>})
<PIVOT子句> ::= PIVOT [XML] ( (<集函数> {,<集函数>}) <pivot_for_clause> IN (<pivot_in_clause>) )
<pivot_for_clause> ::= 
		FOR <列名> |
		FOR (<列名>{,<列名>})
<pivot_in_clause> ::= <表达式> [ [AS]  <别名>] {,<表达式> [[AS]  <别名>]}  |
(<表达式>) [ [AS]  <别名>] {,(<表达式> )[[AS]  <别名>]}  |
                          <select_clause> | 
ANY
<UNPIVOT子句> ::= UNPIVOT [<include_null_clause>](<unpivot_val_col_lst>
<pivot_for_clause> IN (<unpivot_in_clause_low> ))
<include_null_clause> ::=
		INCLUDE NULLS | 
		EXCLUDE NULLS
<unpivot_val_col_lst>  ::= 
		<表达式> |
		(<表达式>{,<表达式>})
<unpivot_in_clause_low>  ::= <unpivot_in_clause>{,<unpivot_in_clause>}
<unpivot_in_clause>  ::= 
		<列名> [AS <别名>]  |
		(<列名>{,<列名>}) [ AS (<别名>{,<别名>})] |
		(<列名>{,<列名>}) AS <别名>

参数

  1. ALL 返回所有被选择的行,包括所有重复的拷贝,缺省值为ALL;
  2. DISTINCT 从被选择出的具有重复行的每一组中仅返回一个这些行的拷贝,与UNIQUE等价。对于集合算符:UNION,缺省值为DISTINCT,DISTINCT与UNIQUE等价;对于EXCEPT/MINUS和INTERSECT:操作的两个表中数据类型和个数要完全一致。其中,EXCEPT和MINUS集合算符功能完全一样,返回两个集合的差集;INTERSECT返回两个集合的交集(去除重复记录);
  3. CORRESPONDING 用于指定列名链表,通过指定列名(或列名的别名)链表来对两个查询分支的查询项进行筛选。无论分支中有多少列,最终的结果集只包含CORRESPONDING指定的列。查询分支和CORRESPONDING的关系为:<查询分支1> CORRESPONDING [BY (<列名> {,<列名>})]
    <查询分支2>。如果CORRESPONDING指定了列名但两个分支中没有相同列名的查询项则报错,如果CORRESPONDING没指定列名,则按照第一个分支的查询项列名进行筛选;例如:select c1, c2, c3 from t1 union all corresponding by (c1,c2) select d1, d2 c1, d3 c2 from t2;
  4. hint 用于优化器提示,可以出现在语句中任意位置,具体可使用的hint可通过V$HINT_INI_INFO动态视图查询;
  5. <模式名> 被选择的表和视图所属的模式,缺省为当前模式;
  6. <基表名> 被选择数据的基表的名称;
  7. <视图名> 被选择数据的视图的名称;
  8. * 指定对象的所有的列;
  9. <值表达式> 可以为一个<列引用>、<集函数>、<函数>、<标量子查询>或<计算表达式>等等;
  10. <列别名> 为列表达式提供不同的名称,使之成为列的标题,列别名不会影响实际的名称,别名在该查询中被引用;
  11. <相关名> 给表、视图提供不同的名字,经常用于求子查询和相关查询的目的;
  12. <列名> 指明列的名称;
  13. <WHERE 子句> 限制被查询的行必须满足条件,如果忽略该子句,DM从在FROM子句中的表、视图中选取所有的行;其中,<WHERE CURRENT OF子句>专门用于游标更新、删除中,用来限定更新、删除与游标有关的数据行。
  14. <HAVING 子句> 限制所选择的行组所必须满足的条件,缺省为恒真,即对所有的组都满足该条件;
  15. <无符号整数> 指明了要排序的<值表达式>在SELECT后的序列号;
  16. <列说明> 排序列的名称;
  17. ORDER SIBLINGS BY 必须与CONNECT BY一起配合使用。可用于指定层次查询中相同层次数据返回的顺序。
  18. ASC 指明为升序排列,缺省为升序;
  19. DESC 指明为降序排列;
  20. nulls first 指定排序列的null值放在最前面,不受asc和desc的影响,缺省的是nulls first;
  21. nulls last 指定排序列的null值放在最后面,不受asc和desc的影响;
  22. <PARTITION BY子句> 指明分区外连接中的分区项,最多支持255个列;仅允许出现在左外连接右侧表和右外连接中的左侧表,且不允许同时出现,详见4.2.7;
  23. BULK COLLECT INTO的作用是将检索结果批量的、一次性的赋给集合变量。与每次获取一条数据,并每次都要将结果赋值给一个变量相比,可以很大程度上的节省开销。使用BULK COLLECT后,INTO后的变量必须是集合类型。

图例

查询表达式

查询表达式

simple_select

simple_select

query_exp_with

query_exp_with

select_clause

select_clause

ORDER BY 子句

ORDER BY 子句

FOR UPDATE 子句

FOR UPDATE 子句

LIMIT限制条件

LIMIT限制条件

LIMIT子句

LIMIT子句

ROW LIMIT 子句

ROW LIMIT 子句

FETCH说明

FETCH说明

FROM子句

FROM子句

表引用

表引用

普通表1

普通表1

普通表2

普通表2

普通表3

普通表3

连接表

连接表

交叉连接

交叉连接

限定连接

限定连接

连接类型

连接类型

内外连接类型

内外连接类型

连接条件

连接条件

WHERE子句

WHERE子句

层次查询子句

层次查询子句

GROUP BY子句

GROUP BY子句

ROLLUP项

ROLLUP项

CUBE项

CUBE项

GROUPING SETS项

GROUPING SETS项

GROUP项

GROUP项

HAVING子句

HAVING子句

WITH 子句

WITH 子句

WITH FUNCTION子句

WITH FUNCTION子句

WITH CTE子句

WITH CTE子句

闪回查询

闪回查询

选择列表

选择列表

bulk_or_single_into_null

bulk_or_single_into_null

PIVOT子句

PIVOT子句

pivot_for_clause

pivot_for_clause

pivot_in_obj

pivot_in_obj

UNPIVOT子句

UNPIVOT子句

unpivot_in_clause

unpivot_in_clause

使用说明

1.<选择列表>中最多可包含1024个查询项,且查询记录的长度限制不能超过块长的一半;

2.<FROM子句>中最多可引用100张表;

3.WHERE<搜索条件>用于设置对于行的检索条件。不在规定范围内的任何行都从结果集中去除;

4.查询语句调用的函数中,不能包含任何增删改操作(包括函数间接调用其它过程\函数产生的增删改操作);

5.EXCEPT/MINUS/INTERSECT集合运算中,查询列不能含有BLOB、CLOB或IMAGE、TEXT等大字段类型;

6.对于<PIVOT子句>的使用,存在以下限制:

  1. <pivot_in_clause>中,只有多个表达式才使用(),单个表达式时不支持();
  2. <pivot_for_clause>中,只有多个列才使用(),单个列时不支持();
  3. 多个集函数的时候需要为每个集函数定义别名;
  4. <pivot_in_clause>中的表达式仅支持常量表达式;
  5. <pivot_for_clause>中的列个数与<pivot_in_clause>中的表达式个数应一致;
  6. <pivot_in_clause>中的子查询与ANY只有在指定XML时才生效;
  7. XML功能的结果集返回类型是CLOB;
  8. 不支持COVAR_SAMP、COVAR_POP、LISTAGG/LISTAGG2集函数;
  9. GROUP BY项(包括隐含的)不能超过255个;
  10. <pivot_in_clause>中表达式的个数与<pivot_for_clause>中集函数的个数乘积不能超过1024。

7.对于<UNPIVOT子句>的使用,存在以下限制:

  1. unpivot_in指定的列数据类型要保持一致;
  2. unpivot_in的AS 项必须是常量表达式;
  3. 仅支持对单表、视图、DBLINK进行UNPIVOT转换;
  4. unpivot_in_clause中,只有多个表达式才使用(),单个表达式时不支持();
  5. unpivot_for项个数与unpivot_in中的AS项表达式数目保持一致;
  6. unpivot_val_col_lst项个数与unpivot_in中待转换的列数目保持一致;
  7. INI参数UNPIVOT_ORDER_FLAG控制输出结果的顺序,按照不包含在UNPIVOT的列进行排序。值含义: 1:排序; 0:不排序,缺省为0;
  8. UNPIVOT中自定义列名不能为保留字;
  9. unpivot_in_clause中指定的转换列数目最多256列,同时不能超过INI参数N_PARSE_LEVEL的值;
  10. UNPIVOT不支持ROLLUP查询,不能同时存在PIVOT子句;
  11. UNPIVOT的列不能是ROWID\TRXID列。

4.1 单表查询

SELECT语句仅从一个表/视图中检索数据,称单表查询。即<FROM子句>中<普通表>使用的是[<模式名>.]<基表名|视图名>。

4.1.1 简单查询

查询所有图书的名字、作者及当前销售价格,并消去重复。方差集函数中参数 expr 为 < 列名 > 或 < 值

SELECT DISTINCT NAME, AUTHOR, NOWPRICE FROM PRODUCTION .PRODUCT;

其中,DISTINCT保证重复的行将从结果中去除。若允许有重复的元组,改用ALL来替换DISTINCT,或直接去掉DISTINCT即可。

查询结果如表4.1.1所示。(注:除带Order By的查询外,本书所示查询结果中各元组的顺序与实际输出结果中的元组顺序不一定一致。)

表4.1.1
NAME AUTHOR NOWPRICE
长征 王树增 37.7000
工作中无小事 陈满麒 11.4000
红楼梦 曹雪芹,高鹗 15.2000
老人与海 海明威 6.1000
鲁迅文集(小说、散文、杂文)全两册 鲁迅 20.0000
射雕英雄传(全四册) 金庸 21.7000
数据结构(C语言版)(附光盘) 严蔚敏,吴伟民 25.5000
水浒传 施耐庵,罗贯中 14.3000
突破英文基础词汇 刘毅 11.1000
噼里啪啦丛书(全7册) (日)佐佐木洋子 42.0000

当用户需要查出所有列的数据,且各列的显示顺序与基表中列的顺序也完全相同时,为了方便用户提高工作效率,SQL语言允许用户将SELECT后的<值表达式>省略为*。

SELECT * FROM PERSON.PERSON;

等价于:

SELECT PERSONID, NAME, SEX, EMAIL, PHONE FROM PERSON.PERSON;

其查询结果是模式PERSON中基表PERSON的一份拷贝,结果从略。

1)查询tt表中有的,kk表中没有的数据;2)查询tt表和kk表都有的数据。

CREATE TABLE TT(A INT);
INSERT INTO TT VALUES(5);
INSERT INTO TT VALUES(6);
INSERT INTO TT VALUES(7);

CREATE TABLE KK(A INT);
INSERT INTO KK VALUES(5);
INSERT INTO KK VALUES(5);
INSERT INTO KK VALUES(6);
INSERT INTO KK VALUES(8);
  1. 使用MINUS或EXCEPT查询tt表中有的,kk表中没有的数据。
SELECT * FROM TT MINUS SELECT * FROM KK;  等价于 SELECT * FROM TT EXCEPT SELECT * FROM KK;

其查询结果是:

A
7

2)使用INTERSECT查询TT表中和KK表中都有的数据。

select * from tt INTERSECT select * from kk;

其查询结果是:

A
5
6

4.1.2 带条件查询

带条件查询是指在指定表中查询出满足条件的元组。该功能是在查询语句中使用WHERE子句实现的。WHERE子句常用的查询条件由谓词和逻辑运算符组成。谓词指明了一个条件,该条件求解后,结果为一个布尔值:真、假或未知。

逻辑算符有:AND,OR,NOT。

谓词包括比较谓词(=、>、<、>=、<=、<>),BETWEEN谓词、IN谓词、LIKE谓词、NULL谓词、EXISTS谓词。

1.使用比较谓词的查询

当使用比较谓词时,数值数据根据它们代数值的大小进行比较,字符串的比较则按序对同一顺序位置的字符逐一进行比较。若两字符串长度不同,短的一方应在其后增加空格,使两串长度相同后再作比较。

给出当前销售价格在10~20元之间的所有图书的名字、作者、出版社和当前价格。

SELECT NAME, AUTHOR, PUBLISHER, NOWPRICE FROM PRODUCTION.PRODUCT WHERE NOWPRICE>=10 AND NOWPRICE<=20;

查询结果如表4.1.2所示。

表4.1.2
NAME AUTHOR PUBLISHER NOWPRICE
红楼梦 曹雪芹,高鹗 中华书局 15.2000
水浒传 施耐庵,罗贯中 中华书局 14.3000
鲁迅文集(小说、散文、杂文)全两册 鲁迅 20.0000
工作中无小事 陈满麒 机械工业出版社 11.4000
突破英文基础词汇 刘毅 外语教学与研究出版社 11.1000

2.使用BETWEEN谓词的查询

给出当前销售价格在10~20元之间的所有图书的名字、作者、出版社和当前价格。

SELECT NAME, AUTHOR, PUBLISHER, NOWPRICE FROM PRODUCTION.PRODUCT WHERE NOWPRICE BETWEEN 10 AND 20;

此例查询与上例完全等价,查询结果如上表所示。在BETWEEN谓词前面可以使用NOT,以表示否定。

3.使用IN谓词的查询

谓词IN可用来查询某列值属于指定集合的元组。

查询出版社为中华书局或人民文学出版社出版的图书名称与作者信息。

SELECT NAME, AUTHOR FROM PRODUCTION.PRODUCT WHERE PUBLISHER IN ('中华书局', '人民文学出版社');

查询结果如表4.1.3所示。

表4.1.3
NAME AUTHOR
红楼梦 曹雪芹, 高鹗
水浒传 施耐庵, 罗贯中
长征 王树增

在IN谓词前面也可用NOT表示否定。

4.使用LIKE谓词的查询

LIKE谓词一般用来进行字符串的匹配。我们先用实例来说明LIKE谓词的使用方法。

查询第一通讯地址中第四个字开始为“关山”且以202结尾的地址。

SELECT ADDRESSID, ADDRESS1, CITY, POSTALCODE FROM PERSON.ADDRESS WHERE ADDRESS1 LIKE '___关山%202';

查询结果如表4.1.4所示。

表4.1.4
ADDRESSID ADDRESS1 CITY POSTALCODE
13 洪山区关山春晓55-1-202 武汉市洪山区 430073
14 洪山区关山春晓10-1-202 武汉市洪山区 430073
15 洪山区关山春晓11-1-202 武汉市洪山区 430073

由上例可看出,LIKE谓词的一般使用格式为:

<列名> LIKE <匹配字符串常数>

其中,<列名>必须是可以转化为字符类型的数据类型的列。对于一个给定的目标行,如果指定列值与由<匹配字符串常数>给出的内容一致,则谓词结果为真。<匹配字符串常数>中的字符可以是一个完整的字符串,也可以是百分号“%”和下划线“_”,“%”和“_”称通配符。“%”代表任意字符串(也可以是空串);“_”代表任何一个字符。

因此,上例中的SELECT语句将从ADDRESS表中检索出第一通讯地址中第四个字开始为“关山”且以202结尾的地址情况。从该例我们可以看出LIKE谓词是非常有用的。使用LIKE谓词可以找到所需要的但又记不清楚的那样一些信息。这种查询称模糊查询或匹配查询。为了加深对LIKE谓词的理解,下面我们再举几例:

ADDRESS1 LIKE '%洪山%'

如果ADDRESS1的值含有字符“洪山”,则该谓词取真值。

POSTALCODE LIKE '43__7_'

如果POSTALCODE的值由六个字符组成且前两个字符为43,第五个字符为7,则该谓词取真值。

CITY LIKE '%汉阳_'

如果CITY的值中倒数第三和第二个字为汉阳,则该谓词取真值。

ADDRESS1 NOT LIKE '洪山%'

如果ADDRESS1的值的前两个字不是洪山,则该谓词取真值。

阅读以上的例子,读者可能就在想这样一个问题:如果<匹配字符串常数>中所含“%”和“_”不是作通配符,而只是作一般字符使用应如何表达呢?为解决这一问题,SQL语句对LIKE谓词专门提供了对通配符“%”和“_”的转义说明,这时LIKE谓语使用格式为:

<列名> LIKE '<匹配字符串常数>' [ESCAPE <转义字符>]

其中,<转义字符>指定了一个字符,当该字符出现在<匹配字符串常数>中时,用以指明紧跟其后的“%”或“_”不是通配符而仅作一般字符使用。

查询第一通讯地址以C1_501结尾的地址,则LIKE谓词应为:

SELECT ADDRESSID, ADDRESS1, CITY, POSTALCODE FROM PERSON.ADDRESS WHERE ADDRESS1 LIKE '%C1*_501' ESCAPE '*';

在此例中,*被定义为转义字符,因而在<匹配字符串常数>中*号后的下划线不再作通配符,而是普通字符。

查询结果如表4.1.5所示。

表4.1.5
ADDRESSID ADDRESS1 CITY POSTALCODE
16 洪山区光谷软件园C1_501 武汉市洪山区 430073

为避免错误,转义字符一般不要选通配符“%”、“_”或在<匹配字符串常数>中已出现的字符。

5.使用.ROW进行LIKE谓词的查询

LIKE谓词除支持使用列的计算外,还支持通过ROW保留字对表或视图进行LIKE计算。该查询依次对表或视图中所有字符类型的列进行LIKE计算,只要有一列符合条件,则返回TRUE。

其语法的一般格式为

<表名>.ROW  LIKE  <匹配字符串>  [ ESCAPE  <转义字符>]

查询评论中哪些与曹雪芹有关

SELECT * FROM PRODUCTION. PRODUCT_REVIEW WHERE  PRODUCT_REVIEW.ROW LIKE '%曹雪芹%';

该语句等价于

SELECT * FROM PRODUCTION. PRODUCT_REVIEW WHERE NAME LIKE '%曹雪芹%' OR EMAIL LIKE '%曹雪芹%' OR COMMENTS LIKE '%曹雪芹%';

6.使用NULL谓词的查询

空值是未知的值。当列的类型为数值类型时,NULL并不表示0;当列的类型为字符串类型时,NULL也并不表示空串。因为0和空串也是确定值。NULL只能是一种标识,表示它在当前行中的相应列值还未确定或未知,对它的查询也就不能使用比较谓词而须使用NULL谓词。

查询哪些人员的EMAIL地址为NULL。

SELECT NAME, SEX, PHONE FROM PERSON.PERSON WHERE EMAIL IS NULL;

在NULL谓词前,可加NOT表示否定。

7. 组合逻辑

可以用逻辑算符(AND,OR,NOT)与各种谓词相组合生成较复杂的条件查询。

查询当前销售价格低于15元且折扣低于7或出版社为人民文学出版社的图书名称和作者。

SELECT NAME, AUTHOR FROM PRODUCTION.PRODUCT WHERE NOWPRICE < 15 AND DISCOUNT < 7 OR PUBLISHER='人民文学出版社';

查询结果如表4.1.6所示。

表4.1.6
NAME AUTHOR
老人与海 海明威
长征 王树增
工作中无小事 陈满麒

4.1.3 集函数

为了进一步方便用户的使用,增强查询能力,SQL语言提供了多种内部集函数。集函数又称库函数,当根据某一限制条件从表中导出一组行集时,使用集函数可对该行集作统计操作并返回单一统计值。

集函数经常与SELECT语句的GROUP BY子句一同使用。集函数对于每个分组只返回一行数据。

4.1.3.1 函数分类

集函数可分为10类:

  1. COUNT(*);
  2. 相异集函数 AVG|MAX|MIN|SUM|COUNT(DISTINCT<列名>);
  3. 完全集函数 AVG|MAX|MIN| COUNT|SUM([ALL]<值表达式>);
  4. 方差集函数VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV;
  5. 协方差函数 COVAR_POP、COVAR_SAMP、CORR;
  6. 首行函数FIRST_VALUE;
  7. 求区间范围内最大值集函数AREA_MAX;
  8. FIRST/LAST集函数 AVG|MAX|MIN| COUNT|SUM([ALL] <值表达式>) KEEP (DENSE_RANK FIRST|LAST ORDER BY 子句);ORDER BY 子句语法参考第4.7节;
  9. 字符串集函数LISTAGG/LISTAGG2、WM_CONCAT;
  10. 求中位数函数MEDIAN。

4.1.3.2 使用说明

在使用集函数时要注意以下几点:

  1. 相异集函数与完全集函数的区别是:相异集函数是对表中的列值消去重复后再作集函数运算,而完全集函数是对包含列名的值表达式作集函数运算且不消去重复。缺省情况下,集函数均为完全集函数;
  2. 集函数中的自变量可以是集函数,但最多只能嵌套2层。嵌套分组函数的时候,需要使用GROUP BY;
  3. AVG、SUM的参数必须为数值类型;MAX、MIN的结果数据类型与参数类型保持一致;对于SUM函数,如果参数类型为BYTE、BIT、SMALLINT或INTEGER,那么结果类型为INTEGER,如果参数类型为NUMERIC、DECIMAL、FLOAT和DOUBLE PRECISION,那么结果类型为DOUBLE PRECISION;COUNT结果类型统一为BIGINT;

对于AVG函数,其参数类型与结果类型对应关系如表4.1.7所示:

表4.1.7
参数类型 结果类型
tinyint dec(38,6)
smallint dec(38,6)
int dec(38,6)
bigint dec(38,6)
float double
double double
dec(x,y) number
  1. 方差集函数中参数expr为<列名>或<值表达式>,具体用法如下:

    1)VAR_POP(expr) 返回expr的总体方差。其计算公式为:

    2)**VAR_SAMP(expr)**返回expr的样本方差,如果expr的行数为1,则返回NULL。其计算公式为:

    3)**VARIANCE(expr)**返回expr的方差,如果expr的行数为1,则返回为0,行数大于1时,与var_samp函数的计算公式一致;

    4)**STDDEV_POP(expr)**返回expr的标准差,返回的结果为总体方差的算术平方根,即var_pop函数结果的算术平方根。公式如下:

    5)**STDDEV_SAMP(expr)**返回expr的标准差,返回的结果为样本方差的算术平方根,即var_samp函数结果的算术平方根,所以如果expr的行数为1,stddev_samp返回NULL;

    6)**STDDEV(expr)**与stddev_samp基本一致,差别在于,如果expr的行数为1,stddev返回0,即variance函数结果的算术平方根。公式如下:

  2. 协方差集函数中参数expr1和expr2为<列名>或<值表达式>,具体用法如下:

    1)COVAR_POP(expr1, expr2) 返回expr1和expr2的总体协方差。其计算公式为:

    2)**COVAR_SAMP(expr1, expr2)**返回expr1和expr2的样本协方差,如果expr的行数为1,则返回NULL。其计算公式为:

    3)**CORR(expr1, expr2)**返回expr1和expr2的相关系数,如果expr的行数为1,则返回NULL。其计算公式为:

    其中NVL2(expr1, expr2,expr3)表示如果表达式expr1非空,NVL2返回expr2;如果表达expr1为空,NVL2返回expr3。

  3. FIRST_VALUE集函数,返回查询项的第一行记录;

  4. AREA_MAX(EXP, LOW, HIGH) 在区间[LOW,HIGH]的范围内取exp的最大值。如果exp不在该区间内,则返回LOW值。如果LOW或HIGH为NULL,则返回NULL。exp为<变量>、<常量>、<列名>或<值表达式>。参数exp类型为TINYINT、SMALLINT、INT、BIGINT、DEC、FLOAT、DOUBLE、DATE、TIME、DATETIME、BINARY、VARBINARY、INTERVAL YEAR TO MONTH、INTERVAL DAY TO HOUR、TIME WITH TIME ZONE、DATETIEM WITH TIME ZONE。LOW和HIGH的数据类型和exp的类型一致,如果不一致,则转换为exp的类型,不能转换则报错。AREA_MAX集函数返回值定义如下:

表4.1.8 没有GROUP BY的情况
EXP 集合 是否有在[LOW, HIGH] 区间内的非空值 结果
空集 - LOW
非空 LOW
非空 在[LOW,HIGH]区间的最大值
表4.1.9 有GROUP BY的情况
分组前结果 在[LOW, HIGH] 区间内是否非空值 结果
空集 -- 整个结果为空集
非空集 在[LOW,HIGH]区间的最大值
非空集 LOW
  1. FIRST/LAST集函数 首先根据SQL语句中的GROUP BY分组(如果没有指定分组则所有结果集为一组),然后在组内进行排序。根据FIRST/LAST计算第一名(最小值)或者最后一名(最大值)的集函数值,排名按照奥林匹克排名法;

  2. LISTAGG/LISTAGG2(exp1, exp2)集函数 首先根据SQL语句中的GROUP BY分组(如果没有指定分组则所有结果集为一组),然后在组内按照WITHIN GROUP中的ORDER BY进行排序(没有指定排序则按数据组织顺序),最后将表达式exp1用表达式exp2串接起来。表达式exp1支持和DISTINCT关键字一起使用,表示对组内的exp1进行去重操作后再进行串接。LISTAGG2跟LISTAGG的功能是一样的,区别就是LISTAGG2返回的是CLOB类型,LISTAGG返回的是VARCHAR类型。

    LISTAGG的用法:

    <LISTAGG>([DISTINCT] <参数>[,<参数>]) [WITHIN GROUP(<ORDER BY项>)]
    

    LISTAGG2的用法:

    <LISTAGG2>([DISTINCT] <参数>[,<参数>]) [WITHIN GROUP(<ORDER BY项>)]
    
  3. MEDIAN集函数当组内排序后,返回组内的中位数。计算过程中忽略空值NULL。MPP/LPQ情况下,需要保证组内数据是全的,否则结果错误。MEDIAN()不支持和DISTINCT和<KEEP子句>一起使用。<参数>:参数类型可以是数值类型(INT/DEC)、时间类型(DATETIME/DATE)、时间间隔类型(INTERVAL YEAR TO MONTH)。<参数>暂不支持带时区的时间类型。

MEDIAN的用法:

MEDIAN(<参数>)
  1. **WM_CONCAT(expr)**集函数 首先根据SQL语句中的GROUP BY分组(与LISTAGG/LISTAGG2集函数不同的是,WM_CONCAT集函数必须指定分组),然后将返回的组内指定参数用“,”拼接起来。expr为<常量>、<列名>或<值表达式>,返回类型为VARCHAR。WM_CONCAT也可以写成WMSYS.WM_CONCAT。

WM_CONCAT的用法:

WM_CONCAT(expr[ || expr])

4.1.3.3 举例说明

下面按集函数的功能分别举例说明。

1.求最大值集函数MAX和求最小值集函数MIN

查询折扣小于7的图书中现价最低的价格。

SELECT MIN(NOWPRICE) FROM PRODUCTION.PRODUCT WHERE DISCOUNT < 7;

查询结果为:6.1000

需要说明的是:SELECT后使用集函数MAX和MIN得到的是一个最大值和最小值,因而SELECT后不能再有列名出现,如果有只能出现在集函数中。如:

SELECT NAME,MIN(NOWPRICE) FROM PRODUCTION.PRODUCT;

DM系统会报错,因为NAME是一个行集合,而最低价格是唯一值。

至于MAX的使用格式与MIN是完全一样的,读者可以自己举一反三。

2.求平均值集函数AVG和总和集函数SUM

求折扣小于7的图书的平均现价。

SELECT AVG(NOWPRICE) FROM PRODUCTION.PRODUCT WHERE DISCOUNT < 7;

查询结果为:23.15

求折扣大于8的图书的总价格。

SELECT SUM(NOWPRICE) FROM PRODUCTION.PRODUCT WHERE DISCOUNT >8;

查询结果为:25.5

3.求总个数集函数COUNT

查询已登记供应商的个数。

SELECT COUNT(*) FROM PURCHASING.VENDOR;

查询结果为:12

由此例可看出,COUNT(*)的结果是VENDOR表中的总行数,由于主关键字不允许有相同值,因此,它不需要使用保留字DISTINCT。

查询目前销售的图书的出版商的个数。

SELECT COUNT(DISTINCT PUBLISHER) FROM PRODUCTION.PRODUCT;

查询结果为:9

由于一个出版商可出版多种图书,因而此例中一定要用DISTINCT才能得到正确结果。

4.求方差集函数VARIANCE、标准差函数STDDEV和样本标准差函数STDDEV_SAMP

求图书的现价方差。

SELECT VARIANCE(NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:1.3664888888888888888888888888888888889E2

求图书的现价标准差。

SELECT STDDEV(NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:11.689692

求图书的现价样本标准差。

SELECT STDDEV_SAMP(NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:11.689692

5.求总体协方差集函数COVAR_POP、样本协方差函数COVAR_SAMP和相关系数CORR

求产品原始价格ORIGINALPRICE和当头销售价格NOWPRICE的总体协方差。

SELECT COVAR_POP(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:166.226

求产品原始价格ORIGINALPRICE和当头销售价格NOWPRICE的样本协方差。

SELECT COVAR_SAMP(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:1.8469555555555555555555555555555555556E2

求产品原始价格ORIGINALPRICE和当头销售价格NOWPRICE的相关系数。

SELECT CORR(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT;

查询结果为:9.6276530968E-001

6.首行函数FIRST_VALUE

返回查询项的首行记录。

SELECT FIRST_VALUE(NAME) FROM PRODUCTION.PRODUCT;

查询结果为:红楼梦

7. 求区间范围内的最大值函数 area_max

求图书的现价在20~30之间的最大值。

SELECT area_max(NOWPRICE,20,30) FROM PRODUCTION.PRODUCT;

查询结果为:25.5000

8. 求 FIRST/LAST集函数

求每个用户最早定的商品中花费最多和最少的金额。

select CUSTOMERID, max(TOTAL) keep (dense_rank first order by ORDERDATE) max_val, min(TOTAL) keep (dense_rank first order by ORDERDATE) min_val from SALES.SALESORDER_HEADER group by CUSTOMERID;

查询结果为:

CUSTOMERID    MAX_VAL    MIN_VAL
----------------------------
1             36.9000    36.9000

9. 求 LISTAGG/LISTAGG2集函数

求出版的所有图书。

SELECT LISTAGG(NAME, ', ')  WITHIN GROUP (ORDER BY NAME) LISTAGG FROM PRODUCTION.PRODUCT;
或
SELECT LISTAGG2(NAME, ', ')  WITHIN GROUP (ORDER BY NAME) LISTAGG FROM PRODUCTION.PRODUCT;

查询结果为:长征, 工作中无小事, 红楼梦, 老人与海,鲁迅文集(小说、散文、杂文)全两册, 射雕英雄传(全四册), 数据结构(C语言版)(附光盘),水浒传, 突破英文基础词汇, 噼里啪啦丛书(全7册)

10. 求MEDIAN集函数

求按照type分组之后,各组内nowprice的中位数。

SELECT MEDIAN(nowprice)FROM PRODUCTION.PRODUCT group by(type);

查询结果为:

17.6
18.45

11. 求WM_CONCAT集函数

求每个出版社出版的所有图书。先根据出版社进行分组,然后将每个出版社出版的图书名用“,”拼接起来。

SELECT PUBLISHER, WM_CONCAT(NAME) FROM PRODUCTION.PRODUCT GROUP BY PUBLISHER;

查询结果为:

行号    PUBLISHER      WM_CONCAT(NAME)
---------- -------------------- ----------------------------------
1     中华书局       红楼梦,水浒传

2     上海出版社      老人与海

3     广州出版社      射雕英雄传(全四册)

4     鲁迅文集(小说、散文、杂文)全两册

5     人民文学出版社    长征

6     清华大学出版社    数据结构(C语言版)(附光盘)

7     机械工业出版社    工作中无小事

8     外语教学与研究出版社 突破英文基础词汇

9     21世纪出版社    噼里啪啦丛书(全7册)

4.1.4 分析函数

分析函数主要用于计算基于组的某种聚合值。

DM分析函数为用户分析数据提供了一种更加简单高效的处理方式。如果不使用分析函数,则必须使用连接查询、子查询或者视图,甚至复杂的存储过程实现。引入分析函数后,只需要简单的SQL语句,并且执行效率方面也有大幅提高。

与集函数的主要区别是,分析函数对于每组返回多行数据。多行形成的组称为窗口,窗口决定了执行当前行的计算范围,窗口的大小可以由组中定义的行数或者范围值滑动。

4.1.4.1 函数分类

分析函数可分为11类:

  1. COUNT(*);
  2. 完全分析函数 AVG|MAX|MIN|COUNT|SUM([ALL]<值表达式>),这5个分析函数的参数和作为集函数时的参数一致;
  3. 方差函数VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV;
  4. 协方差函数 COVAR_POP、COVAR_SAMP、CORR;
  5. 首尾函数FIRST_VALUE、LAST_VALUE;
  6. 相邻函数LAG和LEAD;
  7. 分组函数NTILE;
  8. 排序函数RANK、DENSE_RANK、ROW_NUMBER;
  9. 百分比函数PERCENT_RANK、CUME_DIST、RATIO_TO_REPORT、PERCENTILE_CONT、NTH_VALUE;
  10. 字符串函数LISTAGG、WM_CONCAT;
  11. 指定行函数NTH_VALUE;
  12. 中位数函数MEDIAN。

4.1.4.2 使用说明

  1. 分析函数只能出现在选择项或者ORDER BY子句中;
  2. 分析函数有DISTINCT的时候,不允许ORDER BY一起使用;
  3. 分析函数参数、PARTITION BY项和ORDER BY项中不允许使用分析函数,即不允许嵌套;
  4. <PARTITION BY项>为分区子句,表示对结果集中的数据按指定列进行分区。不同的区互不相干。当PARTITION BY项包含常量表达式时,表示以整个结果集分区;当省略PARTITION BY项时,将所有行视为一个分组;
  5. <ORDER BY项>为排序子句,对经<PARTITION BY项>分区后的各分区中的数据进行排序。ORDER BY项中包含常量表达式时,表示以该常量排序,即保持原来结果集顺序;
  6. <窗口子句>为分析函数指定的窗口。窗口就是分析函数在每个分区中的计算范围;<窗口子句>必须和<ORDER BY子句>同时使用;
  7. AVG、COUNT、MAX、MIN、SUM这5类分析函数的参数和返回的结果集的数据类型与对应的集函数保持一致,详细参见集函数部分;
  8. 只有MIN、MAX、COUNT、SUM、AVG、STDDEV、VARIANCE的参数支持DISTINCT,其他分析函数的参数不允许为DISTINCT;
  9. FIRST_VALUE分析函数返回组中数据窗口的第一个值,LAST_VALUE表示返回组中数据窗口ORDER BY项相同的最后一个值;
  10. FIRST_VALUE/LAST_VALUE/LAG/LEAD/NTH_VALUE函数支持RESPECT|IGNORE NULLS子句,该子句用来指定计算中是否跳过NULL值;
  11. NTH_VALUE函数支持FROM FIRST/LAST子句,该子句用来指定计算中是从第一行向后还是最后一行向前。

4.1.4.3 具体用法

分析函数的使用,按以下几种情况。

4.1.4.3.1 一般分析函数

分析函数的分析子句语法如下:

<分析函数>::=<函数名>(<参数>) OVER (<分析子句>)
<分析子句>::= [<PARTITION BY项>] [<ORDER BY项> [<窗口子句>]]
<PARTITION BY项>::= PARTITION BY <<常量表达式>| <列名>>
<ORDER BY项>::= ORDER BY <<常量表达式>| <列名>>
<窗口子句>::=<ROWS | RANGE> < <范围子句1>|<范围子句2> >
<范围子句1>::=
	BETWEEN {<UNBOUNDED PRECEDING>|<CURRENT ROW>|<value_expr <PRECEDING|FOLLOWING> >}
	AND 	{<UNBOUNDED FOLLOWING>|<CURRENT ROW>|<value_expr <PRECEDING|FOLLOWING> >}
<范围子句2>::=<UNBOUNDED PRECEDING>|<CURRENT ROW>| <value_expr PRECEDING>
<函数名>::=见下表

窗口子句: 不是所有的分析函数都可以使用窗口。其对应关系如下表所示:

表4.1.10 分析函数窗口列表
序号 函数名 是否为集函数 是否允许使用窗口子句
1 AVG Y Y
2 CORR Y Y
3 COUNT Y Y
4 COVAR_POP Y Y
5 COVAR_SAMP Y Y
6 CUME_DIST Y N
7 DENSE_RANK Y N
8 FIRST_VALUE Y Y
9 LAG N N
10 LAST_VALUE Y Y
11 LEAD N N
12 LISTAGG Y N
13 NTH_VALUE N Y
14 MAX Y Y
15 MIN Y Y
16 NTILE N N
17 PERCENT_RANK Y N
18 PERCENTILE_CONT N N
19 PERCENTILE_DISC N N
20 RANK Y N
21 RATIO_TO_REPORT N N
22 ROW_NUMBER N N
23 STDDEV Y Y
24 STDDEV_POP Y Y
25 STDDEV_SAMP Y Y
26 SUM Y Y
27 VAR_POP Y Y
28 VAR_SAMP Y Y
29 VARIANCE Y Y
30 WM_CONCAT Y Y
31 MEDIAN Y N

<窗口子句>通过指定滑动方式和<范围子句>两项来共同确定分析函数的计算窗口。每个分区的第一行开始往下滑动。

■ 滑动方式有两种:ROW和RANGE。

● ROWS

ROWS用来指定窗口的物理行数。ROWS根据ORDER BY 子句排序后,取的前value_expr 行或后value_expr 行的数据进行计算。与当前行的值无关,只与排序后的行号有关。

对于ROWS来说,value_expr必须是一个可以计算的正数数值型的表达式或常量。

● RANGE

RANGE用来指定窗口的逻辑偏移,即指定行值的取值范围。只要行值处于RANGE指定的取值范围内,该行就包含在窗口中。

  1. 逻辑偏移值(value_expr)必须为常量、表达式或者非负的NUMERIC类型数值;
  2. <ORDER BY 子句>中如果使用表达式,那么只能声明一个表达式;
  3. value_expr类型和ORDER BY expr类型应为相同的或可隐式转换计算的。

■ <范围子句>用来指定具体的窗口范围。ROW和RANGE中用法不同,下面分别介绍。

窗口边界关键字用法

图 4.1 窗口边界关键字用法

● <范围子句>中的边界关键字介绍如下:

  1. UNBOUNDED PRECEDING窗口的边界是分区中的第一行或第一个值;
  2. UNBOUNDED FOLLOWING窗口的边界是分区中的最后一行或最后一个值;
  3. CURRENT ROW窗口的边界是当前行或者当前行的值;
  4. value_expr PRECEDING窗口的边界是当前行向前滑动value_expr的行或当前值-value_expr的值;
  5. value_expr FOLLOWING窗口的边界是当前行向后滑动value_expr的行或当前值+value_expr的值。

● <范围子句>中的边界关键字在RANGE的用法介绍

  1. UNBOUNDED PRECEDING窗口的边界是分区中的第一个值;
  2. UNBOUNDED FOLLOWING窗口的边界是分区中的最后一个值;
  3. CURRENT ROW窗口的边界是当前值;
  4. value_expr PRECEDING窗口的边界是当前值-value_expr的值;
  5. value_expr FOLLOWING窗口的边界是当前值+value_expr的值。

● <范围子句>中的边界关键字的使用须知:

  1. BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,表示该组的第一行到当前行,或表示第一个值到当前值;
  2. BETWEEN CURRENT ROW AND CURRENT ROW,表示当前行到当前行,或表示当前值到当前值;
  3. BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,表示该组的第一行到最后一行,或表示第一个值到最后一个值;
  4. UNBOUNDED PRECEDING,和1)等价;
  5. CURRENT ROW,和2)等价;
  6. value_expr PRECEDING,等价于BETWEEN value_expr PRECEDING AND CURRENT ROW;
  7. 如果省略<窗口子句>,缺省为BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
  8. BETWEEN ... AND...:窗口的范围,如果只定义一个分支,其另一个分支为当前行CURRENT ROW;
  9. CURRENT ROW用法中有两种特殊情况:一是当窗口以CURRENT ROW为开始位置时,窗口的结束点不能是value_expr PRECEDING。二是当窗口以CURRENT ROW为结束位置时,窗口的起始点不能是value_expr FOLLOWING。
  10. value_expr PRECEDING或value_expr FOLLOWING用法中有三种特殊情况:一是对于ROWS或RANGE,如果value_expr FOLLOWING是起始位置,则结束位置也必须是value_expr FOLLOWING;如果value_expr PRECEDING是结束位置,则起始位置必须是value_expr PRECEDING。二是对于ROWS,如果窗口函数的起始位置到结束位置没有记录,则分析函数的值返回NULL。三是对于RANGE,在<ORDER BY 子句>中,只能指定一个表达式,即排序列不能多于一个,对于ROWS,则无此限制。

图例

分析函数语法如下:

分析函数语法如下

分析子句

分析子句

partition by项

partition by项

order by项

order by项

窗口子句

窗口子句

4.1.4.3.2 FIRST/LAST函数

FIRST和LAST作为分析函数时,计算方法和对应的集函数类似,只是一组返回多行。

语法格式

<函数名><参数> <KEEP子句> OVER ([<PARTITION BY项>])
<KEEP子句>::= KEEP (DENSE_RANK FIRST|LAST <ORDER BY项>)
<函数名> ::= AVG | MAX | MIN | COUNT | SUM

<KEEP 子句>:首先根据<ORDER BY项>进行排序,然后根据FIRST/LAST计算出第一名(最小值)/最后一名(最大值)的函数值,排名按照奥林匹克排名法。

图例

FIRST和LAST分析函数语法如下:

FIRST和LAST分析函数语法如下

4.1.4.3.3 LAG和LEAD函数

LAG分析函数表示返回组中和当前行向前相对偏移offset行的参数的值,LEAD方向相反,表示向后相对偏移。如果超出组的总行数范围,则返回DEFAULT值。

语法格式

<LAG|LEAD> <参数选项1|参数选项2> OVER ([<PARTITION BY项>] <ORDER BY项>)
<参数选项1> ::= (<参数>[,<offset>[,<default>]])[<RESPECT|IGNORE> NULLS]
<参数选项2> ::= (<参数>[<RESPECT|IGNORE> NULLS] [,<offset>[,<default>]])

参数

<offset>:为常量或表达式,类型为整型,默认为1;

<default>:不在offset偏移范围内的默认值,为常量或表达式,和LAG和LEAD的参数数据类型一致;

<RESPECT|IGNORE> NULLS: 计算中是否跳过NULL值,RESPECT NULLS为不跳过,IGNORE NULLS为跳过,默认值为RESPECT NULLS。

图例

LAG和LEAD函数

LAG和LEAD函数

arguments1

arguments1

arguments2

arguments2

4.1.4.3.4 FIRST_VALUE和LAST_VALUE函数

FIRST_VALUE返回排序数据集合的第一行,LAST_VALUE返回其最后一行。

语法格式

<FIRST_VALUE|LAST_VALUE> <参数选项1|参数选项2> OVER (<分析子句>)
<参数选项1> ::= (<参数>) [<RESPECT | IGNORE> NULLS ]
<参数选项2> ::= (<参数> [<RESPECT | IGNORE> NULLS ])

图例

FIRST_VALUE和LAST_VALUE函数

FIRST_VALUE和LAST_VALUE函数

arguments1

arguments1

arguments2

arguments2

4.1.4.3.5 PERCENTILE_CONT和PERCENTILE_DISC函数

连续百分比PERCENTILE_CONT和分布百分比PERCENTILE_DISC分析函数。

语法格式

<PERCENTILE_CONT|PERCENTILE_DISC> (<参数>) WITHIN GROUP(<ORDER BY项>) OVER ([<PARTITION BY项>])

图例

PERCENTILE_CONT和PERCENTILE_DISC函数

PERCENTILE_CONT和PERCENTILE_DISC函数

4.1.4.3.6 LISTAGG函数

字符串分析函数LISTAGG按照指定的PARTITION BY项进行分组,组内按照ORDER BY项排序(没有指定排序则按数据组织顺序),将组内的参数通过分隔符拼接起来,返回的结果集行数为组数。

语法格式

LISTAGG (<参数> [,<分隔符>]) [WITHIN GROUP(<ORDER BY项>)] OVER ([<PARTITION BY项>])

图例

LISTAGG函数

LISTAGG函数

4.1.4.3.7 NTH_VALUE函数

指定行分析函数NTH_VALUE按照指定的PARTITION BY项进行分组,组内按照ORDER BY项排序,返回组内结果集的指定行的数据。

语法格式

NTH_VALUE (<参数1> ,<参数2>) [FROM <FIRST | LAST>] [<RESPECT | IGNORE> NULLS] OVER ([<PARTITION BY项>] [<ORDER BY项> [<窗口子句>]])
<PARTITION BY项>、<ORDER BY项>、<窗口子句>请参考4.1.4.3.1 一般分析函数

参数

  1. FROM <FIRST | LAST>:指定组内数据方向,FROM FISRT指定从第一行往后,FROM LAST指定从最后一行往前,默认值为FROM FIRST;
  2. <RESPECT|IGNORE> NULLS: 计算中是否跳过NULL值,RESPECT NULLS为不跳过,IGNORE NULLS为跳过,默认值为RESPECT NULLS。

图例

NTH_VALUE函数

NTH_VALUE函数

4.1.4.3.8 WM_CONCAT函数

字符串分析函数WM_CONCAT按照指定的PARTITION BY项进行分组,然后将返回的组内指定参数用“,”拼接起来,返回的结果集行数为组数。不支持WITH IN子句。

语法格式

WM_CONCAT (<参数> [|| <参数>]) OVER ([<PARTITION BY项>])

图例

WM_CONCAT函数

WM_CONCAT函数

4.1.4.3.9 MEDIAN函数

中位数计算函数MEDIAN按照指定的PARTITION BY项进行分组,不支持WITH IN子句,计算组内参数的中位数,返回的结果集行数为组数。

语法格式

MEDIAN (<参数>) OVER ([<PARTITION BY项>])

图例

MEDIAN函数

MEDIAN函数

4.1.4.4 举例说明

下面按分析函数的功能分别举例说明。

  1. 最大值MAX和最小值MIN

查询折扣大于7的图书作者以及最大折扣。

SELECT  AUTHOR,  MAX(DISCOUNT) OVER (PARTITION BY AUTHOR)  AS MAX  FROM   PRODUCTION.PRODUCT  WHERE  DISCOUNT > 7;

查询结果如表4.1.11所示:

表4.1.11
AUTHOR MAX
曹雪芹,高鹗 8.0
施耐庵,罗贯中 7.5
严蔚敏,吴伟民 8.5

需要说明的是:如果使用的是集函数MAX,那么得到的是所有图书中折扣的最大值,并不能查询出作者,使用了分析函数,就可以对作者进行分区,得到每个作者所写的图书中折扣最大的值。MIN的含义和MAX类似。

  1. 平均值AVG和总和SUM

求折扣小于7的图书作者和平均价格。

SELECT  AUTHOR,  AVG(NOWPRICE) OVER (PARTITION BY AUTHOR) as AVG FROM	PRODUCTION.PRODUCT WHERE   DISCOUNT < 7;

查询结果如表4.1.12所示:

表4.1.12
AUTHOR AVG
(日)佐佐木洋子 42
陈满麒 11.4
海明威 6.1
金庸 21.7
鲁迅 20
王树增 37.7

求折扣大于8的图书作者和书的总价格。

SELECT   AUTHOR,  SUM(NOWPRICE) OVER (PARTITION BY AUTHOR)  as SUM FROM	PRODUCTION.PRODUCT WHERE   DISCOUNT >8;

查询结果如表4.1.13所示:

表4.1.13
AUTHOR SUM
严蔚敏,吴伟民 25.5
  1. 样本个数COUNT

查询信用级别为“很好”的已登记供应商的名称和个数。

SELECT  NAME,  COUNT(*) OVER (PARTITION  BY  CREDIT) AS CNT FROM   PURCHASING.VENDOR WHERE  CREDIT = 2;

查询结果如表4.1.14所示:

表4.1.14
NAME CNT
长江文艺出版社 2
上海画报出版社 2

由此例可看出,COUNT(*)的结果是VENDOR表中的按CREDIT分组后的总行数。

4.分析函数总体协方差COVAR_POP

求产品原始价格ORIGINALPRICE和当前销售价格NOWPRICE的总体协方差。

SELECT PUBLISHER, COVAR_POP(ORIGINALPRICE, NOWPRICE) OVER(PARTITION BY PUBLISHER) AS COVAR_POP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.15所示:

表4.1.15
PUBLISHER COVAR_POP
0
21世纪出版社 0
广州出版社 0
机械工业出版社 0
清华大学出版社 0
人民文学出版社 0
上海出版社 0
外语教学与研究出版社 0
中华书局 0
中华书局 0
  1. 分析函数样本协方差COVAR_SAMP

求产品原始价格ORIGINALPRICE和当前销售价格NOWPRICE的样本协方差。

SELECT PUBLISHER, COVAR_SAMP(ORIGINALPRICE, NOWPRICE) OVER(PARTITION BY PUBLISHER) AS COVAR_SAMP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.16所示:

表4.1.16
PUBLISHER COVAR_SAMP
NULL
21世纪出版社 NULL
广州出版社 NULL
机械工业出版社 NULL
清华大学出版社 NULL
人民文学出版社 NULL
上海出版社 NULL
外语教学与研究出版社 NULL
中华书局 0
中华书局 0

6.系数CORR

求产品原始价格ORIGINALPRICE和当前销售价格NOWPRICE的相关系数。

SELECT PUBLISHER, CORR(ORIGINALPRICE, NOWPRICE) OVER(PARTITION BY PUBLISHER) AS CORR FROM PRODUCTION.PRODUCT;

查询结果如表4.1.17所示:

表4.1.17
PUBLISHER CORR
NULL
21世纪出版社 NULL
广州出版社 NULL
机械工业出版社 NULL
清华大学出版社 NULL
人民文学出版社 NULL
上海出版社 NULL
外语教学与研究出版社 NULL
中华书局 NULL
中华书局 NULL
  1. 排名RANK、DENSE_RANK和ROW_NUMBER

求按销售额排名的销售代表对应的雇员号和排名。

SELECT EMPLOYEEID, RANK() OVER (ORDER BY SALESLASTYEAR) AS RANK FROM SALES.SALESPERSON;

查询结果如表4.1.18所示:

表4.1.18
EMPLOYEEID RANK
4 1
5 2

RANK()排名函数按照指定ORDER BY项进行排名,如果值相同,则排名相同,例如销售额相同的排名相同,该函数使用非密集排名,例如两个第1名后,下一个就是第3名;与之对应的是DENSE_RANK(),表示密集排名,例如两个第1名之后,下一个就是第2名。ROW_NUMBER()表示按照顺序编号,不区分相同值,即从1开始编号。

  1. FIRST和LAST

求每个用户最早定的商品中花费最多和最少的金额以及用户当前的花费金额。

SELECT CUSTOMERID, TOTAL,
MAX(TOTAL) KEEP (DENSE_RANK FIRST ORDER BY ORDERDATE) OVER (PARTITION BY CUSTOMERID) MAX_VAL,
MIN(TOTAL) KEEP (DENSE_RANK FIRST ORDER BY ORDERDATE) OVER (PARTITION BY CUSTOMERID) MIN_VAL
FROM SALES.SALESORDER_HEADER;

查询结果如表4.1.19所示:

表4.1.19
CUSTOMERID TOTAL MAX_VAL MIN_VAL
1 36.9000 36.9000 36.9000
1 36.9000 36.9000 36.9000

FIRST和LAST分析函数计算方法和对应的集函数类似,作为分析函数时一组返回多行,而集函数只返回一行。

  1. FIRST_VALUE和LAST_VALUE分析函数

求花费最多和最少金额的用户和花费金额。

SELECT NAME, TOTAL,
FIRST_VALUE(NAME) OVER (ORDER BY TOTAL) FIRST_PERSON,
LAST_VALUE(NAME) OVER (ORDER BY TOTAL) LAST_PERSON
FROM SALES.SALESORDER_HEADER S,SALES.CUSTOMER C,PERSON.PERSON P
WHERE S.CUSTOMERID = C.CUSTOMERID AND C.PERSONID = P.PERSONID;

查询结果如表4.1.20所示:

表4.1.20
NAME TOTAL FIRST_PERSON LAST_PERSON
刘青 36.9000 刘青 刘青
刘青 36.9000 刘青 刘青

FIRST_VALUE返回一组中的第一行数据,LAST_VALUE相反,返回组中的最后一行数据。根据ORDER BY项就可以返回需要的列的值。

  1. LAG和LEAD

求当前订单的前一个和下一个订单的销售总额。

SELECT ORDERDATE,
LAG(TOTAL, 1, 0) OVER (ORDER BY ORDERDATE) PRV_TOTAL,
LEAD(TOTAL, 1, 0) OVER (ORDER BY ORDERDATE) NEXT_TOTAL
FROM  SALES.SALESORDER_HEADER;

查询结果如表4.1.21所示:

表4.1.21
ORDERDATE PRV_TOTAL NEXT_TOTAL
2007-05-06 0 36.9000
2007-05-07 36.9000 0

LAG返回当前组的前一个订单日期的TOTAL值,如果超出该组,则返回DEFAULT值0。

  1. 窗口的使用

按照作者分类,求到目前为止图书价格最贵的作者和价格。

SELECT AUTHOR,
MAX(NOWPRICE) OVER(PARTITION BY AUTHOR ORDER BY NOWPRICE ROWS
UNBOUNDED PRECEDING) AS MAX_PRICE
FROM PRODUCTION.PRODUCT;

查询结果如表4.1.22所示:

表4.1.22
AUTHOR MAX_PRICE
(日)佐佐木洋子 42.0000
曹雪芹,高鹗 15.2000
陈满麒 11.4000
海明威 6.1000
金庸 21.7000
刘毅 11.1000
鲁迅 20.0000
施耐庵,罗贯中 14.3000
王树增 37.7000
严蔚敏,吴伟民 25.5000

分析函数中的窗口限定了计算的范围,ROWS UNBOUNDED PRECEDING表示该组的第一行开始到当前行,等价于ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW。

  1. 标准差STDDEV

求每个出版社图书现价的标准差。

SELECT PUBLISHER, STDDEV(NOWPRICE) OVER(PARTITION BY PUBLISHER) AS STDDEV FROM PRODUCTION.PRODUCT;

查询结果如表4.1.23所示:

表4.1.23
PUBLISHER STDDEV
0
21世纪出版社 0
广州出版社 0
机械工业出版社 0
清华大学出版社 0
人民文学出版社 0
上海出版社 0
外语教学与研究出版社 0
中华书局 0.6363961030678927
中华书局 0.6363961030678927
  1. 样本标准差STDDEV_SAMP

求每个出版社图书现价的样本标准差。

SELECT PUBLISHER, STDDEV_SAMP(NOWPRICE) OVER(PARTITION BY PUBLISHER) AS STDDEV_SAMP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.24所示:

表4.1.24
PUBLISHER STDDEV_SAMP
NULL
21世纪出版社 NULL
广州出版社 NULL
机械工业出版社 NULL
清华大学出版社 NULL
人民文学出版社 NULL
上海出版社 NULL
外语教学与研究出版社 NULL
中华书局 0.6363961030678927
中华书局 0.6363961030678927
  1. 总体标准差STDDEV_POP

求每个出版社图书现价的总体标准差。

SELECT PUBLISHER, STDDEV_POP (NOWPRICE) OVER(PARTITION BY PUBLISHER) AS STDDEV_POP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.25所示:

表4.1.25
PUBLISHER STDDEV_POP
0
21世纪出版社 0
广州出版社 0
机械工业出版社 0
清华大学出版社 0
人民文学出版社 0
上海出版社 0
外语教学与研究出版社 0
中华书局 0.45
中华书局 0.45
  1. 样本方差VAR_SAMP

求每个出版社图书现价的样本方差。

SELECT PUBLISHER, VAR_SAMP(NOWPRICE) OVER(PARTITION BY PUBLISHER) AS VAR_SAMP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.26所示:

表4.1.26
PUBLISHER VAR_SAMP
NULL
21世纪出版社 NULL
广州出版社 NULL
机械工业出版社 NULL
清华大学出版社 NULL
人民文学出版社 NULL
上海出版社 NULL
外语教学与研究出版社 NULL
中华书局 0.405
中华书局 0.405
  1. 总体方差VAR_POP

求每个出版社图书现价的总体方差。

SELECT PUBLISHER , VAR_POP(NOWPRICE) OVER(PARTITION BY PUBLISHER) AS VAR_POP FROM PRODUCTION.PRODUCT;

查询结果如表4.1.27所示:

表4.1.27
PUBLISHER VAR_POP
0
21世纪出版社 0
广州出版社 0
机械工业出版社 0
清华大学出版社 0
人民文学出版社 0
上海出版社 0
外语教学与研究出版社 0
中华书局 0.2025
中华书局 0.2025

17.方差VARIANCE

求每个出版社图书现价的方差。

SELECT PUBLISHER, VARIANCE (NOWPRICE) OVER(PARTITION BY PUBLISHER) AS VARIANCE FROM PRODUCTION.PRODUCT;

查询结果如表4.1.28所示:

表4.1.28
PUBLISHER VARIANCE
0
21世纪出版社 0
广州出版社 0
机械工业出版社 0
清华大学出版社 0
人民文学出版社 0
上海出版社 0
外语教学与研究出版社 0
中华书局 0.405
中华书局 0.405

18.分组NTILE

根据图书的现价将图书分成三个组。

SELECT NAME, NTILE (3) OVER(ORDER BY NOWPRICE) AS NTILE FROM PRODUCTION.PRODUCT;

查询结果如表4.1.29所示:

表4.1.29
NAME NTILE
老人与海 1
突破英文基础词汇 1
工作中无小事 1
水浒传 1
红楼梦 2
鲁迅文集(小说、散文、杂文)全两册 2
射雕英雄传(全四册) 2
数据结构(C语言版)(附光盘) 3
长征 3
噼里啪啦丛书(全7册) 3

19.排列百分比PERCENT_RANK

求图书的现价排列百分比。

SELECT NAME, PERCENT_RANK() OVER(ORDER BY NOWPRICE) AS NTILE FROM PRODUCTION.PRODUCT;

查询结果如表4.1.30所示:

表4.1.30
NAME NTILE
老人与海 0.0000000000E+000
突破英文基础词汇 1.1111111111E-001
工作中无小事 2.2222222222E-001
水浒传 3.3333333333E-001
红楼梦 4.4444444444E-001
鲁迅文集(小说、散文、杂文)全两册 5.5555555556E-001
射雕英雄传(全四册) 6.6666666667E-001
数据结构(C语言版)(附光盘) 7.7777777778E-001
长征 8.8888888889E-001
噼里啪啦丛书(全7册) 1.0000000000E+000

20.连续百分比对应的值PERCENTILE_CONT

求连续百分比占0.5对应的图书现价值。

SELECT NAME, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY NOWPRICE) OVER() AS PERCENTILE_CONT FROM PRODUCTION.PRODUCT;

查询结果如表4.1.31所示:

表4.1.31
NAME PERCENTILE_CONT
老人与海 17.600000
突破英文基础词汇 17.600000
工作中无小事 17.600000
水浒传 17.600000
红楼梦 17.600000
鲁迅文集(小说、散文、杂文)全两册 17.600000
射雕英雄传(全四册) 17.600000
数据结构(C语言版)(附光盘) 17.600000
长征 17.600000
噼里啪啦丛书(全7册) 17.600000
  1. 分布百分比对应的值PERCENTILE_DISC

求分布百分比占0.5对应的图书现价值。

SELECT NAME, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY NOWPRICE) OVER() AS PERCENTILE_DISC FROM PRODUCTION.PRODUCT;

查询结果如表4.1.32所示:

表4.1.32
NAME PERCENTILE_DISC
老人与海 15.2000
突破英文基础词汇 15.2000
工作中无小事 15.2000
水浒传 15.2000
红楼梦 15.2000
鲁迅文集(小说、散文、杂文)全两册 15.2000
射雕英雄传(全四册) 15.2000
数据结构(C语言版)(附光盘) 15.2000
长征 15.2000
噼里啪啦丛书(全7册) 15.2000

22.累计百分比CUME_DIST

求图书现价的累计百分比。

SELECT NAME, CUME_DIST() OVER(ORDER BY NOWPRICE) AS CUME_DIST FROM PRODUCTION.PRODUCT;

查询结果如表4.1.33所示:

表4.1.33
NAME CUME_DIST
老人与海 1.0000000000E-001
突破英文基础词汇 2.0000000000E-001
工作中无小事 3.0000000000E-001
水浒传 4.0000000000E-001
红楼梦 5.0000000000E-001
鲁迅文集(小说、散文、杂文)全两册 6.0000000000E-001
射雕英雄传(全四册) 7.0000000000E-001
数据结构(C语言版)(附光盘) 8.0000000000E-001
长征 9.0000000000E-001
噼里啪啦丛书(全7册) 1.0000000000E+000
  1. 某一样本值所占百分比RATIO_TO_REPORT

求出版社每种图书现价所占的百分比。

SELECT NAME, RATIO_TO_REPORT(NOWPRICE) OVER(PARTITION BY PUBLISHER) AS RATIO_TO_REPORT FROM PRODUCTION.PRODUCT;

查询结果如表4.1.34所示:

表4.1.34
NAME RATIO_TO_REPORT
老人与海 1.0000000000E+000
突破英文基础词汇 1.0000000000E+000
工作中无小事 1.0000000000E+000
水浒传 1.0000000000E+000
红楼梦 1.0000000000E+000
鲁迅文集(小说、散文、杂文)全两册 1.0000000000E+000
射雕英雄传(全四册) 1.0000000000E+000
数据结构(C语言版)(附光盘) 1.0000000000E+000
长征 4.8474576271E-001
噼里啪啦丛书(全7册) 5.1525423729E-001

24.组内指定行NTH_VALUE

例1: 求每个出版社第二贵的书的价格。

SELECT  PUBLISHER, NTH_VALUE(NOWPRICE, 2) FROM FIRST RESPECT NULLS  OVER(PARTITION BY PUBLISHER ORDER BY NOWPRICE DESC)  AS NTH_VALUE FROM  PRODUCTION.PRODUCT;

查询结果如表4.1.35所示:

表4.1.35
PUBLISHER NTH_VALUE
NULL
21世纪出版社 NULL
广州出版社 NULL
机械工业出版社 NULL
清华大学出版社 NULL
人民文学出版社 NULL
上海出版社 NULL
外语教学与研究出版社 NULL
中华书局 NULL
中华书局 14.3000

例2: 利用窗口子句求每个出版社第二贵的书的价格。

SELECT PUBLISHER, NTH_VALUE(NOWPRICE, 2) FROM FIRST RESPECT NULLS OVER(PARTITION BY PUBLISHER ORDER BY NOWPRICE DESC ROWS UNBOUNDED PRECEDING) AS NTH_VALUE FROM PRODUCTION.PRODUCT;

查询结果同例1。

25. 字符串分析函数WM_CONCAT

求每个出版社出版的图书。先根据出版社进行分组,然后将每个出版社出版的图书名用“,”拼接起来。

SELECT PUBLISHER, WM_CONCAT(NAME) OVER (PARTITION BY PUBLISHER) AS WM_CONCAT FROM PRODUCTION.PRODUCT;

查询结果如表4.1.36所示:

表4.1.36
PUBLISHER WM_CONCAT
鲁迅文集(小说、散文、杂文)全两册
21世纪出版社 噼里啪啦丛书(全7册)
上海出版社 老人与海
中华书局 红楼梦,水浒传
中华书局 红楼梦,水浒传
人民文学出版社 长征
外语教学与研究出版社 突破英文基础词汇
广州出版社 射雕英雄传(全四册)
机械工业出版社 工作中无小事
清华大学出版社 数据结构(C语言版)(附光盘)

26. 计算中位数MEDIAN

求图书作者和其所著图书价格的中位数。先根据PARTITION BY项进行分组,然后计算组内参数的中位数。

SELECT AUTHOR, MEDIAN(NOWPRICE) OVER (PARTITION BY AUTHOR) as MED FROM PRODUCTION.PRODUCT;

查询结果如表4.1.37所示:

表4.1.37
AUTHOR MED
(日)佐佐木洋子 42
曹雪芹,高鹗 15.2
陈满麒 11.4
海明威 6.1
金庸 21.7
刘毅 11.1
鲁迅 20
施耐庵,罗贯中 14.3
王树增 37.7
严蔚敏,吴伟民 25.5

4.1.5 情况表达式

<值表达式>可以为一个<列引用>、<集函数>、<标量子查询>或<情况表达式>等等。

<情况表达式>包括<情况缩写词>和<情况说明>两大类。<情况缩写词>包括函数NULLIF和COALESCE,在DM中被划分为空值判断函数。具体函数说明请见8.4节。下面详细介绍<情况说明>表达式。

<CASE情况说明>的语法和语义如下:

语法格式

<情况说明> ::= <简单情况> | <搜索情况>

<简单情况> ::= CASE
<值表达式>
{<简单WHEN 子句>}
[<ELSE 子句>]
END

<搜索情况> ::= CASE
[<搜索WHEN子句>]
[<ELSE 子句>]
END
<简单WHEN 子句> ::= WHEN <值表达式> THEN <结果>
<搜索WHEN子句> ::= WHEN <搜索条件> THEN <结果>
<结果> ::= <值表达式> | NULL

图例

情况表达式

情况表达式

功能

指明一个条件值。将搜索条件作为输入并返回一个标量值。

使用说明

1.在<情况说明>中至少有一个<结果>应该指明<值表达式>;

2.如果未指明<ELSE子句>,则隐含ELSE NULL;

3.<简单情况>中,CASE运算数的数据类型必须与<简单WHEN子句>中的<值表达式>的数据类型是可比较的,且与ELSE子句的结果也是可比较的;

4.<情况说明>的数据类型由<结果>中的所有<值表达式>的数据类型确定;

1)如果<结果>指明NULL,则它的值是空值;

2)如果<结果>指明<值表达式>,则它的值是该<值表达式>的值。

5.如果在<情况说明>中某个<搜索WHEN子句>的<搜索条件>为真,则<情况说明>的值是其<搜索条件>为真的第一个<搜索WHEN子句>的<结果>的值,并按照<情况说明>的数据类型来转换;

6.<搜索WHEN子句>中支持多列,如:

SELECT CASE WHEN (C1,C2) IN (SELECT C1,C2 FROM T2) THEN 1 ELSE 0 END FROM T1;

7.如果在<情况说明>中没有一个<搜索条件>为真,则<情况表达式>的值是其显式或隐式的<ELSE子句>的<结果>的值,并按照<情况说明>的数据类型来转换;

8.CASE表达式查询列名为“CASE……END”这部分,最大长度124字节,如果大于124字节则后面部分截断。

举例说明

查询图书信息,如果当前销售价格大于20元,返回“昂贵”,如果当前销售价格小于等于20元,大于等于10元,返回“普通”,如果当前销售价格小于10元,返回“便宜”。

SELECT NAME,
CASE
WHEN NOWPRICE > 20 THEN  '昂贵'
WHEN NOWPRICE <= 20 AND NOWPRICE >= 10 THEN '普通'
ELSE '便宜'
END AS 选择
FROM PRODUCTION.PRODUCT;

查询结果如下表所示:

表4.1.37
NAME 选择
红楼梦 普通
水浒传 普通
老人与海 便宜
射雕英雄传(全四册) 昂贵
鲁迅文集(小说、散文、杂文)全两册 普通
长征 昂贵
数据结构(C语言版)(附光盘) 昂贵
工作中无小事 普通
突破英文基础词汇 普通
噼里啪啦丛书(全7册) 昂贵

在VERDOR中如果NAME为中华书局或清华大学出版社,且CREDIT为1则返回“采购”,否则返回“考虑”。

SELECT NAME,
  CASE
	WHEN (NAME = '中华书局' OR NAME = '清华大学出版社') AND CREDIT = 1 THEN '采购'
	ELSE '考虑'
  END  AS 选择
FROM PURCHASING.VENDOR;

查询结果如下表所示:

表4.1.38
NAME 选择
上海画报出版社 考虑
长江文艺出版社 考虑
北京十月文艺出版社 考虑
人民邮电出版社 考虑
清华大学出版社 采购
中华书局 采购
广州出版社 考虑
上海出版社 考虑
21世纪出版社 考虑
外语教学与研究出版社 考虑
社械工业出版社 考虑
文学出版社 考虑

在上述表中将NAME为中华书局,CREDIT为1的元组返回。

SELECT NAME, CREDIT FROM PURCHASING.VENDOR
WHERE NAME IN (SELECT CASE
WHEN CREDIT = 1 THEN '中华书局'
ELSE 'NOT EQUAL'
END
FROM PURCHASING.VENDOR);

查询结果如下表所示:

表4.1.39
NAME CREDIT
中华书局 1

在上述表中,若CREDIT大于1则修改该值为1。

UPDATE PURCHASING.VENDOR SET CREDIT = CASE
WHEN CREDIT > 1 THEN 1
ELSE CREDIT
END;
SELECT NAME, CREDIT  FROM PURCHASING.VENDOR;

查询结果如下表所示:

表4.1.40
NAME CREDIT
上海画报出版社 1
长江文艺出版社 1
北京十月文艺出版社 1
人民邮电出版社 1
清华大学出版社 1
中华书局 1
广州出版社 1
上海出版社 1
21世纪出版社 1
外语教学与研究出版社 1
机械工业出版社 1
文学出版社 1

4.2 连接查询

如果一个查询包含多个表(>=2),则称这种方式的查询为连接查询。即<FROM子句>中使用的是<连接表>。DM的连接查询方式包括:交叉连接(cross join)、自然连接(natural join)、内连接(inner)、外连接(outer)。下面分别举例说明。

4.2.1 交叉连接

  1. 无过滤条件

对连接的两张表记录做笛卡尔集,产生最终结果输出。

SALESPERSON和EMPLOYEE通过交叉连接查询HAIRDATE和SALESLASTYEAR。

SELECT T1.HAIRDATE, T2.SALESLASTYEAR FROM RESOURCES.EMPLOYEE T1 CROSS JOIN SALES.SALESPERSON T2;

查询结果如表4.2.1所示。

表4.2.1
HAIRDATE SALESLASTYEAR
2002-5-2 10.0000
2002-5-2 20.0000
2002-5-2 10.0000
2002-5-2 20.0000
2002-5-2 10.0000
2002-5-2 20.0000
2002-5-2 10.0000
2002-5-2 20.0000
2002-5-2 10.0000
2002-5-2 20.0000
2005-5-2 10.0000
2005-5-2 20.0000
2002-5-2 10.0000
2002-5-2 20.0000
2004-5-2 10.0000
2004-5-2 20.0000
  1. 有过滤条件

对连接的两张表记录做笛卡尔集,根据WHERE条件进行过滤,产生最终结果输出。

查询性别为男性的员工的姓名与职务。

SELECT T1.NAME, T2.TITLE
FROM PERSON.PERSON T1, RESOURCES.EMPLOYEE T2
WHERE T1.PERSONID = T2.PERSONID AND T1.SEX = 'M';

查询结果如表4.2.2所示。

表4.2.2
NAME TITLE
王刚 销售经理
李勇 采购经理
黄非 采购代表
张平 系统管理员

本例中的查询数据必须来自PERSON和EMPLOYEE两个表。因此,应在FROM子句中给出这两个表的表名(为了简化采用了别名),在WHERE子句中给出连接条件(即要求两个表中PERSONID的列值相等)。当参加连接的表中出现相同列名时,为了避免混淆,可在这些列名前加表名前缀。

该例的查询结果是PERSON和EMPLOYEE在PERSONID列上做等值连接产生的。条件“T1.PERSONID=T2.PERSONID”称为连接条件或连接谓词。当连接运算符为“=”号时,称为等值连接,使用其它运算符则称非等值连接。

说明:

  1. 连接谓词中的列类型必须是可比较的,但不一定要相同,只要可以隐式转换即可;
  2. 不要求连接谓词中的列同名;
  3. 连接谓词中的比较操作符可以是>、>=、<、<=、=、< >;
  4. WHERE子句中可同时包含连接条件和其它非连接条件。

4.2.2 自然连接(NATURAL JOIN)

把两张连接表中的同名列作为连接条件,进行等值连接,我们称这样的连接为自然连接。

自然连接具有以下特点:

  1. 连接表中存在同名列;
  2. 如果有多个同名列,则会产生多个等值连接条件;
  3. 如果连接表中的同名列类型不匹配,则报错处理。

查询销售人员的入职时间和去年销售总额。

SELECT T1.HAIRDATE, T2.SALESLASTYEAR FROM RESOURCES.EMPLOYEE T1  NATURAL JOIN SALES.SALESPERSON T2;

查询结果如表4.2.3所示。

表4.2.3
HAIRDATE SALESLASTYEAR
2002-5-2 10.0000
2002-5-2 20.0000

4.2.3 JOIN … USING

这是自然连接的另一种写法,JOIN关键字指定连接的两张表,USING指明连接列。要求USING中的列存在于两张连接表中。

查询销售人员的入职时间和去年销售总额。

SELECT HAIRDATE, SALESLASTYEAR FROM RESOURCES.EMPLOYEE JOIN SALES.SALESPERSON USING(EMPLOYEEID);

查询结果如表4.2.4所示。

表4.2.4
HAIRDATE SALESLASTYEAR
2002-5-2 10.0000
2002-5-2 20.0000

4.2.4 JOIN … ON

这是一种连接查询的常用写法,说明是一个连接查询。JOIN关键字指定连接的两张表,ON子句指定连接条件表达式,其中不允许出现ROWNUM。具体采用何种连接方式,由数据库内部分析确定。

查询销售人员的入职时间和去年销售总额。

SELECT  T1.HAIRDATE,
T2.SALESLASTYEAR
FROM  RESOURCES.EMPLOYEE  T1 JOIN  SALES.SALESPERSON  T2
ON  T1.EMPLOYEEID=T2.EMPLOYEEID;

查询结果如表4.2.5所示。

表4.2.5
HAIRDATE SALESLASTYEAR
2002-5-2 10.0000
2002-5-2 20.0000

4.2.5 自连接

数据表与自身进行连接,我们称这种连接为自连接。

自连接查询至少要对一张表起别名,否则,服务器无法识别要处理的是哪张表。

对PURCHASING.VENDOR表进行自连接查询

SELECT T1.NAME, T2.NAME, T1.ACTIVEFLAG
FROM PURCHASING.VENDOR T1, PURCHASING.VENDOR T2
WHERE T1.NAME = T2.NAME;

查询结果如表4.2.6所示。

表4.2.6
NAME NAME ACTIVEFLAG
上海画报出版社 上海画报出版社 1
长江文艺出版社 长江文艺出版社 1
北京十月文艺出版社 北京十月文艺出版社 1
人民邮电出版社 人民邮电出版社 1
清华大学出版社 清华大学出版社 1
中华书局 中华书局 1
广州出版社 广州出版社 1
上海出版社 上海出版社 1
21世纪出版社 21世纪出版社 1
外语教学与研究出版社 外语教学与研究出版社 1
机械工业出版社 机械工业出版社 1
文学出版社 文学出版社 1

4.2.6 内连接(INNER JOIN)

根据连接条件,结果集仅包含满足全部连接条件的记录,我们称这样的连接为内连接。

从PRODUCT_CATEGORY、PRODUCT_SUBCATEGORY中查询图书的目录名称和子目录名称。

SELECT T1.NAME, T2.NAME
FROM  PRODUCTION.PRODUCT_CATEGORY T1  INNER JOIN
PRODUCTION.PRODUCT_SUBCATEGORY T2
ON T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID;

查询结果如表4.2.7所示。

表4.2.7
NAME NAME
小说 世界名著
小说 武侠
小说 科幻
小说 四大名著
小说 军事
小说 社会
文学 文集
文学 纪实文学
文学 文学理论
文学 中国古诗词
文学 中国现当代诗
文学 戏剧
文学 民间文学
计算机 计算机理论
计算机 计算机体系结构
计算机 操作系统
计算机 程序设计
计算机 数据库
计算机 软件工程
计算机 信息安全
计算机 多媒体
英语 英语词汇
英语 英语语法
英语 英语听力
英语 英语口语
英语 英语阅读
英语 英语写作
管理 行政管理
管理 项目管理
管理 质量管理与控制
管理 商业道德
管理 经营管理
管理 财务管理
少儿 幼儿启蒙
少儿 益智游戏
少儿 童话
少儿 卡通
少儿 励志
少儿 少儿英语

因为PRODUCT_CATEGORY中的NAME为金融的没有对应的子目录,所以结果集中没有金融类的图书信息。

4.2.7 外连接(OUTER JOIN)

外连接对结果集进行了扩展,会返回一张表的所有记录,对于另一张表无法匹配的字段用NULL填充返回。DM数据库支持三种方式的外连接:左外连接、右外连接、全外连接。

外连接中常用到的术语:左表、右表。根据表所在外连接中的位置来确定,位于左侧的表,称为左表;位于右侧的表,称为右表。例如
SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.D1,T1表为左表,T2表为右表。

返回所有记录的表根据外连接的方式而定。

  1. 左外连接:返回左表所有记录;
  2. 右外连接:返回右表所有记录;
  3. 全外连接:返回两张表所有记录。处理过程为分别对两张表进行左外连接和右外连接,然后合并结果集。

在左外连接和右外连接中,如果需要对未能匹配的缺失数据进行填充,可以使用分区外连接(PARTITION OUTER JOIN),分区外连接通常用于处理稀疏数据以得到分析报表。

下面举例说明。

从PRODUCT_CATEGORY、PRODUCT_SUBCATEGORY中查询图书的所有目录名称和子目录名称,包括没有子目录的目录。

SELECT T1.NAME, T2.NAME
FROM  PRODUCTION.PRODUCT_CATEGORY T1  LEFT OUTER JOIN
PRODUCTION.PRODUCT_SUBCATEGORY T2
ON T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID;

查询结果如表4.2.8所示。

表4.2.8
NAME NAME
小说 世界名著
小说 武侠
小说 科幻
小说 四大名著
小说 军事
小说 社会
文学 文集
文学 纪实文学
文学 文学理论
文学 中国古诗词
文学 中国现当代诗
文学 戏剧
文学 民间文学
计算机 计算机理论
计算机 计算机体系结构
计算机 操作系统
计算机 程序设计
计算机 数据库
计算机 软件工程
计算机 信息安全
计算机 多媒体
英语 英语词汇
英语 英语语法
英语 英语听力
英语 英语口语
英语 英语阅读
英语 英语写作
管理 行政管理
管理 项目管理
管理 质量管理与控制
管理 商业道德
管理 经营管理
管理 财务管理
少儿 幼儿启蒙
少儿 益智游戏
少儿 童话
少儿 卡通
少儿 励志
少儿 少儿英语
金融 NULL

从PRODUCT_CATEGORY、PRODUCT_SUBCATEGORY中查询图书的目录名称和所有子目录名称,包括没有目录的子目录。

SELECT T1.NAME, T2.NAME
FROM PRODUCTION.PRODUCT_CATEGORY T1 RIGHT OUTER JOIN
PRODUCTION.PRODUCT_SUBCATEGORY T2
ON T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID;

查询结果如表4.2.9所示。

表4.2.9
NAME NAME
小说 世界名著
小说 武侠
小说 科幻
小说 四大名著
小说 军事
小说 社会
文学 文集
文学 纪实文学
文学 文学理论
文学 中国古诗词
文学 中国现当代诗
文学 戏剧
文学 民间文学
计算机 计算机理论
计算机 计算机体系结构
计算机 操作系统
计算机 程序设计
计算机 数据库
计算机 软件工程
计算机 信息安全
计算机 多媒体
英语 英语词汇
英语 英语语法
英语 英语听力
英语 英语口语
英语 英语阅读
英语 英语写作
管理 行政管理
管理 项目管理
管理 质量管理与控制
管理 商业道德
管理 经营管理
管理 财务管理
少儿 幼儿启蒙
少儿 益智游戏
少儿 童话
少儿 卡通
少儿 励志
少儿 少儿英语
NULL 历史

从PRODUCT_CATEGORY、PRODUCT_SUBCATEGORY中查询图书的所有目录名称和所有子目录名称。

SELECT T1.NAME, T2.NAME
FROM PRODUCTION.PRODUCT_CATEGORY T1 FULL OUTER JOIN
PRODUCTION.PRODUCT_SUBCATEGORY T2
ON T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID;

查询结果如表4.2.10所示。

表4.2.10
NAME NAME
小说 世界名著
小说 武侠
小说 科幻
小说 四大名著
小说 军事
小说 社会
文学 文集
文学 纪实文学
文学 文学理论
文学 中国古诗词
文学 中国现当代诗
文学 戏剧
文学 民间文学
计算机 计算机理论
计算机 计算机体系结构
计算机 操作系统
计算机 程序设计
计算机 数据库
计算机 软件工程
计算机 信息安全
计算机 多媒体
英语 英语词汇
英语 英语语法
英语 英语听力
英语 英语口语
英语 英语阅读
英语 英语写作
管理 行政管理
管理 项目管理
管理 质量管理与控制
管理 商业道德
管理 经营管理
管理 财务管理
少儿 幼儿启蒙
少儿 益智游戏
少儿 童话
少儿 卡通
少儿 励志
少儿 少儿英语
金融 NULL
NULL 历史

外连接还有一种写法,在连接条件或WHERE条件中,在列后面增加(+)指示左外连接或者右外连接。如果表A和表B连接,连接条件或者where条件中,A的列带有(+)后缀,则认为是B LEFT JOIN A。如果用户的(+)指示引起了外连接环,则报错。下面举例说明。

从PRODUCT_CATEGORY、PRODUCT_SUBCATEGORY中查询图书的目录名称和所有子目录名称,包括没有目录的子目录。

SELECT T1.NAME, T2.NAME
FROM PRODUCTION.PRODUCT_CATEGORY T1, PRODUCTION.PRODUCT_SUBCATEGORY T2
WHERE T1.PRODUCT_CATEGORYID(+) = T2.PRODUCT_CATEGORYID;

查询结果如上表4.2.9所示。

新建产品区域销售统计表SALES.SALESREGION并插入数据。

CREATE TABLE SALES.SALESREGION(REGION CHAR(10), PRODUCTID INT, AMOUNT INT);
INSERT INTO SALES.SALESREGION VALUES('大陆', 2, 19800);
INSERT INTO SALES.SALESREGION VALUES('大陆', 4, 20090);
INSERT INTO SALES.SALESREGION VALUES('港澳台', 6, 5698);
INSERT INTO SALES.SALESREGION VALUES('外国', 9, 3756);
COMMIT;

统计每个产品在各个区域的销售量,没有销售则显示NULL,此时可使用PARTITON OUTER JOIN将稀疏数据转为稠密数据。

SELECT A.PRODUCTID, B.REGION, B.AMOUNT
FROM PRODUCTION.PRODUCT A LEFT JOIN SALES.SALESREGION B
PARTITION BY(B.REGION) ON A.PRODUCTID=B.PRODUCTID
ORDER BY A.PRODUCTID, B.REGION;

查询结果如表4.2.10所示。

表4.2.10
PRODUCTID REGION AMOUNT
1 大陆 NULL
1 港澳台 NULL
1 外国 NULL
2 大陆 19800
2 港澳台 NULL
2 外国 NULL
3 大陆 NULL
3 港澳台 NULL
3 外国 NULL
4 大陆 20090
4 港澳台 NULL
4 外国 NULL
5 大陆 NULL
5 港澳台 NULL
5 外国 NULL
6 大陆 NULL
6 港澳台 5698
6 外国 NULL
7 大陆 NULL
7 港澳台 NULL
7 外国 NULL
8 大陆 NULL
8 港澳台 NULL
8 外国 NULL
9 大陆 NULL
9 港澳台 NULL
9 外国 3756
10 大陆 NULL
10 港澳台 NULL
10 外国 NULL

4.3 子查询

在DM_SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块,如果在一个查询块中嵌套一个或多个查询块,我们称这种查询为子查询。子查询会返回一个值(标量子查询)或一个表(表子查询)。它通常采用(SELECT…)的形式嵌套在表达式中。子查询语法如下:

<子查询> ::= (<查询表达式>)

即子查询是嵌入括弧的<查询表达式>,而这个<查询表达式>通常是一个SELECT语句。它有下列限制:

  1. 在子查询中不得有ORDER BY子句;
  2. 子查询允许TEXT类型与CHAR类型值比较。比较时,取出TEXT类型字段的最多8188字节与CHAR类型字段进行比较;如果比较的两字段都是TEXT类型,则最多取300*1024字节进行比较;
  3. 子查询不能包含在集函数中;
  4. 在子查询中允许嵌套子查询。

按子查询返回结果的形式,DM子查询可分为两大类:

  1. 标量子查询:只返回一行一列;
  2. 表子查询:可返回多行多列。

4.3.1 标量子查询

标量子查询是一个普通SELECT查询,它只应该返回一行一列记录。如果返回结果多于一行则会提示单行子查询返回多行,返回结果多于一列则会提示SELECT语句列数超长。

下面是一个标量子查询的例子(请先关闭自动提交功能,否则COMMIT与ROLLBACK会失去效果):

SELECT 'VALUE IS', (SELECT ADDRESS1 FROM PERSON.ADDRESS WHERE ADDRESSID = 
1) 
FROM PERSON.ADDRESS_TYPE;
-- 子查询只有一列,结果正确

SELECT 'VALUE IS', LEFT((SELECT ADDRESS1 FROM PERSON. ADDRESS WHERE ADDRESSID = 1), 8)  FROM  PERSON.ADDRESS_TYPE;
-- 函数+标量子查询,结果正确 
  
      SELECT 'VALUE IS', (SELECT ADDRESS1, CITY FROM PERSON.ADDRESS WHERE ADDRESSID = 1) FROM  PERSON.ADDRESS_TYPE;
      -- 返回列数不为1,报错
  
      SELECT 'VALUES IS', (SELECT ADDRESS1 FROM PERSON.ADDRESS)
      FROM  PERSON.ADDRESS_TYPE;
      -- 子查询返回行值多于一个,报错

        DELETE FROM SALES.SALESORDER_DETAIL;
      SELECT 'VALUE IS', (SELECT ORDERQTY FROM SALES.SALESORDER_DETAIL) 
FROM  SALES.CUSTOMER;
      -- 子查询有0行,结果返回NULL

UPDATE PRODUCTION.PRODUCT SET PUBLISHER = 
(SELECT NAME FROM PURCHASING.VENDOR WHERE VENDORID = 2)
WHERE PRODUCTID = 5;

UPDATE PRODUCTION.PRODUCT_VENDOR SET STANDARDPRICE =
(SELECT AVG(NOWPRICE) FROM PRODUCTION.PRODUCT)
WHERE PRODUCTID = 1;
-- Update语句中允许使用标量子查询

INSERT INTO PRODUCTION.PRODUCT_CATEGORY(NAME) VALUES
(( SELECT NAME FROM PRODUCTION.PRODUCT_SUBCATEGORY 
WHERE PRODUCT_SUBCATEGORYID= 40));
-- Insert语句中允许使用标量子查询

例如,查询通常价格最小的供应商的名称和最小价格:

SELECT NAME, (SELECT MIN(STANDARDPRICE)
FROM PRODUCTION.PRODUCT_VENDOR T1
WHERE T1.VENDORID = T2.VENDORID)
FROM PURCHASING.VENDOR T2;

4.3.2 表子查询

和标量子查询不同的是,表子查询的查询结果可以是多行多列。

一般情况下,表子查询类似标量子查询,单列构成了表子查询的选择清单,但它的查询结果允许返回多行。可以从上下文中区分出表子查询:在其前面始终有一个只对表子查询的算符:<比较算符>ALL、<比较算符>ANY(或是其同义词<比较算符> SOME)、IN和EXISTS。

其中,在IN/NOT IN 表子查询的情况下,DM支持查询结果返回多列。

查询职务为销售代表的员工的编号、今年销售总额和去年销售总额。

SELECT  EMPLOYEEID, SALESTHISYEAR, SALESLASTYEAR
FROM   SALES.SALESPERSON
WHERE  EMPLOYEEID IN
( SELECT  EMPLOYEEID
FROM  RESOURCES.EMPLOYEE
WHERE  TITLE = '销售代表'
);

查询结果如表4.3.1所示。

表4.3.1
EMPLOYEEID SALESTHISYEAR SALESLASTYEAR
4 8.0000 10.0000
5 8.0000 20.0000

该查询语句的求解方式是:首先通过子查询“SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE WHERE TITLE = '销售代表'”查到职务为销售代表的EMPLOYEEID的集合,然后,在SALESPERSON表中找到与子查询结果集中的EMPLOYEEID所对应员工的SALESTHISYEAR和SALESLASTYEAR。

在带有子查询的查询语句中,通常也将子查询称内层查询或下层查询。由于子查询还可以嵌套子查询,相对于下一层的子查询,上层查询又称为父查询或外层查询。

由于DM_SQL语言所支持的嵌套查询功能可以将一系列简单查询构造成复杂的查询,从而有效地增强了DM_SQL语句的查询功能。以嵌套的方式构造语句是DM_SQL的“结构化”的特点。

需要说明的是:上例的外层查询只能用IN谓词而不能用比较算符“=”,因为子查询的结果包含多个元组,除非能确定子查询的结果只有一个元组时,才可用等号比较。上例语句也可以用连接查询的方式实现。

SELECT T1.EMPLOYEEID, T1.SALESTHISYEAR, T1.SALESLASTYEAR
FROM SALES.SALESPERSON T1 , RESOURCES.EMPLOYEE T2
WHERE T1.EMPLOYEEID = T2.EMPLOYEEID AND T2.TITLE = '销售代表';

查询对目录名为小说的图书进行评论的人员名称和评论日期。

采用子查询嵌套方式写出以下查询语句:

SELECT  DISTINCT NAME, REVIEWDATE
FROM   PRODUCTION.PRODUCT_REVIEW
WHERE  PRODUCTID  IN
( SELECT  PRODUCTID
FROM  PRODUCTION.PRODUCT
WHERE  PRODUCT_SUBCATEGORYID IN
( SELECT  PRODUCT_SUBCATEGORYID
FROM   PRODUCTION.PRODUCT_SUBCATEGORY
WHERE  PRODUCT_CATEGORYID IN
( SELECT  PRODUCT_CATEGORYID
FROM  PRODUCTION.PRODUCT_CATEGORY
WHERE  NAME = '小说'
)
)
);

查询结果如表4.3.2所示。

表4.3.2
NAME REVIEWDATE
刘青 2007-05-06
桑泽恩 2007-05-06

该语句采用了四层嵌套查询方式,首先通过最内层子查询从PRODUCT_CATEGORY中查出目录名为小说的目录编号,然后从PRODUCT_SUBCATEGORY中查出这些目录编号对应的子目录编号,接着从PRODUCT表中查出这些子目录编号对应的图书的编号,最后由最外层查询查出这些图书编号对应的评论人员和评论日期。

此例也可用四个表的连接来完成。

从上例可以看出,当查询涉及到多个基表时,嵌套子查询与连接查询相比,前者由于是逐步求解,层次清晰,易于阅读和理解,具有结构化程序设计的优点。

在许多情况下,外层子查询与内层子查询常常引用同一个表,如下例所示。

查询当前价格低于红楼梦的图书的名称、作者和当前价格。

SELECT  NAME, AUTHOR,  NOWPRICE
FROM  PRODUCTION.PRODUCT
WHERE  NOWPRICE < ( SELECT NOWPRICE FROM PRODUCTION.PRODUCT
	WHERE NAME = '红楼梦');

查询结果如表4.3.3所示。

表4.3.3
NAME AUTHOR NOWPRICE
水浒传 施耐庵,罗贯中 14.3000
老人与海 海明威 6.1000
工作中无小事 陈满麒 11.4000
突破英文基础词汇 刘毅 11.1000

此例的子查询与外层查询尽管使用了同一表名,但作用是不一样的。子查询是在该表中红楼梦的图书价格,而外查询是在PRODUCT表NOWPRICE列查找小于该值的集合,从而得到这些值所对应的名称和作者。DM_SQL语言允许为这样的表引用定义别名:

SELECT  NAME, AUTHOR,  NOWPRICE
FROM  PRODUCTION.PRODUCT  T1
WHERE  T1.NOWPRICE < ( SELECT  T2.NOWPRICE
FROM  PRODUCTION.PRODUCT  T2
	     WHERE  T2.NAME = '红楼梦');

该语句也可以采用连接方式实现:

SELECT  T1.NAME, T1.AUTHOR, T1.NOWPRICE
FROM  PRODUCTION.PRODUCT T1 ,  PRODUCTION.PRODUCT T2
WHERE  T2.NAME = '红楼梦' AND T1.NOWPRICE < T2.NOWPRICE;

查询图书的出版社和产品供应商名称相同的图书编号和名称。

SELECT  T1.PRODUCTID, T1.NAME
FROM  PRODUCTION.PRODUCT T1, PRODUCTION.PRODUCT_VENDOR T2
WHERE  T1.PRODUCTID = T2.PRODUCTID  AND T1.PUBLISHER = ANY
( SELECT  NAME  FROM  PURCHASING.VENDOR T3
  WHERE  T2.VENDORID = T3.VENDORID);

其结果如表4.3.4所示。

表4.3.4
PRODUCTID NAME
1 红楼梦
2 水浒传
3 老人与海
4 射雕英雄传(全四册)
7 数据结构(C语言版)(附光盘)
8 工作中无小事
9 突破英文基础词汇
10 噼里啪啦丛书(全7册)

此例有一点需要注意:子查询的WHERE子句涉及到PRODUCT_VENDOR.VENDORID(即T2.VENDORID),但是其FROM子句中却没有提到PRODUCT_VENDOR。在外部子查询FROM子句中命名了PRODUCT_VENDOR——这就是外部引用。当一个子查询含有一个外部引用时,它就与外部语句相关联,称这种子查询为相关子查询。

查询图书的出版社和产品供应商名称不相同的图书编号和名称。

SELECT T1.PRODUCTID, T1.NAME
FROM PRODUCTION.PRODUCT T1
WHERE T1.PUBLISHER <> ALL(SELECT NAME FROM PURCHASING.VENDOR );

其结果如表4.3.5所示。

表4.3.5
PRODUCTID NAME
5 鲁迅文集(小说、散文、杂文)全两册
6 长征

4.3.3 派生表子查询

派生表子查询是一种特殊的表子查询。所谓派生表是指FROM 子句中的查询表达式,可以以别名对其进行引用。在SELCET 语句的FROM 子句中可以包含一个或多个派生表。派生表嵌套层次不能超过60层。

说明:在派生表中,如果有重复列名,DM系统将自动修改其列名。

查询每个目录的编号、名称和对应的子目录的数量,并按数量递减排列。

SELECT  T1.PRODUCT_CATEGORYID, T1.NAME, T2.NUM
FROM  PRODUCTION.PRODUCT_CATEGORY  T1,
( SELECT PRODUCT_CATEGORYID, COUNT(PRODUCT_SUBCATEGORYID)
  FROM  PRODUCTION.PRODUCT_SUBCATEGORY
  GROUP BY PRODUCT_CATEGORYID
)  AS  T2(PRODUCT_CATEGORYID,NUM)
WHERE T1.PRODUCT_CATEGORYID = T2.PRODUCT_CATEGORYID
ORDER BY T2.NUM
DESC;

查询结果如表4.3.6所示。

表4.3.6
PRODUCT_CATEGORYID NAME NUM
3 计算机 8
2 文学 7
6 少儿 6
1 小说 6
5 管理 6
4 英语 6

4.3.4 定量比较

量化符ALL、SOME、ANY可以用于将一个<数据类型>的值和一个由表子查询返回的值的集合进行比较。

1.ALL

ALL定量比较要求的语法如下:

<标量表达式> <比较算符> ALL <表子查询>

其中:

1)<标量表达式>可以是对任意单值计算的表达式;

2)<比较算符>包括=、>、<、>=、<=或<>。

若表子查询返回0行或比较算符对表子查询返回的每一行都为TRUE,则返回TRUE。若比较算符对于表子查询返回的至少一行是FALSE,则ALL返回FALSE。

查询没有分配部门的员工的编号、姓名和身份证号码。

SELECT T1.EMPLOYEEID, T2.NAME, T1.NATIONALNO
FROM RESOURCES.EMPLOYEE T1 , PERSON.PERSON T2
WHERE T1.PERSONID = T2.PERSONID AND T1.EMPLOYEEID <> ALL
( SELECT EMPLOYEEID FROM RESOURCES.EMPLOYEE_DEPARTMENT);

查询结果如表4.3.7所示。

表4.3.7
EMPLOYEEID NAME NATIONALNO
7 王菲 420921197708051523

查询比中华书局所供应的所有图书都贵的图书的编号、名称和现在销售价格。

SELECT  PRODUCTID, NAME, NOWPRICE
FROM  PRODUCTION.PRODUCT
WHERE  NOWPRICE  > ALL
( SELECT  T1.NOWPRICE
  FROM  PRODUCTION.PRODUCT  T1 , PRODUCTION.PRODUCT_VENDOR T2
  WHERE T1.PRODUCTID = T2.PRODUCTID AND T2.VENDORID =
  ( SELECT  VENDORID  FROM  PURCHASING.VENDOR
    WHERE  NAME = '中华书局'
  )
)
AND  PRODUCTID  <>  ALL
( SELECT  T1.PRODUCTID
FROM  PRODUCTION.PRODUCT_VENDOR  T1 , PURCHASING.VENDOR  T2
WHERE  T1.VENDORID =  T2.VENDORID  AND T2.NAME = '中华书局'
);

查询结果如表4.3.8所示。

表4.3.8
PRODUCTID NAME NOWPRICE
4 射雕英雄传(全四册) 21.7000
5 鲁迅文集(小说、散文、杂文)全两册 20.0000
6 长征 37.7000
7 数据结构(C语言版)(附光盘) 25.5000
10 噼里啪啦丛书(全7册) 42.0000

2.ANY或SOME

ANY或SOME定量比较要求的语法如下:

<标量表达式> <比较算符> ANY | SOME <表子查询>

SOME和ANY是同义词。如果它们对于表子查询返回的至少一行为TRUE,则返回为TRUE。若表子查询返回0行或比较算符对表子查询返回的每一行都为FALSE,则返回FALSE。

ANY和ALL与集函数的对应关系如表4.3.9所示。

表4.3.9 ANY和ALL与集函数的对应关系
<> <= >=
ANY IN 不存在 <MAX <=MAX >MIN >=MIN
ALL 不存在 NOT IN <MIN <=MIN >MAX >=MAX

在具体使用时,读者完全可根据自己的习惯和需要选用。

4.3.5 带EXISTS谓词的子查询

带EXISTS谓词的子查询语法如下:

<EXISTS谓词> ::= [NOT] EXISTS <表子查询>

EXISTS判断是对非空集合的测试并返回TRUE或FALSE。若表子查询返回至少一行,则EXISTS返回TRUE,否则返回FALSE。若表子查询返回0行,则NOT
EXISTS返回TRUE,否则返回FALSE。

查询职务为销售代表的员工的编号和入职时间。

SELECT  T1.EMPLOYEEID , T1.STARTDATE
FROM  RESOURCES.EMPLOYEE_DEPARTMENT T1
WHERE  EXISTS
( SELECT  *  FROM  RESOURCES.EMPLOYEE T2
  WHERE  T2.EMPLOYEEID = T1.EMPLOYEEID  AND T2.TITLE = '销售代表');

查询结果如表4.3.10所示。

表4.3.10
EMPLOYEEID STARTDATE
5 2005-02-01
4 2005-02-01

此例查询需要EMPLOYEE_DEPARTMENT表和EMPLOYEE表中的数据,其执行方式为:首先在EMPLOYEE_DEPARTMENT表的第一行取EMPLOYEEID的值为2,这样对内层子查询则为:

(SELECT * FROM RESOURCES.EMPLOYEE T2
WHERE T2.EMPLOYEEID='2' AND T2.TITLE='销售代表');

在EMPLOYEE表中,不存在满足该条件的行,子查询返回值为假,说明不能取EMPLOYEE_DEPARTMENT表的第一行作为结果。系统接着取EMPLOYEE_DEPARTMENT表的第二行,又得到EMPLOYEEID的值为4,执行内层查询,此时子查询返回值为真,说明可以取该行作为结果。重复以上步骤……。只有外层子查询WHERE子句结果为真时,方可将EMPLOYEE_DEPARTMENT表中的对应行送入结果表,如此继续,直到把EMPLOYEE_DEPARTMENT表的各行处理完。

从以上分析得出,EXISTS子查询的查询结果与外表相关,即连接条件中包含内表和外表列,我们称这种类型的子查询为相关子查询;反之,子查询的连接条件不包含外表列,即查询结果不受外表影响,我们称这种类型的子查询为非相关子查询。

4.3.6 多列表子查询

为了满足应用需求,DM数据库扩展了子查询功能,目前支持多列IN/NOT IN子查询。

子查询可以是值列表或者查询块。

查询活动标志为1且信誉为2的供应商编号和名称。

SELECT VENDORID, NAME
FROM PURCHASING.VENDOR
WHERE (ACTIVEFLAG, CREDIT) IN ((1, 2));

查询结果如表4.3.11所示。

表4.3.11
VENDORID NAME
1 上海画报出版社
2 长江文艺出版社

上例中子查询的选择清单为多列,而看到子查询算符后面跟着的形如((1,2))的表达式我们称之为多列表达式链表,这个多列表达式链表以一个或多个多列数据集构成的集合构成。上述的例子中的多列表达式链表中的元素有两个。

查询作者为海明威且出版社为上海出版社或作者为王树增且出版社为人民文学出版社的图书名称和现在销售价格。

SELECT  NAME, NOWPRICE
FROM  PRODUCTION.PRODUCT
WHERE  (AUTHOR, PUBLISHER)  IN
(( '海明威', '上海出版社'), ('王树增', '人民文学出版社'));

查询结果如表4.3.12所示。

表4.3.12
NAME NOWPRICE
老人与海 6.1000
长征 37.7000

子查询为值列表时,需要注意以下三点:

  1. 值列表需要用括号;
  2. 值列表之间以逗号分割;
  3. 值列表的个数与查询列个数相同。

子查询为查询块的情况如下例所示:


查询由采购代表下的供应商是清华大学出版社的订单的创建日期、状态和应付款总额。

SELECT  ORDERDATE, STATUS, TOTAL
FROM   PURCHASING.PURCHASEORDER_HEADER
WHERE  (EMPLOYEEID, VENDORID)  IN
(SELECT  T1.EMPLOYEEID,  T2.VENDORID
	FROM  RESOURCES.EMPLOYEE  T1,  PURCHASING.VENDOR  T2
	WHERE  T1.TITLE = '采购代表' AND T2.NAME = '清华大学出版社');

查询结果如表4.3.13所示。

表4.3.13
ORDERDATE STATUS TOTAL
2006-07-21 1 6400

由例子可以看到,WHERE子句中有两个条件列,IN子查询的查询项也由两列构成。

DM对多列子查询的支持,满足了更多的应用场景。

4.4 WITH 子句

WITH子句语法如下:

<WITH 子句> ::= [<WITH FUNCTION子句>] [<WITH CTE子句>]

4.4.1 WITH FUNCTION子句

WITH FUNCTION子句用于在SQL语句中临时声明并定义存储函数,这些存储函数可以在其作用域内被引用。相比模式对象中的存储函数,通过WITH FUNCTION定义的存储函数在对象名解析时拥有更高的优先级。

和公用表表达式CTE类似,WITH FUNCTION定义的存储函数对象也不会存储到系统表中,且只在当前SQL语句内有效。

WITH FUNCTION子句适用于偶尔需要使用存储过程的场景。和模式对象中的存储函数相比,它可以清楚地看到函数定义并避免了DDL操作带来的开销。

语法格式

WITH <函数> {<函数>}

参数

1.<函数> 语法遵照《DM8_SQL程序设计》中存储过程的语法规则。

图例

with function子句

with function子句

语句功能

供用户定义同一语句内临时使用的存储函数。

使用说明

  1. <WITH FUNCTION子句>中定义的函数的作用域为<WITH子句>所在的查询表达式内;
  2. 同一<WITH FUNCTION子句>中函数名不得重复;
  3. <WITH FUNCTION子句>中定义的函数不能是外部函数;
  4. 该语句的使用者并不需要CREATE PROCEDURE数据库权限。

举例说明

WITH FUNCTION中定义的函数优先级高于模式对象的例子。

WITH FUNCTION f1(C INT) RETURN INT AS BEGIN RETURN C \* 10; END;
SELECT f1(5236) FROM DUAL;
/

查询结果:

52360

WITH FUNCTION和公用表表达式混合的例子。

WITH FUNCTION f21(C1 INT) RETURN INT AS BEGIN RETURN C1; END;
SELECT f21(1) FROM dual WHERE 100 IN
(
  WITH FUNCTION f22(C1 INT) RETURN INT AS BEGIN RETURN C1 + 2; END;
     FUNCTION f23(C1 INT) RETURN INT AS BEGIN RETURN C1 - 2; END;
	  v21(C) AS (SELECT 50 FROM dual)
  SELECT f22(C) +f23(C) FROM v21
);
/

查询结果:

1

4.4.2 WITH CTE子句

嵌套SQL语句如果层次过多,会使SQL语句难以阅读和维护。如果将子查询放在临时表中,会使SQL语句更容易维护,但同时也增加了额外的I/O开销,因此,临时表并不太适合数据量大且频繁查询的情况。为此,在DM中引入了公用表表达式(CTE,COMMON TABLE EXPRESSION),使用CTE可以提高SQL语句的可维护性,同时CTE要比临时表的效率高很多。

CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE可自引用,还可在同一查询中引用多次。

WITH CTE子句会定义一个公用表达式,该公用表达式会被整个SQL语句所用到。它可以有效提高SQL语句的可读性,也可以用在UNION ALL中,作为提供数据的部分。

WITH CTE子句根据CTE是否递归执行CTE自身,DM将WITH CTE子句分为递归WITH和非递归WITH两种情况。

4.4.2.1 公用表表达式的作用

公用表表达式(CTE)是一个在查询中定义的临时命名结果集,将在FROM子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存,而且可以使用CTE来执行递归操作。
因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH CTE子句,则只要执行一遍即可。如果WITH CTE子句所定义的表名被调用两次以上,则优化器会自动将WITH CTE子句所获取的数据放入一个临时表里,如果只是被调用一次则不会,很多查询通过这种方法都可以提高速度。

4.4.2.2 非递归WITH的使用

语法格式

WITH <非递归with cte子句>{,<非递归with cte子句>}<cte查询语句>;
<非递归with cte子句>::= <公用表表达式的名称> [<列名> ({,<列名>})]  AS  ( <公用表表达式子查询语句>)

参数

1.<列名> 指明被创建的公用表表达式中列的名称;

2.<公用表表达式子查询语句> 标识公用表表达式所基于的表的行和列,其语法遵照SELECT语句的语法规则。

图例

非递归with

非递归with

<非递归with cte子句>:

非递归with cte子句

语句功能

供用户定义非递归公用表表达式,也就是非递归WITH语句。

使用说明

  1. < 公用表表达式的名称 >必须与在同一WITH子句中定义的任何其他公用表表达式的名称不同,但公用表表达式名可以与基表或基视图的名称相同。在查询中对公用表表达式名的任何引用都会使用公用表表达式,而不使用基对象;
  2. 在一个CTE定义中不允许出现重复的列名。指定的列名数必须与<公用表表达式子查询语句>结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的;
  3. <公用表表达式子查询语句>指定一个结果集填充公用表表达式的 SELECT 语句。除了CTE不能定义另一个 CTE 以外,<公用表表达式子查询语句> 的 SELECT语句必须满足与创建视图时相同的要求;
  4. <cte查询语句> SELECT查询语句。此处,语法上<cte查询语句>支持任意SELECT语句,但是对于CTE而言,只有<cte查询语句>中使用<公用表表达式的名称>,CTE才有意义。

权限

该语句的使用者必须对< cte查询语句>中的每个表均具有SELECT权限。

举例说明

公用表表达式可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。

创建一个表TEST1和表TEST2,并利用公用表表达式对它们进行连接运算。

CREATE TABLE TEST1(I INT);
INSERT INTO TEST1 VALUES(1);
INSERT INTO TEST1 VALUES(2);

CREATE TABLE TEST2(J INT);
INSERT INTO TEST2 VALUES(5);
INSERT INTO TEST2 VALUES(6);
INSERT INTO TEST2 VALUES(7);

WITH CTE1(K) AS(SELECT I FROM TEST1 WHERE I > 1),
CTE2(G) AS(SELECT J FROM TEST2 WHERE J > 5)
SELECT K, G FROM CTE1, CTE2;

运算结果:

        K               G
1       2               6
2       2               7

利用公用表表达式将表TEST1中的记录插入到TEST2表中。

INSERT INTO TEST2 WITH CTE1 AS(SELECT * FROM TEST1)
SELECT * FROM CTE1;

SELECT * FROM TEST2;

运行结果:

 J
-----------------

 5
 6
 7
 1
 2

4.4.2.3 递归WITH的使用

递归WITH是一个重复执行初始CTE以返回数据子集直到获取完整结果集的公用表表达式。递归WITH通常用于返回分层数据。

语法格式

WITH <递归with cte子句>{,<递归with cte子句>}<cte查询语句>;
<递归with cte子句>::=<公用表表达式的名称> (<列名>{,<列名>})  AS (<定位点成员> UNION ALL <递归成员>)

参数

1.<列名> 指明被创建的递归WITH中列的名称;各列不能同名,列名和AS后的列名没有关系,类似建视图时为视图指定的列别名。

2.<定位点成员> 任何不包含<公用表表达式的名称>的SELECT查询语句,可以UNION ALL、 UNION、 INTERSECT 或MINUS。定位点成员的查询结果集是递归成员迭代的基础。

3.<递归成员> 引用<公用表表达式的名称>的SELECT查询语句。递归成员通过引用自身<公用表表达式的名称>反复迭代执行,下一次迭代的数据基于上一次迭代的查询结果,当且仅当本次迭代结果为空集时才终止迭代。

4.<cte查询语句> SELECT查询语句。此处,语法上<cte查询语句>支持任意SELECT语句,但是对于CTE而言,只有<cte查询语句>中使用<公用表表达式的名称>,CTE才有意义。

图例

递归with

递归with

<递归with cte子句>:

递归with cte子句

语句功能

供用户定义递归公用表表达式,也就是递归WITH语句。

与递归WITH有关的两个INI参数为CTE_MAXRECURSION和CTE_OPT_FLAG。CTE_MAXRECURSION用来指定递归CTE迭代层次,取值范围[1,ULINT_MAX],默认100。CTE_OPT_FLAG用来指定递归WITH相关子查询是否转换为WITH FUNCTION优化,取值0或1,默认1。

递归WITH的执行流程如下:

  1. 将递归WITH拆分为定位点成员和递归成员;
  2. 运行定位点成员,创建第一个基准结果集 (T0);
  3. 运行递归成员,将 TI 作为输入(初始i=0),将 TI+1 作为输出,I=I++;
  4. 重复步骤 3,直到返回空集;
  5. 返回结果集为T0 到 TN 执行 UNION ALL 的结果。

使用说明

  1. <公用表表达式的名称>在定位点成员中不能出现。<公用表表达式的名称>在递归成员中有且只能引用一次;
  2. 递归成员中不能包含下面元素:
  • DISTINCT;
  • GROUP BY;
  • 集函数,但支持分析函数;
  • <公用表表达式的名称>不能在<递归with cte子句>中使用;
  • <公用表表达式的名称>不能作为<递归成员>中外连接OUTER JOIN的右表。
  1. <递归成员>中列的数据类型必须与定位点成员中相应列的数据类型兼容。

举例说明

DROP TABLE MYEMPLOYEES;
CREATE TABLE MYEMPLOYEES(
EMPLOYEEID  SMALLINT,
   FIRST_NAME  VARCHAR2 (30) NOT NULL,
   LAST_NAME  VARCHAR2 (40) NOT NULL,
   TITLE  		VARCHAR2 (50) NOT NULL,
   DEPTID  		SMALLINT NOT NULL,
   MANAGERID 	INT NULL);

INSERT INTO MYEMPLOYEES VALUES (1, 'KEN', 'SANCHEZ', 'CHIEF EXECUTIVE OFFICER', 16, NULL);
INSERT INTO MYEMPLOYEES VALUES (273, 'BRIAN', 'WELCKER', 'VICE PRESIDENT OF SALES', 3, 1);
INSERT INTO MYEMPLOYEES VALUES (274, 'STEPHEN','JIANG', 'NORTH AMERICAN SALES MANAGER',3, 273);
INSERT INTO MYEMPLOYEES VALUES (275, 'MICHAEL', 'BLYTHE', 'SALES REPRESENTATIVE', 3, 274);
INSERT INTO MYEMPLOYEES VALUES (276, 'LINDA', 'MITCHELL', 'SALES REPRESENTATIVE', 3, 274);
INSERT INTO MYEMPLOYEES VALUES (285, 'SYED', 'ABBAS', 'PACIFIC SALES MANAGER', 3, 273);
INSERT INTO MYEMPLOYEES VALUES (286, 'LYNN', 'TSOFLIAS', 'SALES REPRESENTATIVE', 3, 285);
INSERT INTO MYEMPLOYEES VALUES (16, 'DAVID', 'BRADLEY', 'MARKETING MANAGER', 4, 273);
INSERT INTO MYEMPLOYEES VALUES (23, 'MARY', 'GIBSON', 'MARKETING SPECIALIST', 4, 16);

commit;

上下级关系如下图所示:

上下级关系如下图所示

WITH DIRECTREPORTS(MANAGERID, EMPLOYEEID, TITLE, DEPTID) AS
(SELECT MANAGERID, EMPLOYEEID, TITLE, DEPTID 
    FROM MYEMPLOYEES
    WHERE MANAGERID IS NULL   --定位点成员
    UNION ALL
    SELECT E.MANAGERID, E.EMPLOYEEID, E.TITLE, E.DEPTID 
    FROM MYEMPLOYEES E
INNER JOIN DIRECTREPORTS D
ON E.MANAGERID = D.EMPLOYEEID  --递归成员
)
SELECT MANAGERID, EMPLOYEEID, TITLE  FROM DIRECTREPORTS;

递归调用执行步骤:

(1)产生定位点成员

MANAGERID EMPLOYEEID TITLE
--------- ---------- ------------------
NULL	1	CHIEF EXECUTIVE OFFICER 

(2)第一次迭代,返回一个成员

MANAGERID EMPLOYEEID TITLE
--------- ---------- ---------------------------
1 273 VICE PRESIDENT OF SALES

(3)第二次迭代,返回三个成员

MANAGERIDEMPLOYEEID TITLE
--- ----------------------------- ------
273 16  MARKETING MANAGER
273 274 NORTH AMERICAN SALES MANAGER
273 285 PACIFIC SALES MANAGER

(4)第三次迭代,返回四个成员

MANAGERID EMPLOYEEID TITLE
--------- ---------- -----------------------------
16	23	MARKETING SPECIALIST
274	275	SALES REPRESENTATIVE
274	276	SALES REPRESENTATIVE
285	286	SALES REPRESENTATIVE

(5)第四次迭代,返回空集。递归结束。

(6)正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集(UNION
ALL)。

MANAGERID EMPLOYEEID TITLE
--------- ---------- -----------------------------
NULL		1		CHIEF EXECUTIVE OFFICER
1	 	273		VICE PRESIDENT OF SALES
273	 	16	   	MARKETING MANAGER
273	 	274		NORTH AMERICAN SALES MANAGER
273	 	285		PACIFIC SALES MANAGER 
16	 	23	   	MARKETING SPECIALIST
274	 	275		SALES REPRESENTATIVE
274	 	276		SALES REPRESENTATIVE
285	 	286   	SALES REPRESENTATIVE

4.5 合并查询结果

DM提供了一种集合运算符:UNION,这种运算符将两个或多个查询块的结果集合并为一个结果集输出。语法如下:

语法格式

<查询表达式>
UNION [ALL][DISTINCT]
[ ( ]<查询表达式> [ ) ];

使用说明

  1. 每个查询块的查询列数目必须相同;
  2. 每个查询块对应的查询列的数据类型必须兼容;
  3. 在UNION后的可选项关键字ALL的意思是保持所有重复,而没有ALL的情况下表示删除所有重复;
  4. 在UNION后的可选项关键字DISTINCT的意思是删除所有重复。缺省值为DISTINCT。

举例说明


查询所有图书的出版商,查询所有图书供应商的名称,将两者连接,并去掉重复行。

SELECT PUBLISHER FROM PRODUCTION.PRODUCT
UNION
SELECT NAME FROM PURCHASING.VENDOR ORDER BY 1;

查询结果如表4.5.1所示。

表4.5.1
PUBLISHER
21世纪出版社
北京十月文艺出版社
长江文艺出版社
广州出版社
机械工业出版社
清华大学出版社 (注:末尾含空格)
清华大学出版社(注:末尾不含空格)
人民文学出版社
人民邮电出版社
上海出版社
上海画报出版社
外语教学与研究出版社
文学出版社
中华书局

UNION ALL。

SELECT PUBLISHER FROM PRODUCTION.PRODUCT
UNION ALL
SELECT NAME FROM PURCHASING.VENDOR ORDER BY 1;

查询结果如表4.5.2所示。

表4.5.2
PUBLISHER
21世纪出版社
21世纪出版社
北京十月文艺出版社
长江文艺出版社
广州出版社
广州出版社
机械工业出版社
机械工业出版社
清华大学出版社
清华大学出版社
人民文学出版社
人民邮电出版社
上海出版社
上海出版社
上海画报出版社
外语教学与研究出版社
外语教学与研究出版社
文学出版社
中华书局
中华书局
中华书局

4.6 GROUP BY和HAVING子句

GROUP BY子句逻辑地将由WHERE子句返回的临时结果重新编组。结果是行的集合,一组内一个分组列的所有值都是相同的。HAVING子句用于为组设置检索条件。

4.6.1 GROUP BY子句的使用

GROUP BY子句是SELECT语句的可选项部分。它定义了分组表。GROUP BY子句语法如下:

<GROUP BY 子句> ::= GROUP BY <group_by项>{,<group_by项>}
<group_by项>::=<分组项> | <ROLLUP项> | <CUBE项> | <GROUPING SETS项>
<分组项>::= <值表达式>
<ROLLUP项>::=ROLLUP (<分组项>)
<CUBE项>::=CUBE (<分组项>)
<GROUPING SETS项>::=GROUPING SETS(<GROUP项>{,<GROUP项>})
<GROUP项>::=<分组项> 
           |(<分组项>{,<分组项>})
           |()

GROUP BY定义了分组表:行组的集合,其中每一个组由其中所有分组列的值都相等的行构成。

统计每个部门的员工数。

SELECT DEPARTMENTID,COUNT(\*) FROM RESOURCES.EMPLOYEE_DEPARTMENT GROUP BY DEPARTMENTID;

查询结果如表4.6.1所示。

表4.6.1
DEPARTMENTID COUNT(*)
4 1
3 1
2 3
1 2

系统执行此语句时,首先将EMPLOYEE_DEPARTMENT表按DEPARTMENTID列进行分组,相同的DEPARTMENTID为一组,然后对每一组使用集函数COUNT(*),统计该组内的记录个数,如此继续,直到处理完最后一组,返回查询结果。

如果存在WHERE子句,系统先根据WHERE条件进行过滤,然后对满足条件的记录进行分组。

此外,GROUP BY不会对结果集排序。如果需要排序,可以使用ORDER BY子句。

求小说类别包含的子类别所对应的产品数量,并按子类别编号的升序排列。

SELECT A1.PRODUCT_SUBCATEGORYID AS 子分类编号,A3.NAME AS 子分类名,count(*)AS 
数量
FROM PRODUCTION.PRODUCT A1,
PRODUCTION.PRODUCT_CATEGORY A2,
PRODUCTION.PRODUCT_SUBCATEGORY A3
WHERE A1.PRODUCT_SUBCATEGORYID=A3.PRODUCT_SUBCATEGORYID 
AND  A2.PRODUCT_CATEGORYID=A3.PRODUCT_CATEGORYID
AND  A2.NAME='小说'
GROUP  BY  A1.PRODUCT_SUBCATEGORYID,A3.NAME
ORDER  BY  A1.PRODUCT_SUBCATEGORYID;

查询结果如表4.6.2所示。

表4.6.2
子分类编号 子分类名 数量
1 世界名著 1
2 武侠 1
4 四大名著 2

使用GROUP BY要注意以下问题:

1.在GROUP BY子句中的每一列必须明确地命名属于在FROM子句中命名的表的一列。分组列的数据类型不能是多媒体数据类型;

2.分组列不能为集函数表达式或者在SELECT子句中定义的别名;

3.当分组列值包含空值时,则空值作为一个独立组;

4.当分组列包含多个列名时,则按照GROUP BY子句中列出现的顺序进行分组;

5.GROUP BY子句中至多可包含255个分组列;

6.ROLLUP\CUBE\GROUPING SETS组合不能超过9个。

4.6.2 ROLLUP的使用

ROLLUP主要用于统计分析,对分组列以及分组列的部分子集进行分组,输出用户需要的结果。语法如下:

GROUP BY ROLLUP (<分组项>)
<分组项>::= <列名> | <值表达式>{,<列名> | <值表达式>}

假如ROLLUP分组列为(A, B, C),首先对(A,B,C)进行分组,然后对(A,B)进行分组,接着对(A)进行分组,最后对全表进行查询,无分组列,其中查询项中出现在ROLLUP中的列设为NULL。查询结果是把每种分组的结果集进行UNION ALL合并输出。如果分组列为n列,则共有n+1种组合方式。

按小区住址和所属行政区域统计员工居住分布情况。

SELECT CITY , ADDRESS1, COUNT(\*) as NUMS FROM PERSON.ADDRESS GROUP BY ROLLUP(CITY, ADDRESS1);

查询结果如表4.6.3所示。

表4.6.3
CITY ADDRESS1 NUMS
武汉市洪山区 洪山区369号金地太阳城56-1-202 1
武汉市洪山区 洪山区369号金地太阳城57-2-302 1
武汉市洪山区 洪山区保利花园50-1-304 1
武汉市洪山区 洪山区保利花园51-1-702 1
武汉市洪山区 洪山区关山春晓51-1-702 1
武汉市洪山区 洪山区关山春晓55-1-202 1
武汉市洪山区' 洪山区关山春晓10-1-202 1
武汉市洪山区 洪山区关山春晓11-1-202 1
武汉市洪山区 洪山区光谷软件园C1_501 1
武汉市青山区 青山区青翠苑1号 1
武汉市武昌区 武昌区武船新村115号 1
武汉市武昌区 武昌区武船新村1号 1
武汉市汉阳区 汉阳大道熊家湾15号 1
武汉市江汉区 江汉区发展大道561号 1
武汉市江汉区 江汉区发展大道555号 1
武汉市江汉区 江汉区发展大道423号 1
武汉市洪山区 NULL 9
武汉市青山区 NULL 1
武汉市武昌区 NULL 2
武汉市汉阳区 NULL 1
武汉市江汉区 NULL 3
NULL NULL 16

上例中的查询等价于:

SELECT CITY , ADDRESS1, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  
CITY, ADDRESS1
UNION ALL
SELECT CITY , NULL, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  CITY
UNION ALL
SELECT NULL , NULL, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS GROUP  BY  0;

使用ROLLUP要注意以下事项:

  1. ROLLUP项不能包含集函数;
  2. 不支持包含ROWNUM、WITH FUNCTION的相关查询;
  3. 不支持包含存在ROLLUP的嵌套相关子查询;
  4. ROLLUP项最多支持511个;
  5. ROLLUP项不能引用外层列。

4.6.3 CUBE的使用

CUBE的使用场景与ROLLUP类似,常用于统计分析,对分组列以及分区列的所有子集进行分组,输出所有分组结果。语法如下:

GROUP BY CUBE (<分组项>)
<分组项>::= <列名> | <值表达式>{,<列名> | <值表达式>}

假如,CUBE分组列为(A, B, C),则首先对(A,B,C)进行分组,然后依次对(A,B)、(A,C)、(A)、(B,C)、(B)、(C)六种情况进行分组,最后对全表进行查询,无分组列,其中查询项存在于CUBE列表的列设置为NULL。输出为每种分组的结果集进行UNION ALL。CUBE分组共有 2n 种组合方式。CUBE最多支持9列。

按小区住址、所属行政区域统计员工居住分布情况。

SELECT CITY , ADDRESS1, COUNT(\*) AS NUMS FROM PERSON.ADDRESS GROUP BY
CUBE(CITY, ADDRESS1);

查询结果如表4.6.4所示。

表4.6.4
CITY ADDRESS1 NUMS
武汉市洪山区 洪山区369号金地太阳城56-1-202 1
武汉市洪山区 洪山区369号金地太阳城57-2-302 1
武汉市洪山区 洪山区保利花园50-1-304 1
武汉市洪山区 洪山区保利花园51-1-702 1
武汉市洪山区 洪山区关山春晓51-1-702 1
武汉市洪山区 洪山区关山春晓55-1-202 1
武汉市洪山区 洪山区关山春晓10-1-202 1
武汉市洪山区 洪山区关山春晓11-1-202 1
武汉市洪山区 洪山区光谷软件园C1_501 1
武汉市青山区 青山区青翠苑1号 1
武汉市武昌区 武昌区武船新村115号 1
武汉市武昌区 武昌区武船新村1号 1
武汉市汉阳区 汉阳大道熊家湾15号 1
武汉市江汉区 江汉区发展大道561号 1
武汉市江汉区 江汉区发展大道555号 1
武汉市江汉区 江汉区发展大道423号 1
武汉市洪山区 NULL 9
武汉市青山区 NULL 1
武汉市武昌区 NULL 2
武汉市汉阳区 NULL 1
武汉市江汉区 NULL 3
NULL 洪山区369号金地太阳城56-1-202 1
NULL 洪山区369号金地太阳城57-2-302 1
NULL 洪山区保利花园50-1-304 1
NULL 洪山区保利花园51-1-702 1
NULL 洪山区关山春晓51-1-702 1
NULL 洪山区关山春晓55-1-202 1
NULL 洪山区关山春晓10-1-202 1
NULL 洪山区关山春晓11-1-202 1
NULL 洪山区光谷软件园C1_501 1
NULL 青山区青翠苑1号 1
NULL 武昌区武船新村115号 1
NULL 武昌区武船新村1号 1
NULL 汉阳大道熊家湾15号 1
NULL 江汉区发展大道561号 1
NULL 江汉区发展大道555号 1
NULL 江汉区发展大道423号 1
NULL NULL 16

上例中的查询等价于:

SELECT CITY , ADDRESS1, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  
CITY, ADDRESS1
UNION ALL
SELECT CITY , NULL, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  CITY
UNION ALL
SELECT NULL , ADDRESS1, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  
ADDRESS1
UNION ALL
SELECT NULL , NULL, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS;

使用CUBE要注意以下事项:

  1. CUBE项不能包含集函数;
  2. 不支持包含WITH FUNCTION的相关查询;
  3. 不支持包含存在CUBE的嵌套相关子查询;
  4. CUBE项最多支持9个;
  5. CUBE项不能引用外层列。

4.6.4 GROUPING的使用

GROUPING可以视为集函数,一般用于含GROUP BY的语句中,标识某子结果集是否是按指定分组项分组的结果,如果是,GROUPING值为0;否则为1。语法如下:

<GROUPING项>::=GROUPING (<分组项>)
<分组项>::= <列名> | <值表达式>

使用约束说明:

  1. GROUPING中只能包含一列;
  2. GROUPING只能在GROUP BY查询中使用;
  3. GROUPING不能在WHERE或连接条件中使用;
  4. GROUPING支持表达式运算。例如GROUPING(c1) + GROUPING(c2)。

按小区住址和所属行政区域统计员工居住分布情况。

SELECT GROUPING(CITY) AS G_CITY,GROUPING(ADDRESS1) AS G_ADD, CITY , ADDRESS1,
COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  ROLLUP(CITY, ADDRESS1);

查询结果如表4.6.5所示。

表4.6.5
G_CITY G_ADD CITY ADDRESS1 NUMS
0 0 武汉市洪山区 洪山区369号金地太阳城56-1-202 1
0 0 武汉市洪山区 洪山区369号金地太阳城57-2-302 1
0 0 武汉市洪山区 洪山区保利花园50-1-304 1
0 0 武汉市洪山区 洪山区保利花园51-1-702 1
0 0 武汉市洪山区 洪山区关山春晓51-1-702 1
0 0 武汉市洪山区 洪山区关山春晓55-1-202 1
0 0 武汉市洪山区' 洪山区关山春晓10-1-202 1
0 0 武汉市洪山区 洪山区关山春晓11-1-202 1
0 0 武汉市洪山区 洪山区光谷软件园C1_501 1
0 0 武汉市青山区 青山区青翠苑1号 1
0 0 武汉市武昌区 武昌区武船新村115号 1
0 0 武汉市武昌区 武昌区武船新村1号 1
0 0 武汉市汉阳区 汉阳大道熊家湾15号 1
0 0 武汉市江汉区 江汉区发展大道561号 1
0 0 武汉市江汉区 江汉区发展大道555号 1
0 0 武汉市江汉区 江汉区发展大道423号 1
0 1 武汉市洪山区 NULL 9
0 1 武汉市青山区 NULL 1
0 1 武汉市武昌区 NULL 2
0 1 武汉市汉阳区 NULL 1
0 1 武汉市江汉区 NULL 3
1 1 NULL NULL 16

4.6.5 GROUPING SETS的使用

GROUPING SETS是对GROUP BY的扩展,可以指定不同的列进行分组,每个分组列集作为一个分组单元。使用GROUPING SETS,用户可以灵活的指定分组方式,避免ROLLUP/CUBE过多的分组情况,满足实际应用需求。GROUPING SETS的分组过程为依次按照每一个分组单元进行分组,最后把每个分组结果进行UNION ALL输出最终结果。如果查询项不属于分组列,则用NULL代替。语法如下:

GROUP BY GROUPING SETS (<分组项>)
<分组项> ::= <分组子项> {,<分组子项>}
<分组子项> ::= <表达式> | () |(<表达式>{,<表达式>})
<表达式> ::= <列名> | <值表达式>

按照邮编、住址和行政区域统计员工住址分布情况。

SELECT CITY , ADDRESS1, POSTALCODE, COUNT(*) AS NUMS FROM PERSON.ADDRESS
GROUP BY GROUPING SETS((CITY, ADDRESS1), POSTALCODE);

查询结果如表4.6.6所示。

表4.6.6
CITY ADDRESS1 POSTALCODE NUMS
武汉市洪山区 洪山区369号金地太阳城56-1-202 NULL 1
武汉市洪山区 洪山区369号金地太阳城57-2-302 NULL 1
武汉市洪山区 洪山区保利花园50-1-304 NULL 1
武汉市洪山区 洪山区保利花园51-1-702 NULL 1
武汉市洪山区 洪山区关山春晓51-1-702 NULL 1
武汉市洪山区 洪山区关山春晓55-1-202 NULL 1
武汉市洪山区' 洪山区关山春晓10-1-202 NULL 1
武汉市洪山区 洪山区关山春晓11-1-202 NULL 1
武汉市洪山区 洪山区光谷软件园C1_501 NULL 1
武汉市青山区 青山区青翠苑1号 NULL 1
武汉市武昌区 武昌区武船新村115号 NULL 1
武汉市武昌区 武昌区武船新村1号 NULL 1
武汉市汉阳区 汉阳大道熊家湾15号 NULL 1
武汉市江汉区 江汉区发展大道561号 NULL 1
武汉市江汉区 江汉区发展大道555号 NULL 1
武汉市江汉区 江汉区发展大道423号 NULL 1
NULL NULL 430073 9
NULL NULL 430080 1
NULL NULL 430063 2
NULL NULL 430050 1
NULL NULL 430023 3

上例中的查询等价于:

SELECT CITY , ADDRESS1, NULL , COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  
BY  CITY, ADDRESS1
UNION ALL
SELECT NULL , NULL, POSTALCODE ,COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  	GROUP  BY  POSTALCODE;

使用GROUPING SETS要注意以下事项:

  1. GROUPING SETS项不能包含集函数;
  2. 不支持包含WITH FUNCTION的相关查询;
  3. 不支持包含存在GROUPING SETS的嵌套相关子查询;
  4. GROUPING SETS项最多支持512个;
  5. GROUPING SETS项不能引用外层列。

4.6.6 GROUPING_ID的使用

GROUPING_ID表示参数列是否为分组列。返回值的每一个二进制位表示对应的参数列是否为分组列,如果是分组列,该位值为0;否则为1。

使用GROUPING_ID可以按照列的分组情况过滤结果集。

语法如下:

<GROUPING_ID项>::=GROUPING_ID (<分组项>{,<分组项>)
<分组项>::= <列名> | <值表达式>

使用约束说明:

  1. GROUPING_ID中至少包含一列,最多包含63列;
  2. GROUPING_ID只能与分组项一起使用;
  3. GROUPING_ID支持表达式运算;
  4. GROUPING_ID作为分组函数,不能出现在where或连接条件中。

按小区住址和所属行政区域统计员工居住分布情况。

SELECT GROUPING(CITY) AS G_CITY,GROUPING(ADDRESS1) AS G_ADD, GROUPING_ID(CITY, ADDRESS1) AS G_CA,CITY , ADDRESS1, COUNT(\*) AS NUMS FROM PERSON.ADDRESS GROUP BY ROLLUP(CITY, ADDRESS1);

查询结果如表4.6.7所示。

表4.6.7
G_CITY G_ADD G_CA CITY ADDRESS1 NUMS
0 0 0 武汉市洪山区 洪山区369号金地太阳城56-1-202 1
0 0 0 武汉市洪山区 洪山区369号金地太阳城57-2-302 1
0 0 0 武汉市洪山区 洪山区保利花园50-1-304 1
0 0 0 武汉市洪山区 洪山区保利花园51-1-702 1
0 0 0 武汉市洪山区 洪山区关山春晓51-1-702 1
0 0 0 武汉市洪山区 洪山区关山春晓55-1-202 1
0 0 0 武汉市洪山区' 洪山区关山春晓10-1-202 1
0 0 0 武汉市洪山区 洪山区关山春晓11-1-202 1
0 0 0 武汉市洪山区 洪山区光谷软件园C1_501 1
0 0 0 武汉市青山区 青山区青翠苑1号 1
0 0 0 武汉市武昌区 武昌区武船新村115号 1
0 0 0 武汉市武昌区 武昌区武船新村1号 1
0 0 0 武汉市汉阳区 汉阳大道熊家湾15号 1
0 0 0 武汉市江汉区 江汉区发展大道561号 1
0 0 0 武汉市江汉区 江汉区发展大道555号 1
0 0 0 武汉市江汉区 江汉区发展大道423号 1
0 1 1 武汉市洪山区 NULL 9
0 1 1 武汉市青山区 NULL 1
0 1 1 武汉市武昌区 NULL 2
0 1 1 武汉市汉阳区 NULL 1
0 1 1 武汉市江汉区 NULL 3
1 1 3 NULL NULL 16

4.6.7 GROUP_ID的使用

GROUP_ID表示结果集来自于哪一个分组,用于区别相同分组的结果集。如果有N个相同分组,则GROUP_ID取值从0..N-1。每组的初始值为0。

当查询包含多个分组时,使用GROUP_ID可以方便的过滤相同分组的结果集。

<GROUP_ID项>::=GROUP_ID()

使用约束说明:

  1. GROUP_ID不包含参数;
  2. GROUP_ID只能与分组项一起使用;
  3. GROUP_ID支持表达式运算;
  4. GROUP_ID作为分组函数,不能出现在WHERE或连接条件中。

按小区住址和所属行政区域统计员工居住分布情况。

SELECT GROUPING(CITY) AS G_CITY,GROUPING(ADDRESS1) AS G_ADD, GROUP_ID() AS 
GID,CITY , ADDRESS1, COUNT(*) AS NUMS  FROM  PERSON.ADDRESS  GROUP  BY  ROLLUP(CITY, ADDRESS1), CITY;

查询结果如表4.6.8所示。

表4.6.8
G_CITY G_ADD GID CITY ADDRESS1 NUMS
0 0 0 武汉市洪山区 洪山区369号金地太阳城56-1-202 1
0 0 0 武汉市洪山区 洪山区369号金地太阳城57-2-302 1
0 0 0 武汉市洪山区 洪山区保利花园50-1-304 1
0 0 0 武汉市洪山区 洪山区保利花园51-1-702 1
0 0 0 武汉市洪山区 洪山区关山春晓51-1-702 1
0 0 0 武汉市洪山区 洪山区关山春晓55-1-202 1
0 0 0 武汉市洪山区' 洪山区关山春晓10-1-202 1
0 0 0 武汉市洪山区 洪山区关山春晓11-1-202 1
0 0 0 武汉市洪山区 洪山区光谷软件园C1_501 1
0 0 0 武汉市青山区 青山区青翠苑1号 1
0 0 0 武汉市武昌区 武昌区武船新村115号 1
0 0 0 武汉市武昌区 武昌区武船新村1号 1
0 0 0 武汉市汉阳区 汉阳大道熊家湾15号 1
0 0 0 武汉市江汉区 江汉区发展大道561号 1
0 0 0 武汉市江汉区 江汉区发展大道555号 1
0 0 0 武汉市江汉区 江汉区发展大道423号 1
0 1 0 武汉市洪山区 NULL 9
0 1 0 武汉市青山区 NULL 1
0 1 0 武汉市武昌区 NULL 2
0 1 0 武汉市汉阳区 NULL 1
0 1 0 武汉市江汉区 NULL 3
0 1 1 武汉市洪山区 NULL 9
0 1 1 武汉市青山区 NULL 1
0 1 1 武汉市武昌区 NULL 2
0 1 1 武汉市汉阳区 NULL 1
0 1 1 武汉市江汉区 NULL 3

4.6.8 HAVING子句的使用

HAVING子句是SELECT语句的可选项部分,它也定义了一个成组表。HAVING子句语法如下:

<HAVING 子句> ::= HAVING <搜索条件>
<搜索条件>::= <表达式>

HAVING子句定义了一个成组表,其中只含有搜索条件为TRUE的那些组,且通常跟随一个GROUP BY子句。HAVING子句与组的关系正如WHERE子句与表中行的关系。WHERE子句用于选择表中满足条件的行,而HAVING子句用于选择满足条件的组。

统计出同一子类别的产品数量大于1的子类别名称,数量,并按数量从小到大的顺序排列。

SELECT A2.NAME AS 子分类名, COUNT (*)AS 数量
FROM PRODUCTION.PRODUCT A1,
PRODUCTION.PRODUCT_SUBCATEGORY A2
WHERE A1.PRODUCT_SUBCATEGORYID=A2.PRODUCT_SUBCATEGORYID 
GROUP  BY  A2.NAME
HAVING COUNT(*)>1
ORDER  BY  2;

查询结果如表4.6.9所示。

表4.6.9
子分类名 数量
四大名著 2

系统执行此语句时,首先将PRODUCT表和PRODUCT_SUBCATEGORY表中的各行按相同的SUBCATEGORYID作连接,再按子类别名的取值进行分组,相同的子类别名为一组,然后对每一组使用集函数COUNT(*),统计该组内产品的数量,如此继续,直到最后一组。再选择产品数量大于1的组作为查询结果。

4.7 ORDER BY子句

ORDER BY子句可以选择性地出现在<查询表达式>之后,它规定了当行由查询返回时应具有的顺序。ORDER BY子句的语法如下:

<ORDER BY 子句> ::= ORDER [SIBLINGS] BY	< order_by_list>
<order_by_list>::= < order_by_list>{,<order_by_item>}
<order_by_item>::=  <exp> [ASC | DESC] [NULLS FIRST|LAST]
<exp>::=<无符号整数> | <列说明> | <值表达式>

使用说明

  1. ORDER BY子句提供了要排序的项目清单和他们的排序顺序:递增顺序(ASC,默认)或是递减顺序(DESC)。它必须跟随在<查询表达式>之后,因为它是在查询计算得出的最终结果上进行操作的;
  2. 排序键可以是任何在查询清单中的列的名称,或者是对最终结果表的列计算的表达式(即使这一列不在选择清单中),也可以是子查询。对于UNION查询语句,排序键必须在第一个查询子句中出现;对于GROUP
    BY分组的排序,排序键可以使用集函数,但GROUP BY分组中必须包含查询列中所有列;
  3. <无符号整数> 对应SELECT后结果列的序号。当用<无符号整数>代替列名时,<无符号整数>不应大于SELECT后结果列的个数。如下面例子中ORDER BY 3,因查询结果列只有2列,无法进行排序,系统将会报错。若采用其他常量表达式(如:-1,3×6)作为排序列,将不影响最终结果表的行输出顺序;
  4. SIBLINGS关键字必须与 CONNECT BY 一起配合使用,专门用于指定层次查询中相同层次数据返回的顺序。详见4.12.5 层次查询层内排序
  5. 无论采用何种方式标识想要排序的结果列,它们都不支持多媒体数据类型(如IMAGE、TEXT、BLOB和CLOB);
  6. 当排序列值包含NULL时,根据指定的“NULLS FIRST|LAST”决定包含空值的行是排在最前还是最后,缺省为NULLS FIRST;
  7. 当排序列包含多个列名时,系统则按列名从左到右排列的顺序,先按左边列将查询结果排序,当左边排序列值相等时,再按右边排序列排序……如此右推,逐个检查调整,最后得到排序结果;
  8. 由于ORDER BY只能在最终结果上操作,不能将其放在查询中;
  9. 如果ORDER BY后面使用集函数,则必须使用GROUP BY 分组,且GROUP BY分组中必须包含查询列中所有列;
  10. ORDER BY子句中至多可包含255个排序列。

将RESOURCES.DEPARTMENT表中的资产总值按从大到小的顺序排列。

SELECT * FROM RESOURCES.DEPARTMENT ORDER BY DEPARTMENTID DESC;

等价于:

SELECT * FROM RESOURCES.DEPARTMENT ORDER BY 1 DESC;

查询结果如表4.7.1所示。

表4.7.1
DEPARTMENTID NAME
5 广告部
4 行政部门
3 人力资源
2 销售部门
1 采购部门

SELECT * FROM RESOURCES.DEPARTMENT ORDER BY 3;

系统报错:无效的ORDER BY语句。

4.8 FOR UPDATE子句

FOR UPDATE子句可以选择性地出现在<查询表达式>之后。普通SELECT查询不会修改行数据物理记录上的TID事务号,FOR UPDATE会修改行数据物理记录上的TID事务号并对该TID上锁,以保证该更新操作的待更新数据不被其他事务修改。

语法格式

<FOR UPDATE 子句> ::= FOR READ ONLY
 					| <FOR UPDATE 选项>
<FOR UPDATE 选项> ::= FOR UPDATE [OF <选择列表>] [ NOWAIT 
						   |WAIT N 
						   |[N]SKIP LOCKED 
						]
<选择列表> ::= [<模式名>.] <基表名>|<视图名> .] <列名> {,[<模式名>.] <基表名>|<视图名> .] <列名>}
 

参数

  1. FOR READ ONLY 表示查询不可更新;

  2. OF <选择列表>指定待更新表的列。指定某张表的列,即为锁定某张表。游标更新时,仅能更新指定的列;

  3. NOWAIT,WAIT,SKIP LOCKED等子句表示当试图上锁的行数据TID已经被其他事务上锁的处理方式:

    1. NOWAIT表示不等待,直接报错返回;
    2. WAIT N表示等待一段时间,其中的N值由用户指定,单位为秒。等待成功继续上锁,失败则报错返回。WAIT的指定值必须大于0,如果设置0自动转成NOWAIT方式;
    3. [N] SKIP LOCKED表示上锁时跳过已经被其他事务锁住的行,不返回这些行给客户端。N是整数,为DM特有的语法,表示当取得了N条数据后,便不再取数据了,直接返回N条结果;
    4. 如果FOR UPDATE不设置以上三种子句,则会一直等待锁被其他事务释放;
    5. INI参数LOCK_TID_MODE用来标记SELECT FOR UPDATE封锁方式。0表示结果集小于100行时,直接封锁TID,超过100行升级为表锁。1表示不升级表锁,一律使用TID锁。默认为1。

查询RESOURCES.DEPARTMENT表中的资产。

SELECT * FROM RESOURCES.DEPARTMENT FOR UPDATE; --只要FOR UPDATE语句不提交,其他会话就不能修改此结果集。

查询结果如表4.8.1所示。

表4.8.1
DEPARTMENTID NAME
5 广告部
4 行政部门
3 人力资源
2 销售部门
1 采购部门

需要说明的是:

  1. 以下情况SELECT FOR UPDATE查询会报错:

    1. 带GROUP BY的查询,如SELECT C1, COUNT(C2) FROM TEST GROUP BY C1 FOR UPDATE;
    2. 带聚集函数的查询,如SELECT MAX(C1)FROM TEST FOR UPDATE;
    3. 带分析函数的查询,如SELECT MAX(C1) OVER(PARTITION BY C1) FROM TEST FOR UPDATE;
    4. 对以下表类型的查询:外部表、物化视图、系统表和HUGE表;
    5. WITH子句,如WITH TEST(C1) AS (SELECT C1 FROM T FOR UPDATE )SELECT * FROM TEST。
  2. 涉及DBLINK的SELECT FOR UPDATE查询仅支持单表;

  3. 如果结果集中包含LOB对象,会再封锁LOB对象;

  4. 支持多表连接的情况,会封锁涉及到的所有表的行数据;

  5. 多表连接的时候,如果用OF <选择列表>指定具体列,只会检测和封锁对应的表。例如SELECT C1 FROM TEST, TESTB FOR UPDATE OF TEST.C1即使TESTB表类型不支持FOR UPDATE,上述语句还是可以成功。

4.9 TOP子句

在DM中,可以使用TOP子句来筛选结果。语法如下:

<TOP子句>::=TOP <n> 
		| <n1>,<n2>
		| <n> PERCENT
		| <n> WITH TIES
		| <n> PERCENT WITH TIES
<n>::=整数(>=0)

参数

  1. TOP <n> 选择结果的前n条记录;
  2. TOP <n1>,<n2> 选择第n1条记录之后的n2条记录;
  3. TOP <n> PERCENT 表示选择结果的前n%条记录;
  4. TOP <n> PERCENT WITH TIES表示选择结果的前n%条记录,同时指定结果集可以返回额外的行。额外的行是指与最后一行以相同的排序键排序的所有行。WITH TIES必须与ORDER BY子句同时出现,如果没有ORDER BY子句,则忽略WITH TIES。

查询现价最贵的两种产品的编号和名称。

SELECT TOP 2 PRODUCTID,NAME FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE DESC;

其结果如表4.9.1所示:

表4.9.1
PRODUCTID NAME
10 噼里啪啦丛书(全7册)
6 长征

查询现价第二贵的产品的编号和名称。

SELECT TOP 1,1 PRODUCTID,NAME FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE DESC;

其结果如表4.9.2所示:

表4.9.2
PRODUCTID NAME
6 长征

查询最新出版日期的70%的产品编号、名称和出版日期。

SELECT TOP 70 PERCENT WITH TIES PRODUCTID,NAME,PUBLISHTIME FROM
PRODUCTION.PRODUCT ORDER BY PUBLISHTIME DESC;

其结果如表4.9.3所示:

表4.9.3
PRODUCTID NAME PUBLISHTIME
7 数据结构(C语言版)(附光盘) 2007-03-01
5 鲁迅文集(小说、散文、杂文)全两册 2006-09-01
6 长征 2006-09-01
3 老人与海 2006-08-01
8 工作中无小事 2006-01-01
4 射雕英雄传(全四册) 2005-12-01
2 水浒传 2005-04-01
1 红楼梦 2005-04-01

4.10 LIMIT限定条件

在DM中,可以使用限定条件对结果集做出筛选,支持LIMIT子句和ROW_LIMIT子句两种方式。

4.10.1 LIMIT子句

LIMIT子句按顺序选取结果集中某条记录开始的N条记录。语法如下

<LIMIT子句>::=<LIMIT子句1> | <LIMIT子句2>
<LIMIT子句1>::= LIMIT <记录数> 
		    | <记录数>,<记录数>
		    | <记录数> OFFSET <偏移量>
<LIMIT子句2>::= OFFSET <偏移量> LIMIT <记录数>
<记录数>::=<整数>
<偏移量>::=<整数>

共支持四种方式:

  1. LIMIT N:选择前N条记录;
  2. LIMIT M,N:选择第M条记录之后的N条记录;
  3. LIMIT M OFFSET N:选择第N条记录之后的M条记录;
  4. OFFSET N LIMIT M:选择第N条记录之后的M条记录。

注意:LIMIT不能与TOP同时出现在查询语句中。

查询前2条记录

SELECT PRODUCTID , NAME FROM PRODUCTION.PRODUCT LIMIT 2;

其结果如表4.10.1所示:

表4.10.1
PRODUCTID NAME
1 红楼梦
2 水浒传

查询第3,4个登记的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT LIMIT 2 OFFSET 2;

其结果如表4.10.2所示:

表4.10.2
PRODUCTID NAME
3 老人与海
4 射雕英雄传(全四册)

查询前第5,6,7个登记的姓名。

SELECT PERSONID,NAME FROM PERSON.PERSON LIMIT 4,3;

其结果如表4.10.3所示:

表4.10.3
PERSONID NAME
5 孙丽
6 黄非
7 王菲

4.10.2 ROW_LIMIT子句

ROW_LIMIT子句用于指定查询结果中偏移位置的行数或者百分比行数,以便更为灵活地获取查询结果。

语法如下:

< ROW_LIMIT子句>::= [OFFSET <offset> <ROW | ROWS> ] [<FETCH说明>]
<FETCH说明>::= FETCH <FIRST | NEXT> <大小> [PERCENT] < ROW | ROWS ><ONLY| WITH TIES>

参数

  1. <offset>指定查询返回行的起始偏移。必须为数字。offset为负数时视为0;为NULL或大于等于所返回的行数时,返回0行;为小数时,小数部分截断;
  2. <FIRST | NEXT> FIRST为从偏移为0的位置开始。NEXT为从指定的偏移的下一行开始获取结果。只做注释说明的作用,没有实际的限定作用;
  3. <大小>[PERCENT]指定返回行的行数(无PERCENT)或者百分比(有PERCENT)。其中<大小>只能为数字。percent指定为负数时,视为0%;为NULL时返回0行,如果没有指定percent,返回1行;
  4. <ONLY | WITH TIES>指定结果集是否返回额外的行。额外的行是指与最后一行以相同的排序键排序的所有行。ONLY为只返回指定的行数。WITH TIES必须与ORDER BY子句同时出现,如果没有ORDER BY子句,则忽略WITH TIES。

使用说明

  1. ROW_LIMIT子句不能与FOR UPDATE子句一起使用;
  2. 使用ROW_LIMIT子句时,查询列中不能包含有CURRVAL或者NEXTVAL伪列;
  3. 视图的查询定义中包含有ROW_LIMIT子句时,这个视图不会增量刷新。

查询价格最便宜的50%的商品

SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE FETCH FIRST 50 PERCENT ROWS ONLY;

其结果如表4.10.4所示:

表4.10.4
NAME NOWPRICE
老人与海 6.1000
突破英文基础词汇 11.100
工作中无小事 11.4000
水浒传 14.3000
红楼梦 15.2000

查询价格第3便宜开始的3条记录

SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY;

其结果如表4.10.5所示:

表4.10.5
NAME NOWPRICE
工作中无小事 11.4000
水浒传 14.3000
红楼梦 15.2000

4.11 全文检索

DM数据库提供多文本数据检索服务,包括全文索引和全文检索。全文索引为在字符串数据中进行复杂的词搜索提供了有效支持。全文索引存储关于词和词在特定列中的位置信息,全文检索利用这些信息,可以快速搜索包含某个词或某一组词的记录。

执行全文检索涉及到以下这些任务:

  1. 对需要进行全文检索的表和列进行注册;
  2. 对注册了的列的数据建立全文索引;
  3. 对注册了的列查询填充后的全文索引。

执行全文检索步骤如下:

1.建立全文索引;

2.修改(填充)全文索引;

3.使用带CONTAINS谓词的查询语句进行全文检索;

4.当数据表的全文索引列数据发生变化,则需要进行增量或者完全填充全文索引,以便可以查询到更新后的数据;

5.若不再需要全文索引,可以删除该索引;

6.在全文索引定义并填充后,才可进行全文检索。

全文检索通过在查询语句中使用CONTAINS子句进行。

语法格式

CONTAINS ( <列名> , <检索条件> )
<检索条件>::= <布尔项> | <检索条件> <AND | OR | AND NOT> <布尔项>
<布尔项>::= '字符串'

图例

全文检索

全文检索

使用说明

  1. 使用CONTAINS子句查询时,<列名>必须是已经建立了全文索引并填充后的列,否则系统会报错;
  2. 支持精确字、词、短语及一段文字的查询,CONTAINS谓词内支持 AND | AND NOT | OR的使用,AND的优先级高于OR的优先级;
  3. 支持对每个精确词(单字节语言中没有空格或标点符号的一个或多个字符)或短语(单字节语言中由空格和可选的标点符号分隔的一个或多个连续的词)的匹配。对词或短语中字符的搜索不区分大小写;
  4. 对于短语或一段文字的查询,根据词库,单个查找串被分解为若干个关键词,忽略词库中没有的词和标点符号,在索引上进行(关键词AND关键词)匹配查找。因而,不一定是精确查询;
  5. 英文查询不区分大小写和全角半角中英文字符;
  6. 不提供Noise文件,即不考虑忽略词或干扰词;
  7. 不支持通配符“*”;
  8. 不提供对模糊词或变形词的查找;
  9. 不支持对结果集的相关度排名;
  10. 检索条件子句可以和其他子句共同组成WHERE的检索条件。

举例说明

全文检索综合实例,以PRODUCT表为例。

(1)在DESCRIPTION列上定义全文索引。

CREATE CONTEXT INDEX INDEX1 ON PRODUCTION.PRODUCT(DESCRIPTION) LEXER CHINESE_VGRAM_LEXER;

(2)完全填充全文索引。

ALTER CONTEXT INDEX INDEX1 ON PRODUCTION.PRODUCT REBUILD;

(3)进行全文检索,查找描述里有“语言”字样的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT WHERE CONTAINS(DESCRIPTION,'语言');

结果如表4.11.1所示。

表4.11.1
PRODUCTID NAME
2 水浒传
7 数据结构(C语言版)(附光盘)

(4) 进行全文检索,查找描述里有“语言”及“中国”字样的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT WHERE CONTAINS(DESCRIPTION,'语言' AND '中国');

结果如表4.11.2所示。

表4.11.2
PRODUCTID NAME
2 水浒传

(5)进行全文检索,查找描述里有“语言”或“中国”字样的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT WHERE CONTAINS(DESCRIPTION,'语言' OR '中国');

结果如表4.11.3所示。

表4.11.3
PRODUCTID NAME
1 红楼梦
2 水浒传
7 数据结构(C语言版)(附光盘)

(6)进行全文检索,查找描述里无“中国”字样的雇员的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT WHERE NOT CONTAINS(DESCRIPTION,'中国');

结果如表4.11.4所示。

表4.11.4
PRODUCTID NAME
3 老人与海
4 射雕英雄传(全四册)
5 鲁迅文集(小说、散文、杂文)全两册
6 长征
7 数据结构(C语言版)(附光盘)
8 工作中无小事
9 突破英文基础词汇
10 噼里啪啦丛书(全7册)

(7)进行全文检索,查找描述里有“C语言”字样的产品的编号和名称。

SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT WHERE CONTAINS(DESCRIPTION,'C语言');

结果如表4.11.5所示。

表4.11.5
PRODUCTID NAME
7 数据结构(C语言版)(附光盘)

(8)对不再需要的全文索引进行删除。

DROP CONTEXT INDEX INDEX1 ON PRODUCTION.PRODUCT;

4.12 层次查询子句

可通过层次查询子句进行层次查询,得到数据间的层次关系。在使用层次查询子句时,可以使用层次查询相关的伪列、函数或操作符来明确层次查询结果中的相应层次信息。

4.12.1 层次查询子句

语法格式

<层次查询子句> ::= 
	CONNECT BY [NOCYCLE]  <连接条件> [ START WITH <起始条件> ] |
	START WITH <起始条件> CONNECT BY [NOCYCLE] <连接条件>
<连接条件>::= <逻辑表达式>
<起始条件>::= <逻辑表达式>

参数

  1. <连接条件> 逻辑表达式,指明层次数据间的层次连接关系;
  2. <起始条件> 逻辑表达式,指明选择层次数据根数据的条件;
  3. NOCYCLE关键字用于指定数据导致环的处理方式,如果在层次查询子句中指定NOCYCLE关键字,会忽略导致环元组的儿子数据。否则,返回错误。

4.12.2 层次查询相关伪列

在使用层次查询子句时,可以通过相关的伪列来明确数据的层次信息。层次查询相关的伪列有:

1.LEVEL 该伪列表示当前元组在层次数据形成的树结构中的层数。LEVEL的初始值为1,即层次数据的根节点数据的LEVEL值为1,之后其子孙节点的LEVEL依次递增。

2.CONNECT_BY_ISLEAF 该伪列表示当前元组在层次数据形成的树结构中是否是叶节点(即该元组根据连接条件不存在子结点)。是叶节点时为1,否则为0。

3.CONNECT_BY_ISCYCLE 该伪列表示当前元组是否会将层次数据形成环,该伪列只有在层次查询子句中表明NOCYCLE关键字时才有意义。如果元组的存在会导致层次数据形成环,该伪列值为1,否则为0。

4.12.3 层次查询相关操作符

1.PRIOR

PRIOR操作符主要使用在层次查询子句中,指明PRIOR之后的参数为逻辑表达式中的父节点。

PRIOR操作符还可以出现在查询项、WHERE条件、GROUP BY子句、集函数参数中,表示父层记录对应的值。

SELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY NOCYCLE  PRIOR DEP_NAME = HIGH_DEP;  --DEP_NAME为父节点。下一条记录的HIGH_DEP等于前一条记录的DEP_NAME
或者 
SELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY NOCYCLE  DEP_NAME = PRIOR  HIGH_DEP;   -- HIGH_DEP 为父节点。下一条记录的DEP_NAME等于前一条记录的HIGH_DEP

2.CONNECT_BY_ROOT

该操作符作为查询项,查询在层次查询结果中根节点的某列的值。

4.12.4 层次查询相关函数

语法格式

SYS_CONNECT_BY_PATH(col_name,char)

语句功能

层次查询。

使用说明

该函数得到从根节点到当前节点路径上所有节点名为col_name的某列的值,之间用char指明的字符分隔开。

4.12.5 层次查询层内排序

语法格式

ORDER SIBLINGS BY <order_by_list>
<order_by_list>请参考4.7 ORDER BY子句

语句功能

层次查询。

使用说明

ORDER SIBLINGS BY用于指定层次查询中相同层次数据返回的顺序。在层次查询中使用ORDER SIBLINGS BY,必须与CONNECT BY一起配合使用。但是,ORDER SIBLINGS BY不能和GROUP BY一起使用。

4.12.6 层次查询的限制

  1. START WITH子句中不能使用层次查询的所有伪列、层次查询函数、操作符;
  2. ORDER SIBLINGS BY子句中不能使用层次查询的所有伪列、层次查询函数、操作符、ROWNUM以及子查询;
  3. 层次查询子句不能使用伪列CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE、SYS_CONNECT_BY_PATH伪函数和CONNECT_BY_ROOT操作符;
  4. JOIN ON子句中不允许出现层次查询的所有伪列、层次查询函数;
  5. PRIOR、CONNECT_BY_ROOT操作符后以及SYS_CONNECT_BY_PATH第一个参数不能使用层次查询的所有伪列、层次查询函数、操作符、ROWNUM以及子查询。但SYS_CONNECT_BY_PATH的第一个参数允许出现LEVEL伪列且第二个参数必须是常量字符串,CONNECT_BY_ROOT LEVEL也被允许;
  6. 函数SYS_CONNECT_BY_PATH的最大返回长度为8188,超长就会报错。函数SYS_CONNECT_BY_PATH在一个查询语句中最多使用个数为64;
  7. INI参数CNNTB_MAX_LEVEL表示支持层次查询的最大层次,默认为20000。该参数的有效取值为[1,100000]。

对OTHER.DEPARTMENT数据进行层次查询,HIGH_DEP表示上级部门;DEP_NAME表示部门名称。

层次数据所建立起来的树形结构如下图:

层次数据树形结构图

图4.12.1 层次数据树形结构图

1.不带起始选择根节点起始条件的层次查询

SELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME = HIGH_DEP;

查询结果如表4.12.1所示。

表4.12.1
序号 HIGH_DEP DEP_NAME
1 NULL 总公司
2 总公司 服务部
3 服务部 网络服务部
4 服务部 读者服务部
5 读者服务部 书籍借阅服务部
6 读者服务部 书籍阅览服务部
7 服务部 企业服务部
8 总公司 采购部
9 总公司 财务部
10 总公司 财务部
11 总公司 采购部
12 总公司 服务部
13 服务部 网络服务部
14 服务部 读者服务部
15 读者服务部 书籍借阅服务部
16 读者服务部 书籍阅览服务部
17 服务部 企业服务部
18 服务部 企业服务部
19 服务部 读者服务部
20 读者服务部 书籍借阅服务部
21 读者服务部 书籍阅览服务部
22 服务部 网络服务部
23 读者服务部 书籍阅览服务部
24 读者服务部 书籍借阅服务部

结果是以表中所有的节点为根节点进行先根遍历进行层次查询。

2.带起始选择根节点起始条件的层次查询

SELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司';

查询结果如表4.12.2所示。

表4.12.2
序号 HIGH_DEP DEP_NAME
1 NULL 总公司
2 总公司 服务部
3 服务部 网络服务部
4 服务部 读者服务部
5 读者服务部 书籍借阅服务部
6 读者服务部 书籍阅览服务部
7 服务部 企业服务部
8 总公司 采购部
9 总公司 财务部

3.层次查询伪列的使用

在层次查询中,伪列的使用可以更明确层次数据之间的关系。

SELECT LEVEL,
CONNECT_BY_ISLEAF ISLEAF,
CONNECT_BY_ISCYCLE ISCYCLE,
HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT
CONNECT BY PRIOR DEP_NAME=HIGH_DEP
START WITH DEP_NAME='总公司';

查询结果如表4.12.3所示。

表4.12.3
序号 LEVEL ISLEAF ISCYCLE HIGH_DEP DEP_NAME
1 1 0 0 NULL 总公司
2 2 0 0 总公司 服务部
3 3 1 0 服务部 网络服务部
4 3 0 0 服务部 读者服务部
5 4 1 0 读者服务部 书籍借阅服务部
6 4 1 0 读者服务部 书籍阅览服务部
7 3 1 0 服务部 企业服务部
8 2 1 0 总公司 采购部
9 2 1 0 总公司 财务部

通过伪列,可以清楚地看到层次数据之间的层次结构。

4.含有过滤条件的层次查询

在层次查询中加入过滤条件,将会先进行层次查询,然后进行过滤。

SELECT LEVEL,* FROM OTHER.DEPARTMENT WHERE HIGH_DEP = '总公司' CONNECT BY PRIOR DEP_NAME=HIGH_DEP;

查询结果如表4.12.4所示。

表4.12.4
序号 LEVEL HIGH_DEP DEP_NAME
1 2 总公司 服务部
2 2 总公司 采购部
3 2 总公司 财务部
4 1 总公司 财务部
5 1 总公司 采购部
6 1 总公司 服务部

5.含有排序子句的层次查询

在层次查询中加入排序,查询将会按照排序子句指明的要求排序,不再按照层次查询的排序顺序排序。

SELECT * FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司' ORDER BY HIGH_DEP;

查询结果如表4.12.5所示。

表4.12.5
序号 HIGH_DEP DEP_NAME
1 NULL 总公司
2 读者服务部 书籍阅览服务部
3 读者服务部 书籍借阅服务部
4 服务部 读者服务部
5 服务部 企业服务部
6 服务部 网络服务部
7 总公司 财务部
8 总公司 采购部
9 总公司 服务部
  1. 含层内排序子句的层次查询

在层次查询中加入ORDER SIBLINGS BY,查询会对相同层次的数据进行排序后,深度优先探索返回数据,即LEVEL相同的数据进行排序。

SELECT HIGH_DEP, DEP_NAME, LEVEL FROM OTHER.DEPARTMENT CONNECT BY PRIOR
DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司' ORDER SIBLINGS BY DEP_NAME;

查询结果如表4.12.6所示。

表4.12.6
序号 HIGH_DEP DEP_NAME LEVEL
1 NULL 总公司 1
2 总公司 财务部 2
3 总公司 采购部 2
4 总公司 服务部 2
5 服务部 读者服务部 3
6 读者服务部 书籍借阅服务部 4
7 读者服务部 书籍阅览服务部 4
8 服务部 企业服务部 3
9 服务部 网络服务部 3

7.CONNECT_BY_ROOT操作符的使用

CONNECT_BY_ROOT 操作符之后跟某列的列名,例如:

CONNECT_BY_ROOT DEP_NAME

进行如下查询:

SELECT CONNECT_BY_ROOT DEP_NAME,\* FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司' ;

查询结果如表4.12.7所示。

表4.12.7
序号 CONNECT_BY_ROOT DEP_NAME HIGH_DEP DEP_NAME
1 总公司 NULL 总公司
2 总公司 总公司 服务部
3 总公司 服务部 网络服务部
4 总公司 服务部 读者服务部
5 总公司 读者服务部 书籍借阅服务部
6 总公司 读者服务部 书籍阅览服务部
7 总公司 服务部 企业服务部
8 总公司 总公司 采购部
9 总公司 总公司 财务部

8.SYS_CONNECT_BY_PATH函数的使用

函数的使用方式,如:

SYS_CONNECT_BY_PATH(DEP_NAME, '/')

进行如下查询:

SELECT SYS_CONNECT_BY_PATH(DEP_NAME, '/') PATH,* FROM OTHER.DEPARTMENT CONNECT
BY PRIOR DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司' ;

查询结果如表4.12.8所示。

表4.12.8
序号 PATH HIGH_DEP DEP_NAME
1 /总公司 NULL 总公司
2 /总公司/服务部 总公司 服务部
3 /总公司/服务部/网络服务部 服务部 网络服务部
4 /总公司/服务部/读者服务部 服务部 读者服务部
5 /总公司/服务部/读者服务部/书籍借阅服务部 读者服务部 书籍借阅服务部
6 /总公司/服务部/读者服务部/书籍阅览服务部 读者服务部 书籍阅览服务部
7 /总公司/服务部/企业服务部 服务部 企业服务部
8 /总公司/采购部 总公司 采购部
9 /总公司/财务部 总公司 财务部

4.13 并行查询

达梦支持并行查询技术。首先设置好如下三个INI参数,之后执行SQL语句,即可执行并行查询。三个INI参数解释如下表。

表4.13.1 并行查询相关参数
参数名 缺省值 说明
MAX_PARALLEL_DEGREE 1 用来设置最大并行任务个数。取值范围:1~128。缺省值1,表示无并行任务。全局有效。当PARALLEL_POLICY值为1时该参数值才有效。
PARALLEL_POLICY 0 用来设置并行策略。取值范围:0、1和2,缺省为0。其中,0表示不支持并行;1表示自动配置并行工作线程个数(与物理CPU核数相同);2表示手动设置并行工作线程数。当PARALLEL_POLICY值为2时,需手动指定当前并行任务个数。
PARALLEL_THRD_NUM 10 用来设置并行工作线程个数。取值范围:1~1024。仅当PARALLEL_POLICY值为2时才启用此参数。

注:当处于DMSQL程序调试状态时,并行查询的相关设置均无效。

其中,并行任务数也可以在SQL语句中使用“PARALLEL”关键字特别指定。如果单条查询语句没有特别指定,则依然使用默认并行任务个数。“PARALLEL”关键字的用法为在数据查询语句的SELECT关键字后增加HINT子句。

语法格式

/*+ PARALLEL([<表名>] <并行任务个数>) */

使用说明

对于无特殊要求的并行查询用户,可以使用默认并行任务数MAX_PARALLEL_DEGREE。只需要在INI参数中设置好对应参数,然后执行SQL查询语句,就可以启用并行查询。

举例说明

例1 将PARALLEL_POLICY设置为0,表示不支持并行查询。此时,另外两个参数不起任何作用。

PARALLEL_POLICY 0

例2 将PARALLEL_POLICY设置为1,表示自动配置并行工作线程个数,因此,只要设置下面2个参数就可以。

MAX_PARALLEL_DEGREE   3
PARALLEL_POLIC	      1

然后,执行SQL语句。

SELECT * FROM SYSOBJECTS; ---- 本条语句使用默认并行任务数3

当然,如果单条查询语句不想使用默认并行任务数3,可以通过在SQL语句中增加HINT,通过“PARALLEL”关键字特别指定。本条语句使用特别指定的并行任务数4,例如:

SELECT /*+ PARALLEL(4) */   *   FROM SYSOBJECTS;

例3 将PARALLEL_POLICY设置为2,表示手动配置并行工作线程个数,因此,指定如下2个参数。

PARALLEL_POLICY      2
PARALLEL_THRD_NUM    4

然后,在执行SQL语句时,需手动指定当前并行任务个数。若不指定,将不使用并行。

SELECT /*+ PARALLEL(2) */ * FROM SYSOBJECTS; ----本条语句使用并行任务数2。

4.14 ROWNUM

ROWNUM是一个虚假的列,表示从表中查询的行号,或者连接查询的结果集行数。它将被分配为1,2,3,4,...N,N是行的数量。通过使用ROWNUM可以限制查询返回的行数。例如,以下语句执行只会返回前5行数据。

SELECT * FROM RESOURCES.EMPLOYEE WHERE ROWNUM < 6;

一个ROWNUM值不是被永久的分配给一行。表中的某一行并没有标号,不可以查询ROWNUM值为5的行。ROWNUM值只有当被分配之后才会增长,并且初始值为1。即只有满足一行后,ROWNUM值才会加1,否则只会维持原值不变。因此,以下语句在任何时候都不能返回数据。

SELECT * FROM RESOURCES.EMPLOYEE WHERE ROWNUM > 11;
SELECT * FROM RESOURCES.EMPLOYEE WHERE ROWNUM = 5;

ROWNUM的一个重要作用是控制返回结果集的规模,可以避免查询在磁盘中排序。

因为ROWNUM值的分配是在查询的谓词解析之后,任何排序和聚合之前进行的。因此,在排序和聚合使用ROWNUM时需要注意,可能得到并非预期的结果,例如:

SELECT * FROM RESOURCES.EMPLOYEE WHERE ROWNUM < 11 ORDER BY EMPLOYEEID;

以上语句只会对EMPLOYEE表前10行数据按EMPLOYEEID排序输出,并不是表的所有数据按EMPLOYEEID排序后输出前10行,要实现后者,需要使用如下语句:

SELECT * FROM (SELECT * FROM RESOURCES.EMPLOYEE ORDER BY EMPLOYEEID) WHERE ROWNUM < 11;
SELECT TOP 10 * FROM RESOURCES.EMPLOYEE ORDER BY EMPLOYEEID;

使用说明

1.在查询中,ROWNUM可与任何数字类型表达式进行比较及运算,但不能出现在含OR的布尔表达式中,否则报错处理;

2.ROWNUM可以在非相关子查询中使用;当参数ENABLE_RQ_TO_INV等于1时,部分相关子查询支持使用;

3.在非相关子查询中,ROWNUM只能实现与TOP相同的功能,因此子查询不能含ORDER BY和GROUP BY;

4.ROWNUM所处的子谓词只能为如下形式: ROWNUM op exp,exp的类型只能是立即数、参数和变量值,op ∈ {<, <=, >, >=, =,<>}。

4.15 数组查询

在DM中,可以通过查询语句查询数组信息。即<FROM子句>中<普通表>使用数组。语法如下:

FROM ARRAY <数组>

目前DM只支持一维数组的查询。

数组类型可以是记录类型和普通数据库类型。如果为记录类型的数组,则记录的成员都必须为标量(基本)数据类型。记录类型数组查询出来的列名为记录类型每一个属性的名字。普通数据库类型查询出来的列名均为“COLUMN_VALUE”。

例1 查看数组

SELECT * FROM ARRAY NEW INT[2]{1};

返回结果为:

COLUMN_VALUE
1
NULL

例2 数组与表的连接

DECLARE
   TYPE rrr IS RECORD (x INT, y INT);   
   TYPE ccc IS ARRAY rrr[];
   c ccc;
BEGIN
   c = NEW rrr[2];
   FOR i IN 1..2 LOOP
      c[i].x = i;
      c[i].y = i*2;
   END LOOP;
   SELECT arr.x, o.name  FROM ARRAY c arr, SYSOBJECTS o WHERE arr.x = o.id;
END; 

返回结果为:

X               NAME
1               SYSINDEXES
2               SYSCOLUMNS

4.16 查看执行计划与执行跟踪统计

4.16.1 EXPLAIN

EXPLAIN语句可以查看DML语句的执行计划。

语法格式

EXPLAIN <SQL语句>;
<SQL语句> ::= <删除语句> | <插入语句> | <查询语句> | <更新语句>

参数

1.<删除语句> 指数据删除语句;

2.<插入语句> 指数据插入语句;

3.<查询语句> 指查询语句;

4.<更新语句> 指数据更新语句。

图例

EXPLAIN 语句

EXPLAIN 语句

语句功能

供用户查看执行计划。

举例说明

显示如下语句的查询计划:

EXPLAIN SELECT NAME,schid 
FROM SYSOBJECTS 
WHERE SUBTYPE$='STAB' AND NAME
 NOT IN (
SELECT NAME FROM SYSOBJECTS WHERE NAME IN (SELECT NAME FROM SYSOBJECTS WHERE SUBTYPE$='STAB') AND TYPE$='DSYNOM')

可以看到返回结果:

1   #NSET2: [2, 1, 142] 
2     #PRJT2: [2, 1, 142]; exp_num(2), is_atom(FALSE) 
3       #HASH LEFT SEMI JOIN2: [2, 1, 142];  (ANTI), 
4         #SLCT2: [0, 20, 142]; SYSOBJECTS.SUBTYPE$ = STAB
5           #CSCN2: [0, 807, 142]; SYSINDEXSYSOBJECTS(SYSOBJECTS)
6         #PRJT2: [1, 1, 138]; exp_num(1), is_atom(FALSE) 
7           #HASH LEFT SEMI JOIN2: [1, 1, 138];  
8             #CSEK2: [0, 20, 138]; scan_type(ASC), SYSINDEXSYSOBJECTS(SYSOBJECTS), scan_range[(DSYNOM,min,min),(DSYNOM,max,max))
9             #SLCT2: [0, 20, 138]; SYSOBJECTS.SUBTYPE$ = STAB
10              #CSCN2: [0, 807, 138]; SYSINDEXSYSOBJECTS(SYSOBJECTS)

4.16.2 EXPLAIN FOR

EXPLAIN FOR语句也用于查看DML语句的执行计划,不过执行计划以结果集的方式返回。

EXPLAIN FOR显示的执行计划信息更加丰富,除了常规计划信息,还包括创建索引建议、分区表的起止分区信息等。重要的是,语句的计划保存在数据表中,方便用户随时查看,进行计划对比分析,可以作为性能分析的一种方法。

语法格式

EXPLAIN  [AS <计划名称>]  FOR <SQL语句>;
<SQL语句> ::= <删除语句> | <插入语句> | <查询语句> | <更新语句>

参数

1.<删除语句> 指数据删除语句;

2.<插入语句> 指数据插入语句;

3.<查询语句> 指查询语句;

4.<更新语句> 指数据更新语句。

图例

EXPLAIN FOR语句

EXPLAIN FOR语句

语句功能

供用户以结果集的方式查看执行计划。

举例说明

以结果集的方式显示如下语句的查询计划:

EXPLAIN FOR  SELECT NAME, SCHID FROM SYS.SYSOBJECTS WHERE SUBTYPE $='STAB';

4.17 SAMPLE子句

DM通过SAMPLE子句实现数据采样功能。

语法格式

<SAMPLE子句>::=SAMPLE (<表达式>) |
SAMPLE (<表达式>) SEED (<表达式>) |
SAMPLE BLOCK (<表达式>) |
SAMPLE BLOCK (<表达式>) SEED (<表达式>)

参数

  1. <表达式>输入整数与小数均可;
  2. SAMPLE (<表达式>) 按行采样。<表达式>表示采样百分比,取值范围[0.000001,100)。重复执行相同语句,返回的结果不要求一致;
  3. SAMPLE (<表达式>) SEED (<表达式>) 按行采样,并指定种子。其中SEED(<表达式>)表示种子,取值范围[0,4294967295]。重复执行相同的语句,每次返回相同的结果集;
  4. SAMPLE BLOCK (<表达式>) 按块(页)采样。<表达式>表示采样百分比,取值范围[0.000001,100)。重复执行相同语句,返回的结果不要求一致,允许返回空集;
  5. SAMPLE BLOCK (<表达式>) SEED (<表达式>) 按块(页)采样,并指定种子。其中,BLOCK (<表达式>)表示采样百分比,取值范围[0.000001,100)。SEED (<表达式>)表示种子,取值范围[0,4294967295]。重复执行相同语句,每次返回相同的结果集。

使用说明

  1. SAMPLE只能出现在单表或仅包含单表的视图后面;
  2. 包含过滤条件的SAMPLE查询,是对采样后的数据再进行过滤;
  3. 不能对连接查询、子查询使用SAMPLE子句。

举例说明

对PERSON.ADDRESS表按行进行种子为5的10%采样。

SELECT * FROM PERSON.ADDRESS SAMPLE(10) SEED(5);

可以看到返回结果:

ADDRESSID	   ADDRESS1	      ADDRESS2	   CITY	        POSTALCODE
16	        洪山区光谷软件园        C1_501	武汉市洪山区	    430073

4.18 水平分区表查询

SELECT语句从水平分区子表中检索数据,称水平分区子表查询,即<对象名>中使用的是<分区表>。水平分区父表的查询方式和普通表完全一样。

<分区表>::=
		[<模式名>.]<基表名> PARTITION (<一级分区名>) | 
        [<模式名>.]<基表名> PARTITION FOR (<表达式>,{<表达式>})|
		[<模式名>.]<基表名> SUBPARTITION (<子分区名>)|
        [<模式名>.]<基表名> SUBPARTITION FOR (<表达式>,{<表达式>})

参数

  1. <基表名> 水平分区表父表名称;
  2. <一级分区名> 水平分区表一级分区的名字;
  3. <子分区名> 由水平分区表中多级分区名字逐级通过下划线“_”连接在一起的组合名称,例如P1_P2_P3,其中P1是一级分区名、P2是二级分区名、P3是三级分区名。

使用说明

如果HASH分区不指定分区表名,而是通过指定哈希分区个数来建立哈希分区表,PARTITIONS后的数字表示哈希分区的分区数,使用这种方式建立的哈希分区表分区名是匿名的,DM统一使用DMHASHPART+分区号(从0开始)作为分区名。

举例说明

例1 查询一个LIST-RANGE三级水平分区表。

DROP TABLE STUDENT;
CREATE TABLE STUDENT(
NAME VARCHAR(20), 
AGE INT, 
SEX VARCHAR(10) CHECK (SEX IN ('MAIL','FEMAIL')), 
GRADE INT CHECK (GRADE IN (7,8,9))
)
PARTITION BY LIST(GRADE)
  SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE
  (
     SUBPARTITION Q1 VALUES('MAIL'),
     SUBPARTITION Q2 VALUES('FEMAIL')
  ),
  SUBPARTITION BY RANGE(AGE) SUBPARTITION TEMPLATE
  (
     SUBPARTITION R1 VALUES LESS THAN (12),
     SUBPARTITION R2 VALUES LESS THAN (15),
     SUBPARTITION R3 VALUES LESS THAN (MAXVALUE)
   )
(
  PARTITION P1 VALUES (7),
  PARTITION P2 VALUES (8),
  PARTITION P3 VALUES (9)
);

SELECT * FROM STUDENT;                                     --查询水平分区父表
SELECT * FROM STUDENT PARTITION(P1);				   	   --查询一级分区子表
SELECT * FROM STUDENT SUBPARTITION(P1_Q1); 		  		   --查询二级分区子表
SELECT * FROM STUDENT SUBPARTITION(P1_Q1_R1); 		       --查询三级分区子表

例2 查询一个指定HASH分区名的水平分区表。

CREATE TABLESPACE TS1 DATAFILE 'TS1.DBF' SIZE 128;
CREATE TABLESPACE TS2 DATAFILE 'TS2.DBF' SIZE 128;
CREATE TABLESPACE TS3 DATAFILE 'TS3.DBF' SIZE 128;
CREATE TABLESPACE TS4 DATAFILE 'TS4.DBF' SIZE 128;

DROP TABLE CP_TABLE_HASH  CASCADE;
CREATE TABLE CP_TABLE_HASH(
			C1		INT,
			C2		VARCHAR(256),
			C3		DATETIME,
			C4		BLOB
			)
			PARTITION BY HASH (C1)
			SUBPARTITION BY HASH(C2)
			SUBPARTITION TEMPLATE
			(SUBPARTITION PAR1 STORAGE (ON MAIN),
			 SUBPARTITION PAR2 STORAGE (ON TS1),
			 SUBPARTITION PAR3 STORAGE (ON TS2),
			 SUBPARTITION PAR4)
			(PARTITION PAR1 STORAGE (ON MAIN),
			 PARTITION PAR2 STORAGE (ON TS1),
			 PARTITION PAR3 STORAGE (ON TS2),
			 PARTITION PAR4)
			STORAGE (ON TS4) ;

SELECT * FROM CP_TABLE_HASH PARTITION(PAR1);	          --查询一级分区子表
SELECT * FROM CP_TABLE_HASH SUBPARTITION(PAR1_PAR1);      --查询二级分区子表

例3 查询一个指定HASH分区数的水平分区,查询CP_TABLE_HASH01第一个分区的数据。

DROP TABLE CP_TABLE_HASH01  CASCADE;
CREATE TABLE CP_TABLE_HASH(
C1		INT,
C2		VARCHAR(256),
C3		DATETIME,
C4		BLOB
)
PARTITION BY HASH (C1)
PARTITIONS 4 STORE IN (TS1, TS2, TS3, TS4);
SELECT * FROM CP_TABLE_HASH PARTITION (DMHASHPART0);          --查询一级分区子表
微信扫码
分享文档
扫一扫
联系客服