2021
11/23
17:39
你的位置:首页 > 数据库 > SQL 关联子查询

SQL 关联子查询

发布时间:2021-11-23 17:39:54

原标题:SQL 关联子查询

目录
  • 一,普通的子查询和关联子查询的区别
  • 二,关联子查询也是用来对集合进行切分的
  • 三,结合条件一定要写在子查询中
  • 请参阅

学习重点

  • 关联子查询会在细分的组内进行比较时使用。

  • 关联子查询和 GROUP BY 子句一样,也可以对表中的数据进行切分。

  • 关联子查询的结合条件如果未出现在子查询之中就会发生错误。

一,普通的子查询和关联子查询的区别

按此前所学,使用子查询就能选取出销售单价(sale_price)高于全部商品平均销售单价的商品。这次我们稍稍改变一下条件,选取出各商品种类中高于该商品种类的平均销售单价的商品。

  • 按照商品种类与平均销售单价进行比较

    只通过语言描述可能难以理解,还是让我们来看看具体示例吧。我们以厨房用具中的商品为例,该分组中包含了表 1 所示的 4 种商品。

    表 1 厨房用具中的商品

    商品名称销售单价
    菜刀3000
    高压锅6800
    叉子500
    擦菜板880

    因此,计算上述 4 种商品的平均价格的算术式如下所示。

    (3000 + 6800 + 500 + 880) / 4 = 2795 (日元)

    这样我们就能得知该分组内高于平均价格的商品是菜刀和高压锅了,这两种商品就是我们要选取的对象。

    我们可以对余下的分组继续使用同样的方法。衣服分组的平均销售单价是:

    (1000 + 4000) / 2 = 2500 (日元)

    因此运动T恤就是要选取的对象。办公用品分组的平均销售单价是:

    (500 + 100) / 2 = 300 (日元)

    因此打孔器就是我们要选取的对象。

    这样大家就能明白该进行什么样的操作了吧。我们并不是要以全部商品为基础,而是要以细分的组为基础,对组内商品的平均价格和各商品的销售单价进行比较。

    按照商品种类计算平均价格并不是什么难事,我们已经学习过了,只需按照代码清单 15 那样,使用 GROUP BY 子句就可以了。

    代码清单 15 按照商品种类计算平均价格

    SELECT AVG(sale_price)FROM ProductGROUP BY product_type;

    但是,如果我们使用前一节(标量子查询)的方法,直接把上述 SELECT 语句使用到 WHERE 子句当中的话,就会发生错误。

    -- 发生错误的子查询SELECT product_id, product_name, sale_priceFROM ProductWHERE sale_price > (SELECT AVG(sale_price)      FROM Product      GROUP BY product_type);

    出错原因前一节已经讲过了,该子查询会返回 3 行结果(2795,2500,300),并不是标量子查询。在 WHERE 子句中使用子查询时,该子查询的结果必须是单一的。

    但是,如果以商品种类分组为单位,对销售单价和平均单价进行比较,除此之外似乎也没有其他什么办法了。到底应该怎么办才好呢?

  • 使用关联子查询的解决方案

    这时就轮到我们的好帮手——关联子查询登场了。

    KEYWORD

    • 关联子查询

    只需要在刚才的 SELECT 语句中追加一行,就能得到我们想要的结果了 [1]。事实胜于雄辩,还是让我们先来看看修改之后的 SELECT 语句吧(代码清单 16)。

    代码清单 16 通过关联子查询按照商品种类对平均销售单价进行比较

    特定的 SQL

    Oracle 中不能使用 AS(会发生错误)。因此,在 Oracle 中执行代码清单 16 时,请大家把 ① 中的 FROM Product AS P1 变为 FROM Product P1,把 ② 中的 FROM Product AS P2 变为 FROM Product P2

    执行结果

    product_type | product_name | sale_price---------------+---------------+------------办公用品  | 打孔器   |  500衣服   | 运动T恤  |  4000厨房用具  | 菜刀   |  3000厨房用具  | 高压锅   |  6800

    这样我们就能选取出办公用品,衣服和厨房用具三类商品中高于该类商品的平均销售单价的商品了。

    这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。

    这次由于作为比较对象的都是同一张 Product 表,因此为了进行区别,分别使用了 P1P2 两个别名。在使用关联子查询时,需要在表所对应的列名之前加上表的别名,以“<表名>.<列名>”的形式记述。

    在对表中某一部分记录的集合进行比较时,就可以使用关联子查询。因此,使用关联子查询时,通常会使用“限定(绑定)”或者“限制”这样的语言,例如本次示例就是限定“商品种类”对平均单价进行比较。

    法则 8

    在细分的组内进行比较时,需要使用关联子查询。

二,关联子查询也是用来对集合进行切分的

换个角度来看,其实关联子查询也和 GROUP BY 子句一样,可以对集合进行切分。

大家还记得我们用来说明 GROUP BY 子句的图(图 6)吗?

图 6 根据商品种类对表进行切分的图示

上图显示了作为记录集合的表是如何按照商品种类被切分的。使用关联子查询进行切分的图示也基本相同(图 7)。

图 7 根据关联子查询进行切分的图示

我们首先需要计算各个商品种类中商品的平均销售单价,由于该单价会用来和商品表中的各条记录进行比较,因此关联子查询实际只能返回 1 行结果。这也是关联子查询不出错的关键。关联子查询执行时,DBMS 内部的执行情况如图 8 所示。

图 8 关联子查询执行时 DBMS 内部的执行情况

如果商品种类发生了变化,那么用来进行比较的平均单价也会发生变化,这样就可以将各种商品的销售单价和平均单价进行比较了。关联子查询的内部执行结果对于初学者来说是比较难以理解的,但是像上图这样将其内部执行情况可视化之后,理解起来就变得非常容易了吧。

三,结合条件一定要写在子查询中

下面给大家介绍一下 SQL 初学者在使用关联子查询时经常犯的一个错误,那就是将关联条件写在子查询之外的外层查询之中。请大家看一下下面这条 SELECT 语句。

上述 SELECT 语句只是将子查询中的关联条件移到了外层查询之中,其他并没有任何更改。但是,该 SELECT 语句会发生错误,不能正确执行。允许存在这样的书写方法可能并不奇怪,但是 SQL 的规则禁止这样的书写方法。

该书写方法究竟违反了什么规则呢?那就是关联名称作用域。虽然这一术语看起来有些晦涩难懂,但是一解释大家就明白了。关联名称就是像 P1P2 这样作为表别名的名称,作用域(scope)就是生存范围(有效范围)。也就是说,关联名称存在一个有效范围的限制。

KEYWORD

  • 关联名称

  • 作用域

具体来讲,子查询内部设定的关联名称,只能在该子查询内部使用(图 9)。换句话说,就是“内部可以看到外部,而外部看不到内部”。

请大家一定不要忘记关联名称具有一定的有效范围。如前所述,SQL 是按照先内层子查询后外层查询的顺序来执行的。这样,子查询执行结束时只会留下执行结果,作为抽出源的 P2 表其实已经不存在了 [2]。因此,在执行外层查询时,由于 P2 表已经不存在了,因此就会返回“不存在使用该名称的表”这样的错误。

图 9 子查询内的关联名称的有效范围

请参阅

  • 视图
  • 子查询
  • 关联子查询

(完)


  1. 事实上,对于代码清单 16 中的 SELECT 语句,即使在子查询中不使用 GROUP BY 子句,也能得到正确的结果。这是因为在 WHERE 子句中追加了“P1.product_type=P2.product_type”这个条件,使得 AVG 函数按照商品种类进行了平均值计算。但是为了跟前面出错的查询进行对比,这里还是加上了 GROUP BY 子句。 ??

  2. 当然,消失的其实只是 P2 这个名称而已,Product 表以及其中的数据还是存在的。 ??




原标题:SQL 关联子查询

关键词:SQL

转载请保留本文网址: http://www.www.d5897.com/a/1270428.html
SQL
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#www.d5897.com (#换成@)。
Baidu