WEB开发网
开发学院数据库DB2 DB2 SQL 与 XQuery 教程,第 5 部分: 数据比较 阅读

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

 2009-11-28 00:00:00 来源:WEB开发网   
核心提示:开始之前关于本系列本教程为您讲解高级 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_NAMEPROD_NAMESALES
Beaches BrewAroma Roma3483.50
Beaches BrewCafe Au Lait3129.50
Beaches BrewColombiano2298.25
Beaches BrewDemitasse Ms4529.25
Beaches BrewExpresso XO4132.75
Beaches BrewLa Antigua4219.75
Beaches BrewLotta Latte3468.00
Beaches BrewNA Lite4771.00
Beaches BrewVeracruzano4443.00
Beaches BrewXalapa Lapa4304.00
Cupertino Coffee SupplyAroma Roma4491.00
Cupertino Coffee SupplyCafe Au Lait4375.50
Cupertino Coffee SupplyColombiano2653.50
Cupertino Coffee SupplyDemitasse Ms3936.50
Cupertino Coffee SupplyExpresso XO4689.25
Cupertino Coffee SupplyLa Antigua2932.00
Cupertino Coffee SupplyLotta Latte5146.00
Cupertino Coffee SupplyNA Lite4026.00
Cupertino Coffee SupplyVeracruzano3285.00
Cupertino Coffee SupplyXalapa Lapa5784.00
Instant CoffeeAroma Roma3485.25
Instant CoffeeCafe Au Lait3599.50
Instant CoffeeColombiano3321.75
Instant CoffeeDemitasse Ms5422.25
Instant CoffeeExpresso XO2851.00
Instant CoffeeLa Antigua2937.25
Instant CoffeeLotta Latte4783.50
Instant CoffeeNA Lite3740.00
Instant CoffeeVeracruzano4712.00
Instant CoffeeXalapa Lapa3698.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_NAMEBEACHESCUPERTINOROASTLGSJROASTCOJAVAJUDYINSTANT
Aroma Roma3483.504491.004602.004399.253748.253485.25
Cafe Au Lait3129.504375.504199.003620.004864.503599.50
Colombiano2298.252653.504205.003530.753509.003321.75
Demitasse Ms4529.253936.504347.755699.006395.255422.25
Expresso XO4132.754689.254234.503811.005012.252851.00
La Antigua4219.752932.003447.504323.002410.252937.25
Lotta Latte3468.005146.004469.505103.504003.004783.50
NA Lite4771.004026.003250.002736.004791.003740.00
Veracruzano4443.003285.004467.003856.004510.004712.00
Xalapa Lapa4304.005784.003906.003645.003182.003698.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 时所用的值
DB2 SQL 与 XQuery 教程,第 5 部分: 数据比较重要: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;

结果

PRODUCTJAN_04_SALESTOTAL_04_SALES
Aroma Roma1653.0021697.50
Aroma Sheffield Steel Teapot120.001122.00
Aroma Sounds Cassette58.50866.00
Aroma baseball cap7.952960.15
Aroma t-shirt470.854470.50
Assam Gold Blend652.0011375.00
Assam Grade A352.005429.00
Breakfast Blend608.256394.75
Cafe Au Lait1936.5024050.50
Colombiano2148.0022528.50
Darjeeling Number 1867.508590.00
Darjeeling Special1355.0017787.50
Demitasse Ms2163.0035523.50
Earl Grey540.506608.50
English Breakfast393.005365.50
Espresso Machine Italiano899.554397.80
Expresso XO2935.5027362.00
French Press, 2-Cup104.651196.00
French Press, 4-Cup19.951109.20
Gold Tips440.005381.50
Irish Breakfast703.257455.50
...

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

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

关于该查询

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

DB2 SQL 与 XQuery 教程,第 5 部分: 数据比较重要:任何查询,如果它可以用 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;

结果

PRODUCTJAN_04_SALESTOTAL_04_SALESPCT_OF_04
Espresso Machine Italiano899.554397.8020.45
La Antigua2643.2522244.5011.88
Expresso XO2935.5027362.0010.72
Aroma Sheffield Steel Teapot120.001122.0010.69
Aroma t-shirt470.854470.5010.53
Lotta Latte3195.0031200.0010.24
Darjeeling Number 1867.508590.0010.09
Colombiano2148.0022528.509.53
Breakfast Blend608.256394.759.51
Irish Breakfast703.257455.509.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_NAMESTORE_NAMEDATESALES_05AVG_04
Lotta LatteSan Jose Roasting CompanyDec 8, 2005153.00154.72
Lotta LatteSan Jose Roasting CompanyDec 27, 2005144.50154.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_NAMEJAN_04_SALESTOTAL_04_SALES
Aroma Roma1653.0021697.50
Aroma Sheffield Steel Teapot120.001122.00
Aroma Sounds Cassette58.50866.00
Aroma baseball cap7.952960.15
Aroma t-shirt470.854470.50
Assam Gold Blend652.0011375.00
Assam Grade A352.005429.00
Breakfast Blend608.256394.75
Cafe Au Lait1936.5024050.50
Colombiano2148.0022528.50
Darjeeling Number 1867.508590.00
Darjeeling Special1355.0017787.50
Demitasse Ms2163.0035523.50
Earl Grey540.506608.50
English Breakfast393.005365.50
Espresso Machine Italiano899.554397.80
Expresso XO2935.5027362.00
French Press, 2-Cup104.651196.00
French Press, 4-Cup19.951109.20
Gold Tips440.005381.50
Irish Breakfast703.257455.50
La Antigua2643.2522244.50
Lotta Latte3195.0031200.00
NA Lite1319.0027457.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_NAMEMONTHSALES_05SALES_04
Lotta LatteFEB3213.504239.50
Lotta LatteJAN1611.003195.00
Lotta LatteMAR2510.502980.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_NAMEMONTHMON_SALES_04PCT_QTR1PCT_YR
Aroma RomaFEB688.7539.878.72
Aroma RomaJAN594.5034.427.53
Aroma RomaMAR442.2525.615.60
Cafe Au LaitMAR742.0040.6110.27
Cafe Au LaitJAN600.5032.848.31
Cafe Au LaitFEB484.5026.496.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_NAMEDISTRICTDATESALES_05
Lotta LatteChicagoJun 7, 200576.50
Lotta LatteChicagoJun 10, 200559.50
Lotta LatteChicagoJun 16, 200542.50
Lotta LatteChicagoJun 17, 200576.50
Lotta LatteChicagoJun 29, 2005110.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_NAMEDATEDOLLARS
NA Lite2006-01-23414.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 谓词是同义的。)

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

结束语

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

CASE 表达式

FROM 子句子查询

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

WHERE 子句子查询

比较查询

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

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

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

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

Tags:DB SQL XQuery

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接