DB2 SQL 与 XQuery 教程，第 3 部分: SQL 连接与联合
2009-11-28 00:00:00 来源：WEB开发网开始之前
关于本系列
本教程为您讲解 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 子句
本文示例源代码或素材下载
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
中查找“DB2 SQL 与 XQuery 教程，第 3 部分: SQL 连接与联合”更多相关内容
中查找“DB2 SQL 与 XQuery 教程，第 3 部分: SQL 连接与联合”更多相关内容更多精彩
赞助商链接
