DB2 SQL 与 XQuery 教程，第 5 部分: 数据比较

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

开始之前

关于本系列

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

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

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

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

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

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

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

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

关于本教程

本教程讨论用于比较 IBM DB2 数据库中的数据的查询。本教程首先提出查询编写者遇到的问题：如何使用 SQL 返回一个电子表格或 “交叉表（cross-tab）”，而不是难于阅读的标准的、垂直排序的结果集。这个问题是通过使用 CASE 表达式或子查询来解决的。

首先阐述的是 CASE 方法，这是比较几组类似值的一种简洁的方法。接着，我们会给出一些关于 FROM 子句和 select 列表子查询的例子。这些子查询能够比较来自不同分组的数据，还能够包括对比较值的计算，例如占一定时期的销售额的百分比，这些都是它们的附加价值。

本教程描述在 WHERE 子句中作为条件的子查询，这些子查询对于较简单的比较查询很有用。最后一节还描述了 ALL、EXISTS 和 SOME 或 ANY 谓词，这些谓词可用于表达子查询结果上的条件。

连接到数据库

在使用 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

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

用 SQL 比较数据

问题

如何对 2004 年西部各商店有包装的咖啡产品的销售情况进行比较？

例子查询

SELECT　store_name,　prod_name,　SUM(dollars)　AS　sales　
　FROM　aroma.market　a,　
　　　aroma.store　b,　
　　　aroma.period　c,　
　　　aroma.product　d,　
　　　aroma.class　e,　
　　　aroma.sales　f　
　WHERE　　a.mktkey　=　b.mktkey　
　　　AND　b.storekey　=　f.storekey　
　　　AND　c.perkey　=　f.perkey　
　　　AND　d.classkey　=　e.classkey　
　　　AND　d.classkey　=　f.classkey　
　　　AND　d.prodkey　=　f.prodkey　
　　　AND　region　like　'West%'　
　　　AND　year　=　2004　
　　　AND　class_type　=　'Pkg_coffee'　
　GROUP　BY　store_name,　prod_name　
　ORDER　BY　store_name,　prod_name;

结果

STORE_NAME	PROD_NAME	SALES
Beaches Brew	Aroma Roma	3483.50
Beaches Brew	Cafe Au Lait	3129.50
Beaches Brew	Colombiano	2298.25
Beaches Brew	Demitasse Ms	4529.25
Beaches Brew	Expresso XO	4132.75
Beaches Brew	La Antigua	4219.75
Beaches Brew	Lotta Latte	3468.00
Beaches Brew	NA Lite	4771.00
Beaches Brew	Veracruzano	4443.00
Beaches Brew	Xalapa Lapa	4304.00
Cupertino Coffee Supply	Aroma Roma	4491.00
Cupertino Coffee Supply	Cafe Au Lait	4375.50
Cupertino Coffee Supply	Colombiano	2653.50
Cupertino Coffee Supply	Demitasse Ms	3936.50
Cupertino Coffee Supply	Expresso XO	4689.25
Cupertino Coffee Supply	La Antigua	2932.00
Cupertino Coffee Supply	Lotta Latte	5146.00
Cupertino Coffee Supply	NA Lite	4026.00
Cupertino Coffee Supply	Veracruzano	3285.00
Cupertino Coffee Supply	Xalapa Lapa	5784.00
Instant Coffee	Aroma Roma	3485.25
Instant Coffee	Cafe Au Lait	3599.50
Instant Coffee	Colombiano	3321.75
Instant Coffee	Demitasse Ms	5422.25
Instant Coffee	Expresso XO	2851.00
Instant Coffee	La Antigua	2937.25
Instant Coffee	Lotta Latte	4783.50
Instant Coffee	NA Lite	3740.00
Instant Coffee	Veracruzano	4712.00
Instant Coffee	Xalapa Lapa	3698.00
...

一个简单的比较查询

您可以使用一个简单的 SELECT 语句列出特定商店中一组产品的销售额，但是结果表的格式不便于比较。例如，前部分的结果集显示，La Antigua 咖啡在西部的某些商店有售，但是这些数字难于分离出来。

如果将这些数据转化为电子表格之类的格式，那么这种数据比较起来就容易得多。产生电子表格或 “交叉表” 报表的方法有两种：一种方法是使用 CASE 表达式，另一种方法是使用子查询。本教程中下面的例子将阐释编写比较查询的这两种方法。

关于该查询

这个例子查询返回 2004 年西部各商店在有包装的咖啡产品方面的销售数据，但是输出数据的格式不便于按产品或按商店对数据进行比较。

使用 CASE 表达式

问题

2004 年西部各商店在有包装的咖啡产品方面的销售情况的比较如何？

例子查询

SELECT　prod_name,　
　　　SUM(CASE　WHEN　store_name　=　'Beaches　Brew'　
　　　then　dollars　else　0　end)　AS　Beaches,　
　　　SUM(CASE　WHEN　store_name　=　'Cupertino　Coffee　Supply'　
　　　then　dollars　else　0　end)　AS　Cupertino,　
　　　SUM(CASE　WHEN　store_name　=　'Roasters,　Los　Gatos'　
　　　then　dollars　else　0　end)　AS　RoastLG,　
　　　SUM(CASE　WHEN　store_name　=　'San　Jose　Roasting　Company'　
　　　then　dollars　else　0　end)　AS　SJRoastCo,　
　　　SUM(CASE　WHEN　store_name　=　'Java　Judy''s'　
　　　then　dollars　else　0　end)　AS　JavaJudy,　
　　　SUM(CASE　WHEN　store_name　=　'Instant　Coffee'　
　　　then　dollars　else　0　end)　AS　Instant　
　FROM　aroma.market　a,　
　　　aroma.store　b,　
　　　aroma.period　c,　
　　　aroma.product　d,　
　　　aroma.class　e,　
　　　aroma.sales　f　
　WHERE　　a.mktkey　=　b.mktkey　
　　　AND　b.storekey　=　f.storekey　
　　　AND　c.perkey　=　f.perkey　
　　　AND　d.classkey　=　e.classkey　
　　　AND　d.classkey　=　f.classkey　
　　　AND　d.prodkey　=　f.prodkey　
　　　AND　region　LIKE　'West%'　
　　　AND　year　=　2004　
　　　AND　class_type　=　'Pkg_coffee'　
　GROUP　BY　prod_name　
　ORDER　BY　prod_name;

结果

PROD_NAME	BEACHES	CUPERTINO	ROASTLG	SJROASTCO	JAVAJUDY	INSTANT
Aroma Roma	3483.50	4491.00	4602.00	4399.25	3748.25	3485.25
Cafe Au Lait	3129.50	4375.50	4199.00	3620.00	4864.50	3599.50
Colombiano	2298.25	2653.50	4205.00	3530.75	3509.00	3321.75
Demitasse Ms	4529.25	3936.50	4347.75	5699.00	6395.25	5422.25
Expresso XO	4132.75	4689.25	4234.50	3811.00	5012.25	2851.00
La Antigua	4219.75	2932.00	3447.50	4323.00	2410.25	2937.25
Lotta Latte	3468.00	5146.00	4469.50	5103.50	4003.00	4783.50
NA Lite	4771.00	4026.00	3250.00	2736.00	4791.00	3740.00
Veracruzano	4443.00	3285.00	4467.00	3856.00	4510.00	4712.00
Xalapa Lapa	4304.00	5784.00	3906.00	3645.00	3182.00	3698.00

比较数据的一种方案：CASE 表达式

有一种以易于阅读的电子表格格式显示比较的值的简洁而有效的方法，这种方法就是在 select 列表中使用 CASE 表达式。每个 CASE 操作计算一个特定的表达式，并根据是否满足某种条件而提供一个不同的值。

CASE 语法

通常，可以为整个域指定约束，限制主查询或外部查询的 WHERE 子句中产生的结果，以此来构造一个 CASE 比较查询。然后，在 select 列表中用 CASE 表达式将结果拆分到子集中：

CASE　WHEN　search_condition　THEN　result1　ELSE　result2　
　END　AS　col_alias

search_condition	值为 true 或 false 的一个逻辑条件
result1	当 search_condition 为 true 时所用的值
result2	当 search_condition 为 false 时所用的值

重要：CASE 表达式可以有以下两种形式之一：简单形式或搜索形式。这个例子使用搜索形式。

关于该查询

这个查询解决本教程中前一个查询所回答的同一个业务问题。但是，这个查询使用 CASE 表达式在结果集中产生六个不同的包含聚合的 dollar 值的列，每一列对应一家商店。

在 FROM 子句中使用子查询

问题

如何将 2004 年 1 月份圣何塞的产品销售情况与该城在同一年全年的产品销售情况进行比较？

例子查询

SELECT　sales1.product,　jan_04_sales,　total_04_sales　
　FROM　
　　　(SELECT　prod_name,　SUM(dollars)　
　　　FROM　aroma.product　a,　
　　　　　aroma.sales　b,　
　　　　　aroma.period　c,　
　　　　　aroma.store　d　
　　　WHERE　　a.prodkey　=　b.prodkey　
　　　　　AND　a.classkey　=　b.classkey　
　　　　　AND　c.perkey　=　b.perkey　
　　　　　AND　d.storekey　=　b.storekey　
　　　　　AND　c.year　=　2004　
　　　　　AND　c.month　=　'JAN'　
　　　　　AND　d.city　LIKE　'San　J%'　
　　　GROUP　BY　a.prod_name)　AS　sales1(product,　jan_04_sales)　
　,　
　　　(SELECT　prod_name,　SUM(dollars)　AS　total_04_sales　
　　　FROM　aroma.product　a,　
　　　　　aroma.sales　b,　
　　　　　aroma.period　c,　
　　　　　aroma.store　d　
　　　WHERE　　a.prodkey　=　b.prodkey　
　　　　　AND　a.classkey　=　b.classkey　
　　　　　AND　c.perkey　=　b.perkey　
　　　　　AND　d.storekey　=　b.storekey　
　　　　　AND　c.year　=　2004　
　　　　　AND　d.city　LIKE　'San　J%'　
　　　GROUP　BY　a.prod_name)　AS　sales2(product,　total_04_sales)　
　WHERE　sales1.product　=　sales2.product　
　ORDER　BY　sales1.product;

结果

PRODUCT	JAN_04_SALES	TOTAL_04_SALES
Aroma Roma	1653.00	21697.50
Aroma Sheffield Steel Teapot	120.00	1122.00
Aroma Sounds Cassette	58.50	866.00
Aroma baseball cap	7.95	2960.15
Aroma t-shirt	470.85	4470.50
Assam Gold Blend	652.00	11375.00
Assam Grade A	352.00	5429.00
Breakfast Blend	608.25	6394.75
Cafe Au Lait	1936.50	24050.50
Colombiano	2148.00	22528.50
Darjeeling Number 1	867.50	8590.00
Darjeeling Special	1355.00	17787.50
Demitasse Ms	2163.00	35523.50
Earl Grey	540.50	6608.50
English Breakfast	393.00	5365.50
Espresso Machine Italiano	899.55	4397.80
Expresso XO	2935.50	27362.00
French Press, 2-Cup	104.65	1196.00
French Press, 4-Cup	19.95	1109.20
Gold Tips	440.00	5381.50
Irish Breakfast	703.25	7455.50
...

一种更灵活的方案：FROM 子句中的子查询

子查询是放在另一个查询内的括号中的查询表达式。子查询有时也称为一个外部查询（outer query）中的内部查询（inner query），或者称为一个父查询（parent query）的子查询（child query）。

关于该查询

有时需要将一个值与一组值的和进行比较。这个例子查询将圣何塞在 2004 年 1 月份 的产品销售额与该城在 2004 年全年 的产品销售额进行比较。这种查询需要混合聚合（mixed aggregations）；因此，它不能用 CASE 表达式来编写，因为 CASE 表达式仅适用于一个组或范围内的值。但是可以用 FROM 子句中的子查询来编写这样的比较查询。

重要：任何查询，如果它可以用 FROM 子句中的子查询来表达，那么必定也可以用 select 列表中的子查询来表达，本教程的后面将展示这一点。但是，FROM 子句中的子查询通常可以运行得更快，从概念上讲也更容易编写。

使用中的注意事项

这个例子查询依赖于标准 SQL 中查询表达式 的灵活性来联结两个子查询的结果。

子查询得到的结果表可以与其他表引用联结在一起。出于这个目的，FROM 子句中的子查询必须有一个相关名称。例如，这个例子中的子查询的计算结果为以下两个表：

sales1(product,　jan_04_sales)　
　sales2(product,　total_04_sales)

通过（在 Product 列上）联结这两个表，可以产生一个未命名的派生表，这个表包含三列，这三个列就是主查询中 select 列表中三个项的来源：

product,　jan_04_sales,　total_04_sales

执行计算和比较

问题

2004 圣何塞各产品在 1 月份的销售额占全年销售总额的百分比是多少？按这个百分比排名，排在前 10 名的产品是哪些？

例子查询

SELECT　sales1.product,　jan_04_sales,　total_04_sales,　
　　　DEC((100.00　*　jan_04_sales/total_04_sales),7,2)　AS　pct_of_04　
　FROM　
　　　(SELECT　a1.prod_name,　SUM(dollars)　
　　　FROM　aroma.product　a1,　
　　　　　aroma.sales　b1,　
　　　　　aroma.period　c1,　
　　　　　aroma.store　d1　
　　　WHERE　　a1.prodkey　=　b1.prodkey　
　　　　　AND　a1.classkey　=　b1.classkey　
　　　　　AND　c1.perkey　=　b1.perkey　
　　　　　AND　d1.storekey　=　b1.storekey　
　　　　　AND　c1.year　=　2004　
　　　　　AND　c1.month　=　'JAN'　
　　　　　AND　d1.city　LIKE　'San　J%'　
　　　GROUP　BY　a1.prod_name)　AS　sales1(product,　jan_04_sales)　
　,　
　　　(SELECT　a2.prod_name,　SUM(dollars)　
　　　FROM　aroma.product　a2,　
　　　　　aroma.sales　b2,　
　　　　　aroma.period　c2,　
　　　　　aroma.store　d2　
　　　WHERE　　a2.prodkey　=　b2.prodkey　
　　　　　AND　a2.classkey　=　b2.classkey　
　　　　　AND　c2.perkey　=　b2.perkey　
　　　　　AND　d2.storekey　=　b2.storekey　
　　　　　AND　c2.year　=　2004　
　　　　　AND　d2.city　LIKE　'San　J%'　
　　　GROUP　BY　a2.prod_name)　AS　sales2(product,　total_04_sales)　
　WHERE　sales1.product　=　sales2.product　
　ORDER　BY　pct_of_04　DESC　
　FETCH　FIRST　10　ROWS　ONLY;

结果

PRODUCT	JAN_04_SALES	TOTAL_04_SALES	PCT_OF_04
Espresso Machine Italiano	899.55	4397.80	20.45
La Antigua	2643.25	22244.50	11.88
Expresso XO	2935.50	27362.00	10.72
Aroma Sheffield Steel Teapot	120.00	1122.00	10.69
Aroma t-shirt	470.85	4470.50	10.53
Lotta Latte	3195.00	31200.00	10.24
Darjeeling Number 1	867.50	8590.00	10.09
Colombiano	2148.00	22528.50	9.53
Breakfast Blend	608.25	6394.75	9.51
Irish Breakfast	703.25	7455.50	9.43

使用 FROM 子句的子查询执行的计算

比较查询的结果集可以作为各种计算的源数据。例如，可以用一个简单的百分比计算，算出某种产品的月销售额占全年销售额的份额：

100.00　*　monthly_sales　/　annual_sales

通过 FROM 子句中的子查询，可以计算简单的和复杂的市场、产品和时间段份额或百分比。

关于该查询

基于前一个例子，本查询计算在圣何塞每种产品的月销售额占该城该产品全年销售额的份额或百分比。结果集可以根据 sort by 指定的列，给出经过排名的列表。

Pct_of_04 列中的值加起来不等于 100，因为这些数字表示各种不同产品一个月的销售额占全年销售额的百分比，而不是所有产品月销售额占全年销售额的百分比。

上面的表显示 1 月份的销售额占全年销售总额的一定百分比。例如，1 月份茶壶销售额占全年茶壶销售额的 10.69%。

使用 SELECT 列表中的子查询

问题

在 2005 年 12 月份，有哪些天 San Jose Roasting 公司在 Lotta Latte 方面的销售额低于 2004 年 12 月份该店同一产品的日均销售额？用一列单独显示 2004 年的日均销售额。

例子查询

SELECT　prod_name,　store_name,　date,　dollars　AS　sales_05,　
　　　(SELECT　DEC(AVG(dollars),7,2)　
　　　FROM　aroma.product　a,　
　　　　　aroma.sales　b,　
　　　　　aroma.period　c,　
　　　　　aroma.store　d　
　　　WHERE　　a.prodkey　=　b.prodkey　
　　　　　AND　a.classkey　=　b.classkey　
　　　　　AND　c.perkey　=　b.perkey　
　　　　　AND　d.storekey　=　b.storekey　
　　　　　AND　year　=　2004　
　　　　　AND　month　=　'DEC'　
　　　　　AND　store_name　=　'San　Jose　Roasting　Company'　
　　　　　AND　prod_name　LIKE　'Lotta%')　AS　avg_04　
　FROM　aroma.product　a,　
　　　aroma.sales　b,　
　　　aroma.period　c,　
　　　aroma.store　d　
　WHERE　　a.prodkey　=　b.prodkey　
　　　AND　a.classkey　=　b.classkey　
　　　AND　c.perkey　=　b.perkey　
　　　AND　d.storekey　=　b.storekey　
　　　AND　prod_name　LIKE　'Lotta%'　
　　　AND　store_name　=　'San　Jose　Roasting　Company'　
　　　AND　year　=　2005　
　　　AND　month　=　'DEC'　
　　　AND　dollars　<　
　　　　　(SELECT　AVG(dollars)　
　　　　　FROM　aroma.product　a,　
　　　　　　　aroma.sales　b,　
　　　　　　　aroma.period　c,　
　　　　　　　aroma.store　d　
　　　　　WHERE　　a.prodkey　=　b.prodkey　
　　　　　　　AND　a.classkey　=　b.classkey　
　　　　　　　AND　c.perkey　=　b.perkey　
　　　　　　　AND　d.storekey　=　b.storekey　
　　　　　　　AND　year　=　2004　
　　　　　　　AND　month　=　'DEC'　
　　　　　　　AND　store_name　=　'San　Jose　Roasting　Company'　
　　　　　　　AND　prod_name　LIKE　'Lotta%');

结果

PROD_NAME	STORE_NAME	DATE	SALES_05	AVG_04
Lotta Latte	San Jose Roasting Company	Dec 8, 2005	153.00	154.72
Lotta Latte	San Jose Roasting Company	Dec 27, 2005	144.50	154.72

用 select 列表子查询进行比较

只有当一个子查询返回一行或零行时，它才可以出现在主查询的 select 列表中。这种子查询称作标量子查询（scalar subquery），它对于电子表格式的比较很有用，这种比较是将主查询返回的一系列值与子查询返回的一个值进行比较。

关于该查询

这个例子子查询返回 San Jose Roasting 公司在 2005 年 12 月份一些天的 Lotta Latte 日销售额，这些数字都满足一个条件，即它们都低于 2004 年 12 月份该商店同一产品的日均销售额。Avg_04 列包含一个不断重复的值，这个值就是 2004 年 12 月份的日均销售额。不管结果集的行数是多少，那一列都是出现相同的值。

同一个子查询在主查询中出现了两次：

一次是作为 select 列表中的一个列定义。

另一次是作为 WHERE 子句条件中小于操作符（<）的一个操作数。

这个查询是按照以下顺序来处理的：

首先执行第二个子查询，该子查询在主查询的 SELECT 子句中定义搜索条件。

将第二个子查询计算出的值插入到主查询的 WHERE 子句中。

执行 select 列表中的子查询。

执行主查询。

使用中的注意事项

结果集的 Avg_04 列上的 DEC 标量函数用于平均销售数据的格式化：

DEC(AVG(dollars),7,2)

使用相关子查询

问题

如何将 2004 年 1 月份各产品在圣何塞的销售额与该城在同一年全年的销售额进行比较？

例子查询

SELECT　p1.prod_name,　SUM(s1.dollars)　AS　jan_04_sales,　
　　　(SELECT　SUM(s2.dollars)　
　　　FROM　aroma.product　p2,　
　　　　　aroma.sales　s2,　
　　　　　aroma.period　d2,　
　　　　　aroma.store　r2　
　　　WHERE　　p2.prodkey　=　s2.prodkey　
　　　　　AND　p2.classkey　=　s2.classkey　
　　　　　AND　d2.perkey　=　s2.perkey　
　　　　　AND　r2.storekey　=　s2.storekey　
　　　　　AND　p1.prod_name　=　p2.prod_name　
　　　　　AND　d1.year　=　d2.year　
　　　　　AND　r1.city　=　r2.city)　AS　total_04_sales　
　FROM　aroma.store　r1,　
　　　aroma.sales　s1,　
　　　aroma.product　p1,　
　　　aroma.period　d1　
　WHERE　　p1.prodkey　=　s1.prodkey　
　　　AND　p1.classkey　=　s1.classkey　
　　　AND　d1.perkey　=　s1.perkey　
　　　AND　r1.storekey　=　s1.storekey　
　　　AND　year　=　2004　
　　　AND　month　=　'JAN'　
　　　AND　city　LIKE　'San　J%'　
　GROUP　BY　p1.prod_name,　d1.year,　r1.city　
　ORDER　BY　p1.prod_name;

结果

PROD_NAME	JAN_04_SALES	TOTAL_04_SALES
Aroma Roma	1653.00	21697.50
Aroma Sheffield Steel Teapot	120.00	1122.00
Aroma Sounds Cassette	58.50	866.00
Aroma baseball cap	7.95	2960.15
Aroma t-shirt	470.85	4470.50
Assam Gold Blend	652.00	11375.00
Assam Grade A	352.00	5429.00
Breakfast Blend	608.25	6394.75
Cafe Au Lait	1936.50	24050.50
Colombiano	2148.00	22528.50
Darjeeling Number 1	867.50	8590.00
Darjeeling Special	1355.00	17787.50
Demitasse Ms	2163.00	35523.50
Earl Grey	540.50	6608.50
English Breakfast	393.00	5365.50
Espresso Machine Italiano	899.55	4397.80
Expresso XO	2935.50	27362.00
French Press, 2-Cup	104.65	1196.00
French Press, 4-Cup	19.95	1109.20
Gold Tips	440.00	5381.50
Irish Breakfast	703.25	7455.50
La Antigua	2643.25	22244.50
Lotta Latte	3195.00	31200.00
NA Lite	1319.00	27457.00
...

select 列表中的相关子查询

虽然 select 列表子查询必须返回一个值或不返回值，但是它们可以在对主查询返回的结果的引用中执行多次。这样，就可以在 select 列表中使用那样的相关子查询 实现与 FROM 子句中的子查询相同的效果。

相关子查询通过交叉引用指定主查询所检索的行中的值，从而与主查询紧密相关。例如，一个相关子查询可以引用主查询的 Month 列中的值。因此，随着 Month 列的值的不同，该子查询每次返回一个新的值。这些交叉引用是用 FROM 子句中指定的表相关名称表达的。

关于该查询

这个例子查询与前面的查询回答的是同一个业务问题，但是该查询将子查询放在 select 列表中，而不是放在 FROM 子句中。该查询将圣何塞在 2004 年 1 月份各产品的销售额与同一年圣何塞各产品全年的销售额进行比较。

为了使子查询可以返回一系列的值，而不是返回一个固定的值，这里使用了三个交叉引用 将子查询与主查询相关联：

　p1.prod_name　=　p2.prod_name　
　d1.year　=　d2.year　
　r1.city　=　r2.city

子查询的 FROM 子句中定义的 p2、d2 和 r2 这三个相关名称消除了模糊之处。每个相关条件都引用主查询当前处理的行中的特定产品、年份和城市。这些交叉引用有时也称为外部引用（outer reference）。

使用中的注意事项

当主查询的 select 列表中出现一个聚合函数时，需要有一个 GROUP BY 子句。子查询的相关条件中引用的列名必须出现在主查询的 GROUP BY 子句中。因此，GROUP BY 子句中必须列出以下列，另外还需列出 Prod_Name 列：

　d1.year,　r1.city

使用交叉引用

问题

圣何塞的商店在 2005 年和 2004 年的前三个月在 Lotta Latte 方面的月销售额是多少？

例子查询

SELECT　q.prod_name,　e.month,　SUM(dollars)　AS　sales_05,　
　　　(SELECT　SUM(dollars)　
　　　FROM　aroma.store　t,　
　　　　　aroma.sales　s,　
　　　　　aroma.product　p,　
　　　　　aroma.period　d　
　　　WHERE　　t.storekey　=　s.storekey　
　　　　　AND　p.classkey　=　s.classkey　
　　　　　AND　p.prodkey　=　s.prodkey　
　　　　　AND　d.perkey　=　s.perkey　
　　　　　AND　d.month　=　e.month　
　　　　　AND　d.year　=　e.year-1　
　　　　　AND　p.prod_name　=　q.prod_name　
　　　　　AND　t.city　=　u.city)　AS　sales_04　
　FROM　aroma.store　u,　
　　　aroma.product　q,　
　　　aroma.period　e,　
　　　aroma.sales　l　
　WHERE　　u.storekey　=　l.storekey　
　　　AND　q.classkey　=　l.classkey　
　　　AND　q.prodkey　=　l.prodkey　
　　　AND　e.perkey　=　l.perkey　
　　　AND　qtr　=　'Q1_05'　
　　　AND　prod_name　LIKE　'Lotta　Latte%'　
　　　AND　city　like　'San　J%'　
　GROUP　BY　q.prod_name,　e.month,　e.year,　u.city;

结果

PROD_NAME	MONTH	SALES_05	SALES_04
Lotta Latte	FEB	3213.50	4239.50
Lotta Latte	JAN	1611.00	3195.00
Lotta Latte	MAR	2510.50	2980.50

表达式交叉引用

交叉引用不限于限定列名，它们也可以是表达式。例如，下面的表达式就是有效的交叉引用：

　period.year-1　(previous　year)　
　period.quarter-1　(previous　quarter)

这些类型的交叉引用可以简化为客户工具编写的应用程序的设计。

关于该查询

这个查询返回圣何塞的商店在 2005 年和 2004 年的前三个月在 Lotta Latte 方面的月销售额。相关的关键之处在于，期望的结果中包含不同年份相同月份的数据。

主查询的 FROM 子句指定所有参加联结的表的相关名称：

　　FROM　aroma.store　u,　
　　　　aroma.product　q,　
　　　　aroma.period　e,　
　　　　aroma.sales　l

然后，子查询根据 WHERE 子句中的下列条件，将它的执行与主查询的执行相关联：

　d.month　=　e.month　
　d.year　=　e.year-1　
　p.prod_name　=　q.prod_name　
　t.city　=　u.city

当主查询检索行时，父查询中的列值可能发生变化，相关条件会将这个变化传送到子查询。通过用 year-1 交叉引用前一年，可以避免在子查询中使用常量值（2004），从而使子查询更通用。

若要更改查询，使其报告其他年份的数据，那么只需更改主查询中的年份常量。

使用中的注意事项

应尽量使相关子查询通用化，使用表达式作为交叉引用，减少用户的交互。

计算占季度和年销售额的百分比

问题

在 2004 年第一季度中，位于圣何塞的商店在一磅装的产品方面的每月销售额是多少？每个月的销售额占季度销售总额和全年销售总额的百分比是多少？

例子查询

　　SELECT　pj.prod_name,　dj.month,　
　　　SUM(dollars)　AS　mon_sales_04,　
　　　DEC(100　*　DEC(SUM(dollars))/　
　　　　　(SELECT　SUM(si.dollars)　
　　　　　FROM　aroma.store　ri,　
　　　　　　　　aroma.sales　si,　
　　　　　　　　aroma.product　pi,　
　　　　　　　　aroma.period　di　
　　　　　WHERE　　si.storekey　=　ri.storekey　
　　　　　　　AND　si.classkey　=　pi.classkey　
　　　　　　　AND　si.prodkey　=　pi.prodkey　
　　　　　　　AND　si.perkey　=　di.perkey　
　　　　　　　AND　di.qtr　=　dj.qtr　
　　　　　　　AND　di.year　=　dj.year　
　　　　　　　AND　pi.prod_name　=　pj.prod_name　
　　　　　　　AND　pi.pkg_type　=　pj.pkg_type　
　　　　　　　AND　ri.city　=　rj.city),　7,　2)　AS　pct_qtr1,　
　　　DEC(100　*　DEC(SUM(dollars))/　
　　　　　(SELECT　SUM(si.dollars)　
　　　　　FROM　aroma.store　ri,　
　　　　　　　　aroma.sales　si,　
　　　　　　　　aroma.product　pi,　
　　　　　　　　aroma.period　di　
　　　　　WHERE　　si.storekey　=　ri.storekey　
　　　　　　　AND　si.classkey　=　pi.classkey　
　　　　　　　AND　si.prodkey　=　pi.prodkey　
　　　　　　　AND　si.perkey　=　di.perkey　
　　　　　　　AND　di.year　=　dj.year　
　　　　　　　AND　pi.prod_name　=　pj.prod_name　
　　　　　　　AND　pi.pkg_type　=　pj.pkg_type　
　　　　　　　AND　ri.city　=　rj.city),　7,　2)　AS　pct_yr　
　FROM　aroma.store　rj,　
　　　　aroma.sales　sj,　
　　　　aroma.product　pj,　
　　　　aroma.period　dj　
　WHERE　　sj.storekey　=　rj.storekey　
　　　AND　sj.classkey　=　pj.classkey　
　　　AND　sj.prodkey　=　pj.prodkey　
　　　AND　sj.perkey　=　dj.perkey　
　　　AND　rj.city　=　'San　Jose'　
　　　AND　dj.year　=　2004　
　　　AND　dj.qtr　=　'Q1_04'　
　　　AND　pkg_type　=　'One-pound　bag'　
　GROUP　BY　pj.prod_name,　dj.month,　dj.qtr,　dj.year,　pj.pkg_type,　rj.city　
　ORDER　BY　pj.prod_name,　pct_qtr1　desc;

结果

PROD_NAME	MONTH	MON_SALES_04	PCT_QTR1	PCT_YR
Aroma Roma	FEB	688.75	39.87	8.72
Aroma Roma	JAN	594.50	34.42	7.53
Aroma Roma	MAR	442.25	25.61	5.60
Cafe Au Lait	MAR	742.00	40.61	10.27
Cafe Au Lait	JAN	600.50	32.84	8.31
Cafe Au Lait	FEB	484.50	26.49	6.70
...

用 select 列表子查询进行计算

每月销售额占季度销售总额和年度销售总额或其他时期销售总额的百分比可以用一个 select 列表子查询来计算。主查询检索每个月的销售额，两个子查询则检索季度销售额和年度销售额。月销售额所占百分比需要进行简单的计算：一个是月销售额占季度销售额的百分比，一个是月销售额占年销售额的百分比。

关于该查询

这个例子查询计算在 2004 年第一季度，在圣何塞的商店出售的指定咖啡商品的月销售额占季度销售额和年销售额的百分比。在计算百分比之后，该查询按产品和占季度销售额百分比递减的顺序对结果表排序。

使用 WHERE 子句中的子查询

问题

2005 年 6 月份，在芝加哥的商店中，有哪些天 Lotta Latte 的销售额低于 2004 年 6 月份该地区同一产品的日均销售额？

例子查询

　SELECT　prod_name,　district,　date,　dollars　AS　sales_05　
　FROM　aroma.market　a,　
　　　　aroma.store　b,　
　　　　aroma.sales　c,　
　　　　aroma.product　d,　
　　　　aroma.period　e　
　WHERE　　a.mktkey　=　b.mktkey　
　　　AND　b.storekey　=　c.storekey　
　　　AND　d.classkey　=　c.classkey　
　　　AND　d.prodkey　=　c.prodkey　
　　　AND　e.perkey　=　c.perkey　
　　　AND　prod_name　like　'Lotta%'　
　　　AND　district　like　'Chic%'　
　　　AND　year　=　2005　
　　　AND　month　=　'JUN'　
　　　AND　dollars　<　
　　　　　(SELECT　AVG(dollars)　
　　　　　FROM　aroma.market　a,　
　　　　　　　　aroma.store　b,　
　　　　　　　　aroma.sales　c,　
　　　　　　　　aroma.product　d,　
　　　　　　　　aroma.period　e　
　　　　　WHERE　　a.mktkey　=　b.mktkey　
　　　　　　　AND　b.storekey　=　c.storekey　
　　　　　　　AND　d.classkey　=　c.classkey　
　　　　　　　AND　d.prodkey　=　c.prodkey　
　　　　　　　AND　e.perkey　=　c.perkey　
　　　　　　　AND　prod_name　like　'Lotta%'　
　　　　　　　AND　district　like　'Chic%'　
　　　　　　　AND　year　=　2004　
　　　　　　　AND　month　=　'JUN')　
　ORDER　BY　date;

结果

PROD_NAME	DISTRICT	DATE	SALES_05
Lotta Latte	Chicago	Jun 7, 2005	76.50
Lotta Latte	Chicago	Jun 10, 2005	59.50
Lotta Latte	Chicago	Jun 16, 2005	42.50
Lotta Latte	Chicago	Jun 17, 2005	76.50
Lotta Latte	Chicago	Jun 29, 2005	110.50

关于该查询

这个查询返回 2005 年 6 月份在芝加哥的商店中，日销售额低于 2004 年 6 月份该地区同一产品日均销售额的那些天的 Lotta Latte 的销售数据。

这个例子中的子查询是标量子查询 —— 它只产生一个值。当子查询计算出芝加哥的商店在 2004 年 6 月份 Lotta Latte 的日均销售额之后，这个平均值被用于主查询返回的所有行上的一个约束条件。2005 年 6 月份中，只有销售额低于 2004 年那个平均值的那些天的数据会显示在结果集中。除非将子查询移入到 select 列表或 FROM 子句中，否则这个平均值本身不会显示在结果集中。

使用中的注意事项

查询处理的逻辑顺序表明，当 FROM 子句中的表被联结之后，在任何使用集合函数（例如 AVG 和 SUM 等）的计算执行之前，服务器立即应用 WHERE 子句约束。因此，不能在 WHERE 子句中的一个简单搜索条件中使用那些函数。

使用比较谓词

问题

2006 年 1 月份，在康涅狄格的哈特福德，什么产品取得了最高日销售额记录？

例子查询

　SELECT　prod_name,　date,　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　year　=　2006　
　　　AND　city　=　'Hartford'　
　　　AND　month　=　'JAN'　
　　　AND　dollars　>=　ALL　
　　　　　(SELECT　dollars　
　　　　　FROM　aroma.product　a,　
　　　　　　　　aroma.sales　b,　
　　　　　　　　aroma.period　c,　
　　　　　　　　aroma.store　d　
　　　　　WHERE　　a.prodkey　=　b.prodkey　
　　　　　　　AND　a.classkey　=　b.classkey　
　　　　　　　AND　c.perkey　=　b.perkey　
　　　　　　　AND　d.storekey　=　b.storekey　
　　　　　　　AND　year　=　2006　
　　　　　　　AND　city　=　'Hartford'　
　　　　　　　AND　month　=　'JAN');

结果

PROD_NAME	DATE	DOLLARS
NA Lite	2006-01-23	414.00

关于该查询

这个例子查询返回 2006 年 1 月份在康涅狄格的哈特福德取得最高日销售额记录的产品的名称，以及取得这一记录的日期。只需将大于等于号（>=）换成小于等于号（<=），就可以将该查询改为返回最低销售额。

ANY / SOME 谓词

还可以在一个子查询检索的一个或多个值上的 select 语句中，对每个返回的结果进行计算。如果任何一个计算的结果为 true，则返回结果集。对于这种类型的查询，关键字 ANY 和 SOME 的意思是一样的。

问题

列出位于总部所在城市的所有商店的名称。

例子查询（ANY 或 SOME）

　SELECT　store_name　
　FROM　aroma.store　
　WHERE　city　=　ANY　
　　　(SELECT　hq_city　
　　　FROM　aroma.market)　
　ORDER　BY　state,　city,　store_name;

结果

STORE_NAME

Java Judy's

Beaches Brew

Instant Coffee

...

East Coast Roast

Texas Teahouse

Coffee Brewers

使用中的注意事项

系统首先执行内部 SELECT 语句，并将一组 HQ_CITY 值存储在一个临时表中。然后，它在外部 SELECT 列表中计算这个临时表中的每一行。如果 CITY 值等于内部查询产生的一行或多行，那么这个值就被返回到结果集中。

子查询中的比较谓词

谓词 ALL、ANY、SOME 和 EXISTS 对于表达一个子查询检索的分组中的值上的条件非常有用。一个比较谓词确定两个值之间的逻辑关系：对于一个给定的行，这种比较的结果为 true、false 或 unknown。（ANY 和 SOME 谓词是同义的。）

PREDICATE	EVALUATES TO "TRUE" WHEN	WHEN NO VALUE IS RETURNED
ALL	对于子查询返回的所有值，该比较都为 true	结果为 true
ANY, SOME	对于子查询返回的至少一个值该比较为 true	结果为 false
EXISTS	子查询产生至少一行	结果为 false

结束语

本教程描述如何编写比较数据并以易于阅读的格式显示结果的查询。本教程阐述了各种不同的方法：

CASE 表达式

FROM 子句子查询

select 列表子查询，包括相关子查询

WHERE 子句子查询

比较查询

本教程最后介绍了 ALL、ANY、SOME 和 EXISTS 比较谓词，这些谓词可用于子查询结果上的约束条件中的数学比较。

教程中还提供了一些更复杂的例子，以展示如何在比较查询中包括计算，例如表示占季度或年度销售额份额的百分比。

重要：通常，如果比较查询使用 CASE 表达式，而不是使用子查询，则查询的性能会更快。但是，如果必须使用子查询，那么应优先使用 FROM 子句，而不是 select 列表。数据库和查询有很多不同的类型，因此建议您对所使用的数据库尝试不同的方法。

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