DB2 SQL 与 XQuery 教程，第 5 部分: 数据比较
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 数据库中的数据的查询。本教程首先提出查询编写者遇到的问题：如何使用 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 列表。数据库和查询有很多不同的类型，因此建议您对所使用的数据库尝试不同的方法。 |
本文示例源代码或素材下载
- ››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 教程，第 5 部分: 数据比较”更多相关内容
中查找“DB2 SQL 与 XQuery 教程，第 5 部分: 数据比较”更多相关内容更多精彩
赞助商链接
