DB2 SQL 与 XQuery 教程，第 3 部分: SQL 连接与联合

核心提示：开始之前关于本系列本教程为您讲解 SQL 的一些基础和高级话题以及 XQuery 的基础知识，并展示如何使用 SQL 查询或 XQuery 语句将常被问起的业务问题表达为数据库查询，DB2 SQL 与 XQuery 教程，第 3 部分: SQL 连接与联合，开发人员和数据库管理员可以使用本教程来提高他们的数据库查询技能

开始之前

关于本系列

本教程为您讲解 SQL 的一些基础和高级话题以及 XQuery 的基础知识，并展示如何使用 SQL 查询或 XQuery 语句将常被问起的业务问题表达为数据库查询。开发人员和数据库管理员可以使用本教程来提高他们的数据库查询技能。Academic Initiative 成员可以使用本教程系列作为他们数据库课程的一部分。

本文中的所有例子都是基于 Aroma，这是一个示例数据库，其中包含了在美国各地的商店中出售的咖啡和茶用品的销售数据。每个例子由以下三部分组成：

以日常用语表达的一个业务问题

以 SQL 或 XQuery 表达的一个或多个例子查询

显示从数据库返回的结果的一个表

本指南是为了让读者学习 SQL 语言和 XQuery 而设计的。和学习任何其它技能一样，重要的是一边学习一边实践。本指南给出的表定义和数据为此提供了便利。

对于使用本指南作为学校课程一部分的学生而言，他们应该向老师学习连接到 Aroma 数据库的操作，并了解本指南的设置与您本地设置的不同之处。

本系列是针对于 DB2 Express-C 9 for UNIX®, Linux® and Windows® （曾用名 Viper）而编写的。

关于本教程

本教程描述组合来自 IBM DB2 数据库中的不同表的数据的两种方式：

连接表

使用 UNION、EXCEPT 和 INTERSECT 操作

本教程的第一部分给出了关于内部连接和外部连接的例子。

第二部分阐释如何使用 UNION、EXCEPT 和 INTERSECT 操作组合来自不同表的数据，这种方法将一个查询表达式的中间结果集与另一个查询表达式的结果集组合起来。

连接到数据库

在使用 SQL 查询或处理数据之前，需要连接到一个数据库。CONNECT 语句将一个数据库连接与一个用户名相关联。

如果您使用本指南作为学校课程的一部分，那么可以向老师询问要连接到的数据库的名称。对于本系列，数据库名为 aromadb。

要连接到 aromadb 数据库，可以在 DB2 命令行处理器中输入以下命令：

　CONNECT　TO　aromadb　USER　userid　USING　password

注意用老师告诉您的用户 ID 和密码替换 "userid" 和 "password"。如果不需要用户 ID 和密码，那么只需使用以下命令：

　CONNECT　TO　aromadb

如果看到下面的消息，则说明您已经建立一个成功的连接：

　Database　Connection　Information　
　Database　server　　　=　DB2/NT　9.0.0　
　SQL　authorization　ID　=　USERID　
　Local　database　alias　=　AROMADB

建立连接后，就可以开始使用数据库了。

表名和模式

什么是模式？

模式用于分组。简单来说，模式就像一个公共图书馆：把书放在不同的区域。 于是，要找历史书，就可以去历史区。类似地，在一个数据库中（类似于图书馆），表是成组地放在不同模式中的。 当一个用户登录到数据库中时，对于所有查询，这个用户的身份即成为默认模式。

例如，如果用户 DBUSER 执行这样一个查询：

　SELECT　...　FROM　sales

，那么系统对该查询的解释是

　SELECT　...　FROM　dbuser.sales

如果数据库中不存在模式 dbuser，那么系统将返回一个错误。

有两种方法可以避免这种错误。一种方法是为每个表使用限定的表名；另一种方法是设置一个不同的默认模式标识符。

使用限定表名

您可以在每个查询的 FROM 子句中显式地列出限定的表名。也就是说，在每个表名之前加上模式名和一个点，例如

　SELECT　...　FROM　aroma.sales　...

使用限定表名有两个明显的优点：

任何用户都可以查看查询，并且知道该查询是对哪个模式发出的。

可以将不同模式中的表包括到同一个查询中（条件是用户拥有访问其中每个模式中的数据的权限），例如：
　SELECT　...　FROM　aroma.sales,　dbuser.customer　...

如果要在一个模式上执行很多查询，但是又不想每次都输入限定表名。那么，可以设置一个新的默认模式。

设置新的默认模式

通过一条简单的命令就可以设置一个新的模式：

　　SET　SCHEMA　<schema_name>;

执行该命令后，系统就使用新的模式名作为默认模式，于是就不必使用限定的表名了。但仍然可以通过使用限定表名将其他模式中的表包括进来，例如：

　SET　SCHEMA　aroma;　
　SELECT　...　FROM　sales,　dbuser.customer　...

但是，用户必须拥有对新模式进行操作的适当权限，否则查询和其他动作将失败。

只有在当前会话期间，这种默认模式设置才有效。如果断开了与数据库的连接，那么，对于接下来的一系列查询，必须再次运行 SET 命令。

使用中的注意事项

本系列中的查询将使用限定表名。

表别名

通过利用表别名也可以节省输入，同时还能提高查询的可读性。表别名通常是一个表的缩写名，在 FROM 子句中指定，并且在本查询的其他地方一直沿用。当一个查询要连接多个表时，常常用到表别名。

例如，下面是一个简单的连接查询：

　SELECT　date,　dollars　
　FROM　aroma.period,　aroma.sales　
　WHERE　　aroma.period.perkey　=　aroma.sales.perkey　
　　　AND　aroma.period.month　=　'JAN'　
　　　AND　aroma.period.year　=　2006;

您可以使用表别名重新编写这个查询。在这里，我们将为 period 表使用别名 "a" ，为 sales 表使用别名 "b"：

　SELECT　date,　dollars　
　FROM　aroma.period　a,　aroma.sales　b　
　WHERE　　a.perkey　=　b.perkey　
　　　AND　a.month　=　'JAN'　
　　　AND　a.year　=　2006;

在 FROM 子句中，每个限定表名后面都跟着一个空格和一个别名。您可以为 FROM 子句中列出的部分表或全部表指定别名，也可以不指定表名。

但是，一旦在 FROM 子句中指定了一个表别名，那么在查询中每次引用那个表时，都必须使用别名。如果尝试在查询中使用限定表名，就会遇到错误消息。

当指定一个别名时，还 必须 小心避免使用模式中已有的表的表名，否则查询就会不正确。

在前面的例子中，假设用户 dbuser 已经登录，并且没有改变默认模式。如果模式中已经有一个名为 "a" 的表，那么系统将查找列 dbuser.a.perkey，而不是用 aroma.period.perkey 替换它。

使用中的注意事项

本指南中的大多数查询将使用表别名。

使用简单连接

问题

在 2005 年举行的类型为 900 的促销活动中，复活节产品在周末的每日销售总额为多少，哪些商店登记了那些销售？

例子查询

　SELECT　prod_name,　store_name,　day,　dollars　
　FROM　aroma.promotion　a,　aroma.product　b,　
　　　　aroma.period　c,　aroma.store　d,　aroma.sales　e　
　WHERE　　　　a.promokey　=　e.promokey　
　　　AND　　b.prodkey　=　e.prodkey　
　　　AND　　b.classkey　=　e.classkey　
　　　AND　　c.perkey　=　e.perkey　
　　　AND　　d.storekey　=　e.storekey　
　　　AND　　　prod_name　LIKE　'Easter%'　
　　　AND　　　day　IN　('SA',　'SU')　
　　　AND　　　promo_type　=　900　
　　　AND　　　year　=　2005;

结果

Prod_Name	Store_Name	Day	Dollars
Easter Sampler Basket	Olympic Coffee Company	SA	150.00

关于该查询

这个业务问题需要连接 Aroma 模式中的五个表：Sales 事实表和它的 Product、Period、Store 和 Promotion 表。要弄清 Aroma 数据库的表布局，请参阅本系列的第 1 部分。

要在查询中连接表，必须对数据库服务器给出明确的指示，告诉它如何执行连接。连接是在 WHERE 子句中通过 5 个简单的条件指定的，这 5 个条件在 5 个主键列上连接 Sales 表。Product 表的主键是由两列组成的，因此它通过两个列与 Sales 表连接：Prodkey 和 Classkey。

使用中的注意事项

任何两个表都可以通过具有可比数据类型的列进行连接；连接并不限于这个例子中用到的主键到外键关系。

使用 ORDER BY 子句

问题

2005 年 11 月份 Instant Coffee 店的 Assam Gold Blend 和 Earl Grey 的销售数据是多少？将每种产品的数据按从高到低排序。

例子查询

　SELECT　prod_name,　store_name,　dollars　
　FROM　aroma.store　a,　aroma.sales　b,　aroma.product　c,　aroma.period　d　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　d.perkey　=　b.perkey　
　　　AND　(prod_name　like　'Assam　Gold%'　OR　prod_name　LIKE　'Earl%')　
　　　AND　store_name　LIKE　'Instant%'　
　　　AND　month　=　'NOV'　
　　　AND　year　=　2005　
　ORDER　BY　prod_name,　dollars　DESC;

结果

Prod_Name	Store_Name	Dollars
Assam Gold Blend	Instant Coffee	96.00
Assam Gold Blend	Instant Coffee	78.00
Assam Gold Blend	Instant Coffee	66.00
Assam Gold Blend	Instant Coffee	58.50
Assam Gold Blend	Instant Coffee	58.50
Assam Gold Blend	Instant Coffee	39.00
Assam Gold Blend	Instant Coffee	39.00
Assam Gold Blend	Instant Coffee	32.50
Earl Grey	Instant Coffee	48.00
Earl Grey	Instant Coffee	45.50
Earl Grey	Instant Coffee	42.00
Earl Grey	Instant Coffee	32.00
Earl Grey	Instant Coffee	24.00
Earl Grey	Instant Coffee	20.00

对结果排序：ORDER BY 子句

可以使用 ORDER BY 子句将查询结果按一个或多个指定列中的值进行排序。默认的排序顺序是升序（ASC）；DESC 关键字规定按指定列的值降序排序，就像下面这样：

　ORDER　BY　prod_name,　3　DESC

ORDER BY 子句的语法

　SELECT　column　name(s)　
　FROM　table　name(s)　
　[WHERE　search_condition]　
　[ORDER　BY　order_list];

order_list	是列的一个列表，数据将按这些列来排序。order_list 中的列不一定要出现在 select_list 中，但是必须存在于 FROM 子句中引用的表中。

关于该查询

这个例子查询检索 2005 年 11 月份 Instant Coffee 店 Assam Gold Blend 和 Earl Grey 的销售数据。该查询按照产品和日销售总额对结果排序。

使用中的注意事项

ORDER BY 子句必须跟在 SELECT 语句中其他子句的后面，并且包括要排序的列。列可以通过它的名称、别名或在 select 列表中的位置（序数）来引用。例如，前面那个查询中的 ORDER BY 子句可以写成这样：

　ORDER　BY　prod_name,　3　DESC

通过在 order_list 中指定不在 column name(s) 中的列，可以按照结果中没有显示的列对数据进行排序。

两个表的连接

对于开始的一些连接查询例子，使用以下示例表：

State 表		Region 表
City	State	City	Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South
Nashville	TN	New Orleans	South

例子查询

　SELECT　*　
　FROM　aroma.state,　aroma.region;

笛卡儿乘积（未指定连接谓词）

City	State	City	Area
Jacksonville	FL	Jacksonville	South
Jacksonville	FL	Miami	South
Jacksonville	FL	New Orleans	South
Miami	FL	Jacksonville	South
Miami	FL	Miami	South
Miami	FL	New Orleans	South
Nashville	TN	Jacksonville	South
Nashville	TN	Miami	South
Nashville	TN	New Orleans	South

注意，查询结果的顺序可能不同。如果没有指定 "ORDER BY" 子句，则系统会以任何可能的顺序返回行。

例子查询

　SELECT　*　
　FROM　aroma.state,　aroma.region　
　WHERE　state.city　=　region.city;

笛卡儿乘积的子集（指定连接谓词）

State:City	State:State	Region:City	Region:Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South

内连接

大多数查询都要连接来自不同表的信息。任何两个表都可以通过具有可比数据类型的列进行连接；连接并不限于主键到外键的关系。

笛卡儿乘积

当一个查询的 FROM 子句中引用了两个或更多表时，数据库服务器就要连接这些表。如果无论是 FROM 子句还是 WHERE 子句都没有指定连接谓词，那么服务器将计算包含 m * n 行的笛卡儿乘积，其中 m 是第一个表中的行数，n 是第二个表中的行数。这个乘积是通过将第一个表中的一行与第二个表中的一行进行连接所能得到的所有可能组合的集合。

笛卡儿乘积的子集

如果表是通过具有可比数据类型的列进行连接的，那么服务器将计算笛卡儿乘积的一个子集。这个子集只包含那些在连接列上具有匹配值的行。在整个查询当中，这个子集充当的是一个派生的表，它可以与其他表或其他查询表达式的结果进行连接。

关于本查询

State 和 Region 表都包含 City 列，这个列在 WHERE 子句中被指定为连接列。因此，在笛卡儿乘积当中，只有那些具有匹配的 City 键的行会显示在结果中。在这个查询例子中，结果表只包含两行，而这两个表的整个笛卡儿乘积包含 9 行。

连接表的另一种方法

问题

显示以大写字母 "A" 开头的所有产品名称及其相关类别的一个列表。按产品名称的字母顺序对列表进行排序。

例子查询 1

　SELECT　prod_name,　class_type　
　FROM　　aroma.product　t,　aroma.class　c　
　WHERE　　t.classkey　=　c.classkey　
　　　AND　prod_name　LIKE　'A%'　
　ORDER　BY　prod_name;

例子查询 2

　SELECT　prod_name,　class_type　
　FROM　　　aroma.product　t　
　　　JOIN　aroma.class　c　ON　t.classkey　=　c.classkey　
　WHERE　prod_name　LIKE　'A%'　
　ORDER　BY　prod_name;

不同查询，相同结果

PROD_NAME	CLASS_TYPE
Aroma Roma	Bulk_beans
Aroma Roma	Pkg_coffee
...

FROM 子句中的连接

您可以像前面例子中展示的那样，使用 ON 语法在 FROM 子句中显式地连接表。

关于本查询

这个查询在具有相同名称的列上连接 Product 和 Class 表。因此，使用 ON 子句的查询与在 WHERE 子句中包含连接的查询产生的值一样。

使用中的注意事项

在 FROM 子句中使用 ON 语法连接表的查询与在 WHERE 子句中连接表的查询在性能上没有差别。具体使用哪种方法取决于创建查询的人的个人偏好。

有些人喜欢 FROM 子句约束语法，因为这种方法清楚地将用于连接表的约束与用于限制结果集的约束区分开来。而另外一些人则喜欢在 WHERE 子句中列出所有约束。

但是，前面显示的例子的确是根据主键/外键关系连接表的。这通常是连接表的最有效的方式。

自连接

查询中所连接的表不一定要是不同的表；您也可以将任何表与它自己连接，只要用不同的名称引用那个表就行。自连接对于发现同一个表中不同列之间的关系非常有用。

问题

在 Product 表中，哪些产品具有相同的名称，但是包装类型却不相同？

例子查询

　SELECT　a.prod_name　AS　products,　a.pkg_type　
　FROM　aroma.product　a,　aroma.product　b　
　WHERE　　a.prod_name　=　b.prod_name　
　　　AND　a.pkg_type　<>　b.pkg_type　
　ORDER　BY　products,　a.pkg_type;

结果

Product	Pkg_Type
Aroma Roma	No pkg
Aroma Roma	One-pound bag
Assam Gold Blend	No pkg
Assam Gold Blend	Qtr-pound bag
Assam Grade A	No pkg
Assam Grade A	Qtr-pound bag
Breakfast Blend	No pkg
Breakfast Blend	Qtr-pound bag
Cafe Au Lait	No pkg
Cafe Au Lait	One-pound bag
Colombiano	No pkg
Colombiano	One-pound bag
Darjeeling Number 1	No pkg
Darjeeling Number 1	Qtr-pound bag
Darjeeling Special	No pkg
Darjeeling Special	Qtr-pound bag
Demitasse Ms	No pkg
Demitasse Ms	One-pound bag
Earl Grey	No pkg
Earl Grey	Qtr-pound bag
English Breakfast	No pkg
English Breakfast	Qtr-pound bag
Expresso XO	No pkg
Expresso XO	One-pound bag
Gold Tips	No pkg
Gold Tips	Qtr-pound bag
Irish Breakfast	No pkg
Irish Breakfast	Qtr-pound bag
...

关于本查询

这个查询在 Prod_Name 列上将 Product 表与它自己相连接，其中使用别名 a 和 b 来区分表引用：

　FROM　aroma.product　a,　aroma.product　b

自连接将 Product 表 a 与 Product 表 b 进行比较，以发现产品名称相同而包装类型不同的那些行：

　　WHERE　a.prod_name　=　b.prod_name　
　　　AND　a.pkg_type　<>　b.pkg_type

结果集包含一系列具有相同名称的产品和这些产品对应的不同的包装类型。

两个表的外连接

State 表		Region 表
City	State	City	Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South
Nashville	TN	New Orleans	South

上面这两个表用于演示左外连接、右外连接和全外连接。为简化问题，这些例子中没有包括模式 aroma。

例子查询（左外连接）

　SELECT　*　
　FROM　state　LEFT　OUTER　JOIN　region　
　　　ON　state.city　=　region.city;

结果

State:City	State:State	Region:City	Region:Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South
Nashville	TN	[null]	[null]

例子查询（右外连接）

　SELECT　*　
　FROM　state　RIGHT　OUTER　JOIN　region　
　　　ON　state.city　=　region.city;

结果

State:City	State:State	Region:City	Region:Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South
[null]	[null]	New Orleans	South

例子查询（全外连接）

　SELECT　*　
　FROM　state　FULL　OUTER　JOIN　region　
　　　ON　state.city　=　region.city;

结果

State:City	State:State	Region:City	Region:Area
Jacksonville	FL	Jacksonville	South
Miami	FL	Miami	South
Nashville	TN	[null]	[null]
[null]	[null]	New Orleans	South

要点：这些例子使用本教程一开始介绍过的那些表。

外连接

在大多数情况下，表是根据只发现具有匹配值的行的搜索条件进行连接的；这种类型的连接称为内连接。然而，在某些情况下，决策支持分析需要外连接，这种连接既检索匹配的行，也检索不匹配的行，它表达的是一种大于或小于之类的关系。

外连接操作返回内连接所返回的所有行，另外还加上一个表中与另一个表任何行都不匹配的所有行。外连接的类型有左外连接、右外连接和全外连接，这取决于是返回左表中的所有行，还是返回右表中的所有行，又或者是返回两个表中的所有行。FROM 子句中列出的第一个表称为左表，第二个表称为右表。对于所有这三种类型的外连接，都使用 NULL 来表示行中不匹配的空列。

语法

如前面的例子所示，可以通过使用 OUTER JOIN 关键字和 ON 子句，在 FROM 子句中指定两个表之间的外连接：

　　FROM　table_1　LEFT|RIGHT|FULL　OUTER　JOIN　table_2　
　　　ON　table_1.column　=　table_2.column

关于该查询

左外连接的结果包含 State 表中的每一行以及 Region 表中所有匹配的行。只在 Region 表中出现的行不显示在结果中。

右外连接的结果包含 Region 表中的每一行以及 State 表中所有匹配的行。只在 State 表中出现的行不显示在结果中。

全外连接的结果包含两个表中各自特有的行以及两个表中都存在的行。

使用 SUM、AVG、MAX、MIN、COUNT 集合函数

问题

2005 年 Lotta Latte 在洛杉矶的销售总额是多少？2005 年的日均销售额、最高日销售额和最低日销售额分别是多少？在计算上述数据时，考虑了多少天的日销售总额？

例子查询

　SELECT　SUM(dollars)　as　Dol_Sales,　AVG(dollars)　as　Avg_Sales,　
　MAX(dollars)　as　Max_Sales,　MIN(dollars)　as　Min_Sales,　COUNT(*)　as　Qty　
　FROM　aroma.store　a,　aroma.sales　b,　aroma.product　c,　aroma.period　d　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　d.perkey　=　b.perkey　
　　　AND　prod_name　LIKE　'Lotta　Latte%'　
　　　AND　year　=　2005　
　　　AND　city　LIKE　'Los　Ang%';

结果

Dol_Sales	Avg_Sales	Max_Sales	Min_Sales	Qty
13706.50	171.33125000	376.00	39.00	80

使用集合函数

集合函数对值的集合进行操作。例如，SUM(dollars) 计算一个结果集中返回的金额的总数，AVG(dollars) 返回平均数。下表中列出的 SQL 集合函数可以在 select 列表中出现一次或多次。

函数	描述
SUM(表达式)	计算表达式中所有值的总和
SUM(DISTINCT 表达式)	计算表达式中不同值的总和
AVG(表达式)	计算表达式中所有值的平均值
AVG(DISTINCT 表达式)	计算表达式中不同值的平均值
MAX(表达式)	判断表达式中的最大值
MIN(表达式)	判断表达式中的最小值
COUNT(*)	计算返回的行数
COUNT(表达式)	计算表达式中非空值的数量
COUNT(DISTINCT 表达式)	计算表达式中不同的非空值的数量

您可以用任何列名或数字表达式替换表达式。除了 COUNT(*) 外，在计算返回的聚合值时，每个函数都忽略 NULL 值。

关于该查询

这个例子查询检索 2005 年 Lotta Latte 在洛杉矶的销售数据。结果集还包括这一年的日均销售额、最高日销售额和最低日销售额，以及计算这些数据时所考虑的日销售额的数量。

使用中的注意事项

如果结果集既要包含单独的值，又要包含聚合值，那么查询必须包含一个 GROUP BY 子句。关于 GROUP BY 子句，请参阅下一节。

使用 GROUP BY 子句对行分组

问题

2004 年咖啡杯在每个地区的年销售总额是多少？在这一时期的日均销售额、最高日销售额和最低日销售额是多少？请按地区列出结果。

例子查询

　　SELECT　district　AS　district_city,　SUM(dollars)　AS　dol_sales,　
　　　AVG(dollars)　AS　avg_sales,　MAX(dollars)　AS　max_sales,　
　　　MIN(dollars)　AS　min_sales　
　FROM　aroma.store　a,　aroma.sales　b,　aroma.product　c,　
　　　　aroma.period　d,　aroma.market　e　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　d.perkey　=　b.perkey　
　　　AND　e.mktkey　=　a.mktkey　
　　　AND　prod_name　LIKE　'%Mug%'　
　　　AND　year　=　2004　
　GROUP　BY　district　
　ORDER　BY　dol_sales　DESC;

结果

District_City	Dol_Sales	Avg_Sales	Max_Sales	Min_Sales
Atlanta	1378.30	35.34102564	98.55	4.00
Los Angeles	711.60	30.93913043	98.55	9.95
San Francisco	410.45	25.65312500	54.75	5.00

对行分组：GROUP BY 子句

集合函数对一个结果表中的所有行进行操作，或者对用 GROUP BY 子句定义的行的分组进行操作。例如，可以按每个市场对销售数据进行分组，并计算各自的总和、最大值和最小值。

GROUP BY 子句的语法

　SELECT　column　name(s)　
　FROM　table　name(s)　
　[WHERE　search_condition]　
　[GROUP　BY　group_list]　
　[ORDER　BY　order_list];

group_list	列名的一个列表，这些列要么是 column name(s) 中的列，要么是 FROM 子句列出的表中的列。column name(s) 中所有非聚合列都必须出现在 group_list 中。

关于该查询

这个例子查询检索咖啡杯在 2004 年的年度销售总额（咖啡杯只在三个地区销售），按照从高到低的顺序对数字排序。从概念上讲，服务器按下面的步骤来处理这个查询：

从 FROM 子句中指定的表中检索所有行，连接来自不同表的行，并生成一个中间结果表。

保留中间结果表中满足 WHERE 子句指定的搜索条件的所有行。

将结果表中的行划分到 GROUP BY 子句指定的分组中。

对于整个结果表，处理所有指定分组上的集合函数。

根据 ORDER BY 子句对结果排序。

只返回 select 列表中指定的那些列。

使用中的注意事项

GROUP BY 子句按实际列名引用 select 列表中的项。在 GROUP BY 子句中不能使用列别名或位置整数。

使用 GROUP BY 子句产生多个分组

问题

在 2004 年和 2005 年每个城市的销售总额是多少？按照年份、区域和地区列出城市名称。

例子查询

　SELECT　year,　region,　district,　city,　SUM(dollars)　AS　sales　
　FROM　aroma.store　a,　aroma.sales　b,　aroma.product　c,　
　　　　aroma.period　d,　aroma.market　e　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　d.perkey　=　b.perkey　
　　　AND　e.mktkey　=　a.mktkey　
　　　AND　year　IN　(2004,　2005)　
　GROUP　BY　year,　region,　district,　city　
　ORDER　BY　year,　region,　district,　city;

结果

Year	Region	District	City	Sales
2004	Central	Chicago	Chicago	133462.75
2004	Central	Chicago	Detroit	135023.50
2004	Central	Minneapolis	Milwaukee	172321.50
2004	North	Boston	Boston	184647.50
2004	North	Boston	Hartford	69196.25
2004	North	New York	New York	181735.00
2004	North	New York	Philadelphia	172395.75
2004	South	Atlanta	Atlanta	230346.45
2004	South	Atlanta	Miami	220519.75
2004	South	New Orleans	Houston	183853.75
2004	South	New Orleans	New Orleans	193052.25
2004	West	Los Angeles	Los Angeles	219397.20
2004	West	Los Angeles	Phoenix	192605.25
2004	West	San Francisco	Cupertino	180088.75
2004	West	San Francisco	Los Gatos	176992.75
2004	West	San Francisco	San Jose	395330.25
2005	Central	Chicago	Chicago	131263.00
2005	Central	Chicago	Detroit	136903.25
2005	Central	Minneapolis	Milwaukee	173844.25
2005	Central	Minneapolis	Minneapolis	132125.75
2005	North	Boston	Boston	189761.00
2005	North	Boston	Hartford	135879.50
2005	North	New York	New York	171749.75
2005	North	New York	Philadelphia	171759.50
2005	South	Atlanta	Atlanta	229615.05
2005	South	Atlanta	Miami	234458.90
2005	South	New Orleans	Houston	186394.25
2005	South	New Orleans	New Orleans	190441.75
2005	West	Los Angeles	Los Angeles	228433.00
2005	West	Los Angeles	Phoenix	197044.50
2005	West	San Francisco	Cupertino	196439.75
2005	West	San Francisco	Los Gatos	175048.75
2005	West	San Francisco	San Jose	398829.10

嵌套分组的结果：GROUP BY 子句

当 GROUP BY 子句中出现几个列名时，结果表就被划分到分组中的子分组中。例如，如果在 GROUP BY 子句中指定 year、region 和 district 这几个列名，那么返回的数字首先按年划分，每一年的数字又按区域划分，每个区域的数字又按地区划分。

在前面的例子中，结果是按年、区域、地区和城市分组的，粒度更细一些。如果省略了 city 这一列，那么结果集就会更短，粒度就更粗一些。

　SELECT　year,　region,　district,　SUM(dollars)　AS　sales　
　FROM　aroma.store　a,　aroma.sales　b,aroma.product　c,　
　　　　aroma.period　d,　aroma.market　e　
　WHERE　　a.storekey　=　b.storekey　
　　　AND　c.prodkey　=　b.prodkey　
　　　AND　c.classkey　=　b.classkey　
　　　AND　d.perkey　=　b.perkey　
　　　AND　e.mktkey　=　a.mktkey　
　GROUP　BY　year,　region,　district　
　ORDER　BY　year,　region,　district;

结果

Year	Region	District	Sales
2004	Central	Chicago	268486.25
2004	Central	Minneapolis	172321.50
2004	North	Boston	253843.75
2004	North	New York	354130.75
2004	South	Atlanta	450866.20
2004	South	New Orleans	376906.00
2004	West	Los Angeles	412002.45
2004	West	San Francisco	752411.75
2005	Central	Chicago	268166.25
2005	Central	Minneapolis	305970.00
2005	North	Boston	325640.50
2005	North	New York	343509.25
2005	South	Atlanta	464073.95
2005	South	New Orleans	376836.00
2005	West	Los Angeles	425477.50
2005	West	San Francisco	770317.60
2006	Central	Chicago	64190.00
2006	Central	Minneapolis	76417.50
2006	North	Boston	78494.25
2006	North	New York	91840.25
2006	South	Atlanta	106912.20
2006	South	New Orleans	93156.75
2006	West	Los Angeles	103876.15
2006	West	San Francisco	192503.30

GROUP BY 子句的语法

　SELECT　column　name(s)　
　FROM　table　name(s)　
　[WHERE　search_condition]　
　[GROUP　BY　group_list]　
　[ORDER　BY　order_list];

group_list	这是列名的一个列表，这些列要么在 column name(s) 中，要么在 FROM 子句列出的表中。column name(s) 中所有非聚合列必须出现在 group_list 中。

关于该查询

这个例子查询检索 2004 年和 2005 年每个城市所有产品的年度销售总额。销售数字按年、区域、地区和城市分组，并且也按这些列来排序。

要点: 这个查询中引用的城市是每个商店的城市位置，即 Store 表中定义的城市，而不是 Market 表中定义的表示城市的 hq_cities。

OR 与 UNION

问题

在 2005 年的第 52 周，所有归类为 "Medium"（中型）的 Aroma 商店的销售总额是多少？在同一时期内 "Large"（大型）商店的销售总额是多少？

使用 OR 条件的例子查询

　SELECT　store_name　AS　store,　store_type　AS　size,　
　　　　　state,　SUM(dollars)　AS　sales　
　FROM　　　aroma.period　p　
　　　JOIN　aroma.sales　s　ON　p.perkey　=　s.perkey　
　　　JOIN　aroma.store　r　ON　r.storekey　=　s.storekey　
　WHERE　　(store_type　=　'Medium'　
　　　OR　store_type　=　'Large')　
　　　AND　year　=　2005　
　　　AND　week　=　52　
　GROUP　BY　store_name,　store_type,　state　
　ORDER　BY　2,　1;

使用 UNION 的例子查询

　SELECT　store_name　AS　store,　store_type　AS　size,　
　　　　　state,　SUM(dollars)　AS　sales　
　FROM　　　aroma.period　p　
　　　JOIN　aroma.sales　s　ON　p.perkey　=　s.perkey　
　　　JOIN　aroma.store　r　ON　r.storekey　=　s.storekey　
　WHERE　　store_type　=　'Medium'　
　　　AND　year　=　2005　
　　　AND　week　=　52　
　GROUP　BY　store_name,　store_type,　state　
　
　UNION　
　
　SELECT　store_name　AS　store,　store_type　AS　size,　
　　　　　state,　SUM(dollars)　
　FROM　　　aroma.period　p　
　　　JOIN　aroma.sales　s　ON　p.perkey　=　s.perkey　
　　　JOIN　aroma.store　r　ON　r.storekey　=　s.storekey　
　WHERE　　store_type　=　'Large'　
　　　AND　year　=　2005　
　　　AND　week　=　52　
　GROUP　BY　store_name,　store_type,　state　
　ORDER　BY　2,　1;

不同查询，相同结果

Store	Size	State	Sales
Beaches Brew	Large	CA	2908.80
Miami Espresso	Large	FL	4582.00
Olympic Coffee Company	Large	GA	3732.50
San Jose Roasting Company	Large	CA	3933.15
Beans of Boston	Medium	MA	3772.75
Cupertino Coffee Supply	Medium	CA	2893.00
Java Judy's	Medium	AZ	3011.25
Moulin Rouge Roasting	Medium	LA	3972.00
Texas Teahouse	Medium	TX	3382.75

组合结果集：UNION

可以使用 UNION、EXCEPT 和 INTERSECT 操作符将两个或更多查询表达式的输出组合成一个行和列的集合。服务器首先单独计算每个查询表达式，然后组合输出，最后的输出中显示第一个表达式中的列标题。如果没有指定 ALL 关键字，服务器将消除重复的行。

两个集合的并集

UNION、INTERSECT 和 EXCEPT

　query_expression　UNION　|　INTERSECT　|　EXCEPT　[ALL]　query_expression　
　[ORDER　BY　order_list];

query_expression	任何连接或非连接查询表达式，请参考 SQL Reference Guide 中的定义。

如果使用 ORDER BY 子句，那么必须引用第一个查询表达式的 select 列表中的列名。如果使用第二个查询表达式中的列名，就会导致错误。

关于该查询

相同的业务问题既可以通过在一个 SELECT 语句中指定 OR 条件来解决，也可以通过用一个 UNION 操作符组合两个查询表达式来解决。

在这个简单的例子中，使用 OR 连接要更容易一些，但是在某些情况下，UNION 操作可以提高查询性能。例如，假设一个查询需要访问两个很大的事实表中的数据。与使用 UNION 操作组合两个查询表达式的结果相比，使用单个查询的方法需要外连接操作，而外连接操作可能需要进行更多的处理。

ORDER BY 子句引用第一个查询表达式的 select 列表中定义的列位置，而不是列名：

　ORDER　BY　1,　2

使用中的注意事项

UNION、INTERSECT 和 EXCEPT 查询都必须对称；也就是说，UNION 操作符两侧的 select 列表中的列的数量和顺序必须一致。对应的列可以有不同的列名，但是必须有相同的或可比的数据类型。

一条语句中可以使用多个 UNION、INTERSECT 和 EXCEPT 操作符；如果没有用括号指定优先次序，那么这些操作将按照从左到右的顺序计算。

当连接有相同数据类型，但是列名不同的表时，UNION 非常有用。在那种情况下，结果集的默认行为是使用第一个表中的列名。

UNION 与 UNION ALL

UNION 有一个内部的 DISTINCT 语句。因此，重复的行将被消除，每个值只显示一个实例。很多业务问题都可以从这种行为当中获益。

问题

提供设有商店或总部或者两者都有的所有城市的一个列表。每个城市只列一次。

例子查询（UNION）

　SELECT　city　
　FROM　aroma.store　
　UNION　
　SELECT　hq_city　AS　city　
　FROM　aroma.market;

结果

CITY

Atlanta

Boston

Chicago

...

Phoenix

San Francisco

San Jose

然而，在另外一些情况下，您可能希望被联合对象中出现多次的值在结果中也重复出现。例如，您可能想计算所有情况中出现的对象的总数。

问题

提供设有商店或总部或者两者都有的所有城市的列表。对于每一次在某个城市发现商店或总部的情况，都列出那个城市。

例子查询（UNION ALL）

　SELECT　city　
　FROM　aroma.store　
　UNION　ALL　
　SELECT　hq_city　AS　city　
　FROM　aroma.market　
　ORDER　BY　city;

结果

CITY

Atlanta

Atlanta

Boston

Boston

Chicago

Chicago

Cupertino

...

San Jose

San Jose

San Jose

在这个例子中，Atlanta、Boston、Chicago 和 San Jose 这几个城市都有多处设施，而 Cupertino 只有一处设施。

INTERSECT 操作

问题

2006 年在圣何塞的促销活动中出售的散装茶产品当中，有哪些产品在 2005 年新奥尔良的促销活动中也曾出售过？那些产品有哪些促销活动？

例子查询

　SELECT　prod_name　AS　tea_name,　promo_desc　
　FROM　　　aroma.class　c　
　　　JOIN　aroma.product　d　ON　c.classkey　=　d.classkey　
　　　JOIN　aroma.sales　s　ON　d.prodkey　=　s.prodkey　
　　　　　AND　d.classkey　=　s.classkey　
　　　JOIN　aroma.store　t　ON　t.storekey　=　s.storekey　
　　　JOIN　aroma.period　p　ON　p.perkey　=　s.perkey　
　　　JOIN　aroma.promotion　n　ON　n.promokey　=　s.promokey　
　WHERE　　city　=　'San　Jose'　
　　　AND　year　=　2006　
　　　AND　class_desc　LIKE　'Bulk　tea%'　
　　
　INTERSECT　
　　
　SELECT　prod_name　AS　tea_name,　promo_desc　
　FROM　　　aroma.class　c　
　　　JOIN　aroma.product　d　ON　c.classkey　=　d.classkey　
　　　JOIN　aroma.sales　s　ON　d.prodkey　=　s.prodkey　
　　　　　AND　d.classkey　=　s.classkey　
　　　JOIN　aroma.store　t　ON　t.storekey　=　s.storekey　
　　　JOIN　aroma.period　p　ON　p.perkey　=　s.perkey　
　　　JOIN　aroma.promotion　n　ON　n.promokey　=　s.promokey　
　WHERE　　city　=　'New　Orleans'　
　　　AND　year　=　2005　
　　　AND　class_desc　LIKE　'Bulk　tea%'　
　　　AND　promo_desc　NOT　LIKE　'No　promo%'　
　ORDER　BY　promo_desc;

结果

Tea_Name	Promo_Desc
Irish Breakfast	Aroma catalog coupon
Special Tips	Aroma catalog coupon
Darjeeling Special	Store display
Darjeeling Special	Temporary price reduction
Gold Tips	Temporary price reduction

发现相同的行：INTERSECT

可以使用 INTERSECT 操作符来返回那些在两个或更多查询表达式返回的结果中都出现的行。

两个集合的交集

关于该查询

这个例子查询发现两个查询表达式的交集，其中一个查询表达式返回 2006 年圣何塞的促销活动中出售的散装茶产品的列表，另一个查询表达式返回 2005 年新奥尔良的类似的列表。INTERSECT 操作符排除没有在两个初步结果集中同时出现的所有行。

EXCEPT 操作符

问题

在 Market 表中没有被定义为 HQ 的加利福尼亚州的各大城市中的商店的 2005 年度总收益是多少？

例子查询

　SELECT　x.city,　store_name,　SUM(dollars)　AS　sales_05　
　FROM　
　　　(SELECT　city　
　　　　FROM　aroma.store　
　　　　WHERE　state='CA'　
　　
　　　　EXCEPT　
　　
　　　　SELECT　hq_city　
　　　　FROM　aroma.market　
　　　　WHERE　hq_state　=　'CA')　AS　x(city)　
　　
　　　　JOIN　aroma.store　t　ON　x.city　=　t.city　
　　　　JOIN　aroma.sales　s　ON　s.storekey　=　t.storekey　
　　　　JOIN　aroma.period　p　ON　p.perkey　=　s.perkey　
　WHERE　year　=　2005　
　GROUP　BY　x.city,　store_name　
　ORDER　BY　3　DESC;

结果

City	Store_Name	Sales_05
Cupertino	Cupertino Coffee Supply	196,439.75
Los Gatos	Roasters, Los Gatos	175,048.75

EXCEPT：发现两个结果集中不同的行

EXCEPT 操作符发现两个查询表达式的结果中的不同的行。例如，EXCEPT 操作可以比较在两个商店出售的产品的列表，排除两个商店都出售的产品，保留第一个查询表达式中指定的商店单独出售的那些产品。

两个集合的差

关于该查询

在这个例子查询中，EXCEPT 操作符的作用是选择在 Store 表的 City 列中能找到而在 Market 表的 Hq_City 列中不能找到的那些城市。

这个查询使用 FROM 子句中的一个子查询来产生一个派生的城市表，这个表可以与 Sales、Store 和 Period 表连接。这个由子查询产生的表有一个关联的名称和一个列名：x(city)。

这个派生的表可以在 City 列上与 Store 表连接。

使用中的注意事项

为测试 EXCEPT 操作的结果，可以单独运行这个例子中的子查询：

　(SELECT　city　
　　FROM　aroma.store　
　　WHERE　state　=　'CA'　
　　EXCEPT　
　　SELECT　hq_city　as　city　
　　FROM　aroma.market　
　　WHERE　hq_state　=　'CA');

结果

City

Cupertino

Los Gatos

要获得关于子查询的更多例子，请参考本系列的第 5 部分。

结束语

结束语

本教程描述了：

如何连接表。

如何使用 UNION、INTERSECT 和 EXCEPT 表达式组合两个独立查询表达式的结果。

本教程还谈到了以下话题：

表名和模式

ORDER BY 子句

SUM、AVG、MAX、MIN、COUNT 等集合函数，以及

GROUP BY 子句

本文示例源代码或素材下载