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 Product
  GROUP BY product_type;
  

  但是，如果我們使用前一節（標量子查詢）的方法，直接把上述 SELECT 陳述句使用到 WHERE 子句當中的話，就會發生錯誤，

  -- 發生錯誤的子查詢
  SELECT product_id, product_name, sale_price
  FROM Product
  WHERE 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 表，因此為了進行區別，分別使用了 P1 和 P2 兩個別名，在使用關聯子查詢時，需要在表所對應的列名之前加上表的別名，以“<表名>.<列名>”的形式記述，

  在對表中某一部分記錄的集合進行比較時，就可以使用關聯子查詢，因此，使用關聯子查詢時，通常會使用“限定（系結）”或者“限制”這樣的語言，例如本次示例就是限定“商品種類”對平均單價進行比較，

  法則 8

  在細分的組內進行比較時，需要使用關聯子查詢，

二、關聯子查詢也是用來對集合進行切分的

換個角度來看，其實關聯子查詢也和 GROUP BY 子句一樣，可以對集合進行切分，

大家還記得我們用來說明 GROUP BY 子句的圖（圖 6）嗎？

上圖顯示了作為記錄集合的表是如何按照商品種類被切分的，使用關聯子查詢進行切分的圖示也基本相同（圖 7），

我們首先需要計算各個商品種類中商品的平均銷售單價，由于該單價會用來和商品表中的各條記錄進行比較，因此關聯子查詢實際只能回傳 1 行結果，這也是關聯子查詢不出錯的關鍵，關聯子查詢執行時，DBMS 內部的執行情況如圖 8 所示，

如果商品種類發生了變化，那么用來進行比較的平均單價也會發生變化，這樣就可以將各種商品的銷售單價和平均單價進行比較了，關聯子查詢的內部執行結果對于初學者來說是比較難以理解的，但是像上圖這樣將其內部執行情況可視化之后，理解起來就變得非常容易了吧，

三、結合條件一定要寫在子查詢中

下面給大家介紹一下 SQL 初學者在使用關聯子查詢時經常犯的一個錯誤，那就是將關聯條件寫在子查詢之外的外層查詢之中，請大家看一下下面這條 SELECT 陳述句，

上述 SELECT 陳述句只是將子查詢中的關聯條件移到了外層查詢之中，其他并沒有任何更改，但是，該 SELECT 陳述句會發生錯誤，不能正確執行，允許存在這樣的書寫方法可能并不奇怪，但是 SQL 的規則禁止這樣的書寫方法，

該書寫方法究竟違反了什么規則呢？那就是關聯名稱的作用域，雖然這一術語看起來有些晦澀難懂，但是一解釋大家就明白了，關聯名稱就是像 P1、P2 這樣作為表別名的名稱，作用域（scope）就是生存范圍（有效范圍），也就是說，關聯名稱存在一個有效范圍的限制，

KEYWORD

• 關聯名稱

• 作用域

具體來講，子查詢內部設定的關聯名稱，只能在該子查詢內部使用（圖 9），換句話說，就是“內部可以看到外部，而外部看不到內部”，

請大家一定不要忘記關聯名稱具有一定的有效范圍，如前所述，SQL 是按照先內層子查詢后外層查詢的順序來執行的，這樣，子查詢執行結束時只會留下執行結果，作為抽出源的 P2 表其實已經不存在了 ^[2]，因此，在執行外層查詢時，由于 P2 表已經不存在了，因此就會回傳“不存在使用該名稱的表”這樣的錯誤，

請參閱

• 視圖
• 子查詢
• 關聯子查詢

（完）

標籤：SQL Server

上一篇：SQL 子查詢簡介

下一篇：SQL 中各種各樣的函式