GBase 8s SQL 指南：教程———6编写高级SELECT语句

6编写高级SELECT语句

本章中增大了使用SELECT语句可执行的操作的范围。并使您能够执行更复杂的数据库查 询和数据处理。编写SELECT语句着重于SELECT语句语法中的五个子句。本章添加了 GROUP BY子句和HAVING子句。可以将GROUP BY子句与聚集函数配合使用来组织 FROM子句返回的行。可以包括HAVING子句来对GROUP BY子句返回的值设置条 件。

本章还扩展了连接的早期讨论。它说明了自连接（它使您能够将表连接至它本身）和四种 类型的外连接（在其中应用关键字OUTER来以不同的方式处理两个或多个连接的表）。 本章还介绍了相关和非相关子查询及其操作关键字，显示了如何使用UNION运算符来组 合查询。并定义了称为联合、相交和差异的集合运算。

本章中的示例显示如何在查询中使用SELECT语句子句的一部分或全部。子句必须按以下 顺序显示

1. Projection
2. FROM
3. WHERE

1. GROUP BY
2. HAVING
3. ORDER BY
4. INTO TEMP

有关以正确顺序使用所有这些子句的SELECT语句的示例，请参阅图5。

附加SELECT语句子句INTO （可用于在SQL API中指定程序和主变量）在SQL编程 和随产品提供的出版物中进行描述。

本章还描述嵌套的SELECT语句，其中子查询在主查询的Projection .FROM或 WHERE 子句中指定。其它几节说明SELECT语句如何定义和操作集合，以及如何对查询结果进行 集合运算。

1. GROUP BY 和 HAVING 子句

可选GROUP BY和HAVING子句向SELECT语句添加功能。可以在基本SELECT语 句中包括一个或全部两个子句来增大处理聚集的能力。

GROUP BY子句组合类似的行，针对Projection子句中列出的每个列，为具有相同值的每 组行生成单一结果行o HAVING子句在构成组之后对那些组设置条件。可以不带HAVING 子句使用GROUP BY子句或不带GROUP BY子句使用HAVING子句。

1. GROUP BY 子句

GROUP BY子句将表分为几组。此子句通常与为每个这样的组生成总结值的聚集函数组 合。编写SELECT语句中的某些示例显示了应用于整个表的聚集函数的用法。本章说明应 用于行组的聚集函数。

使用不带聚集的GROUP BY子句与在SELECT子句中使用DISTINCT （或UNIQUE） 关键字很相似。下列查询在选择特定列中描述。

图:查询

SELECT DISTINCT customer_num FROM orders;

还可以按以下查询编写此语句。

图:查询

SELECT customer_num FROM orders

GROUP BY customer_num;

图1和图2返回下列行。

图:查询结果

customejnum

101

104

106

110

124

126

127

GROUP BY子句将行收集到组中，因此每一组中的每一行具有相同的客户号。在没有选择 任何其它列的情况下，结束是唯一 customer_num值的列表。

GROUP BY子句的功能在将它与聚集函数配合使用时更明显。

下列查询检索每个订单的商品数和所有商品的总价。

图:查询

SELECT order_num, COUNT (*) number, SUM (total_price) price

FROM items

GROUP BY order_num;

GROUP BY子句导致items表的行数被收集为组，每个组由具有相同order_num值的行组 成（即，将每个订单的商品收集在一起）。在数据库服务器构成组之后，就在每个组中应 用聚集行数 COUNT 和 SUM 。

图4对每一组返回每一行。它还使用标号来为COUNT和SUM表达式的结果提供名称, 如下所示。

图:查询结果

order_num	number	price
1001	1	$250.00
1002	2	$1200.00
1003	3	$959.00
1004	4	$1416.00
1021	4	$1614.00
1022	3	$232.00
1023	6	$824.00

该结果将1 tems表的行收集到具有相同订单号的组中，并计算每个组中行的COUNT和价 格的SUM。

不能在GROUP BY子句中包含TEXT、BYTE、CLOB或BLOB列。要进行分组，必

须能够进行排序，并且这些数据类型不存在自然排序顺序。

与ORDER BY子句不同，GROUP BY子句不对数据进行排序。如果想要按特定顺序对数 据进行排序，或在投影列表中的聚集上排序，那么在GROUP BY子句之后包含ORDER BY子句。

下列查询与图4相同，但包括ORDER BY子句以按price的升序对检索到的行进行排序， 如下所示。

图:查询

SELECT order_num, COUNT(*) number, SUM (total_price) price

FROM items

GROUP BY order_num

ORDER BY price;

图:查询结果

order_num number		price
1010	2	$84.00
1011	1	$99.00
1013	4	$143.80
1022	3	$232.00
1001	1	$250.00
1020	2	$438.00
1006	5	$448.00
1002	2	$1200.00
1004	4	$1416.00
1014	2	$1440.00
1019	1	$1499.97
1021	4	$1614.00
1007	5	$1696.00

选择特定列一节描述如何在ORDER BY子句中使用整数来指示投影列表中列的位置。还 可以在GROUP BY子句中使用整数来指示列名的位置或在GROUP BY列表中显示标 号。

以下查询返回与图6所示相同的行。

图:查询

SELECT order_num, COUNT(*) number, SUM (total_price) price

FROM items

GROUP BY 1

ORDER BY 3;

构建查询时,Projection子句的投影列表中的所有非聚集列还必须包含在GROUP BY子句 中。具有GROUP BY子句的SELECT语句必须针对每一组返回一行。列出在GROUP BY 后面的列能够在一组中只反映一个特异值。并且可以返回该值。但是，未列出在GROUP BY 后面的列可在包含在组中的行中包含不同的值。

下列查询显示如何在连接表的SELECT语句中使用GROUP BY子句。

图:查询

SELECT o.order_num, SUM (i.total_price)

FROM orders o, items i

WHERE o.order_date > '01/01/98'

AND o.customer_num = 110

AND o.order_num = i.order_num

GROUP BY o.order_num;

该查询连接orders和items表，将表别名指定给它们，并返回以下所示的行。

图:查询结果

order_num (sum)

1008 $940.00

1015 $450.00

6.1.2 HAVING 子句

要完成GROUP BY子句，使用HAVING子句来在构成组之后将一个或多个限制条件应 用于这些组。HAVING子句对组的影响类似于WHERE子句限定个别行的方式，使用 HAVING子句的一个优点是可以在搜索条件中包括聚集，而在WHERE子句的搜索条件 中去不能包含聚集。

每个HAVING条件将组的一列或一个聚集表达式与组的另一个聚集表达式或与常量作比 较。可以使用HAVING来对列值或组列表中的聚集值设置条件。

下列查询返回具有两个商品以上的订单上每个商品的平均总价格。HAVING子句在每个组 构成时测试每个组，并选择由两行以上构成的那些组。

图:查询

SELECT order_num, COUNT(*) number, AVG (total_price) average

FROM items

GROUP BY order_num

HAVING COUNT(*) > 2;

图:查询结果

order_num number average

1003	3	$319.67
1004	4	$354.00
1005	4	$140.50
1006	5	$89.60
1007	5	$339.20
1013	4	$35.95
1016	4	$163.50
1017	3	$194.67
1018	5	$226.20
1021	4	$403.50
1022	3	$77.33
1023	6	$137.33

如果使用不带GROUP BY子句的HAVING子句，那么HAVING条件应用于满足搜索 条件的所有行。也就是说，满足搜索条件的所有行组成了一个组。

下列查询(图1的修改版本)只返回一行，即表中所有total_pnce值的平均数，如下所示。

图查询

SELECT AVG (total_price) average

FROM items

HAVING count(*) > 2;

图:查询结果

average

$270.97

如果图3与图1 一样，在Projection子句中包含了非聚集列order_num，那么必须将 GROUP BY子句与组列表中的列包含子啊一起。此外，如果不满足HAVING子句中的条 件，那么输出将显示列标题以及一条消息指示没有找到任何行。

下列查询包含可以在GBase 8s版本的交互SQL中使用的所有SELECT语句子句(命名 主变量的INTO子句只在SQL API中可用)。

图:查询

SELECT o.order_num, SUM (i.total_price) price,

paid_date - order_date span

FROM orders o, items i

WHERE o.order_date > '01/01/98'

AND o.customer_num > 110

AND o.ordejnum = i.order_num

GROUP BY 1,3

HAVING COUNT (*) < 5

ORDER BY 3

INTO TEMP temptab1;

该查询连接orders和items表；使用显示标号、表列名和用作列指示符的整数；对数据进行 分组和排序；并将结果放置在临时表中，如下所示。

图:查询结果

order_num	price	span
1017	$584.00
1016	$654.00
1012	$1040.00
1019	$1499.97	26
1005	$562.00	28
1021	$1614.00	30
1022	$232.00	40
1010	$84.00	66
1009	$450.00	68
1020	$438.00	71

6.2创建高级连接

创建连接一节显示如何在SELECT语句中包括 WHERE子句以在一个列或多个列上连接 两个或多个表。它说明了自然连接和等值连接。

本章讨论如何使用两种更复杂的连接：自连接和外连接。如对简单连接描述的那样，可以 为表定义别名并将显示标号指定给表达式以缩短多表查询时间。还可以带ORDER BY子 句发岀SELECT语句，这将把数据排序到临时表中。

6.2.1自连接

连接不一定总是涉及两个不同的表，可以将表连接至它本身，创建自连接。当想要将列中 的值与同一列中的其他值进行比较时，将表连接至它本身非常有用。

要创建自连接，在FROM子句中列出表两次，并且每次为它指定不同的别名。使用别名 在Projection和 WHERE子句中引用表。如同是两个独立的表一样.（SELECT语句中 的别名在别名和GBase 8s SQL指南：语法中讨论。）

与表之间的连接一样，可以在自连接中使用算术表达式，可以测试空值。可以使用ORDER BY子句来以升序或降序对指定列中的值进行排序。

下列查询查询ship_weight相差五倍或更多并且ship_date不为空的订单。接着，查询按 照ship_date对数据进行排序。

图:查询

SELECT x.order_num, x.ship_weight, x.ship_date,

y.order_num, y.ship_weight, y.ship_date

FROM orders x, orders y

WHERE x.ship_weight >= 5 * y.ship_weight

AND x.ship_date IS NOT NULL

AND y.ship_date IS NOT NULL

ORDER BY x.ship_date;

表1.查询结果

order_num	ship_weight	ship_date	order_num	ship_weight	ship_date
1004	95.80	05/30/1998	1011	10.40	07/03/1998
1004	95.80	05/30/1998	1020	14.00	07/16/1998
1004	95.80	05/30/1998	1022	15.00	07/30/1998
1007	125.90	06/05/1998	1015	20.60	07/16/1998
1007	125.90	06/05/1998	1020	14.00	07/16/1998

如果想要将自连接的结果存储到临时表中，那么将INTO TEMP子句追加到SELECT语 句中，并至少对一组列指定显示标号，以重命名这些列。否则，重复列名将导致错误，并 且不会创建临时表。

下列查询，类似于图1，标记从orders表选择的所有列，并将这些列放置在称为shipping的 临时表中。

图:查询

SELECT x.order_num ordersl, x.po_num purchi,

x.ship_date shipi, y.order_num orders2,
y.po_num purch2, y.ship_date ship2

FROM orders x, orders y