如何使用 SQL 視圖和子查詢進行復雜查詢

• 一、視圖
  • 1.1 視圖和表
  • 1.2 創建視圖的方法
  • 1.3 視圖的限制 ①——定義視圖時不能使用 ORDER BY 子句
  • 1.4 視圖的限制 ② ——對視圖進行更新
  • 1.5 洗掉視圖
• 二、子查詢
  • 2.1 子查詢和視圖
  • 2.2 子查詢的名稱
  • 2.3 標量子查詢
    • 2.3.1 什么是標量
    • 2.3.2 在 WHERE 子句中使用標量子查詢
  • 2.4 標量子查詢的書寫位置
  • 2.5 使用標量子查詢時的注意事項
• 三、關聯子查詢
  • 3.1 普通的子查詢和關聯子查詢的區別
    • 3.1.1 按照商品種類與平均銷售單價進行比較
    • 3.1.2 使用關聯子查詢的解決方案
  • 3.2 關聯子查詢也是用來對集合進行切分的
  • 3.3 結合條件一定要寫在子查詢中

前幾篇我們一起學習了 SQL 如何對表進行創建、更新和洗掉操作、SQL SELECT WHERE 陳述句如何指定一個或多個查詢條件 和 SQL 如何插入、洗掉和更新資料 等資料庫的基本操作方法，

從本文開始，我們將會在這些基本方法的基礎上，學習一些實際應用中的方法，

本文將以此前學過的 SELECT 陳述句，以及嵌套在 SELECT 陳述句中的視圖和子查詢等技術為中心進行學習，由于視圖和子查詢可以像表一樣進行使用，因此如果能恰當地使用這些技術，就可以寫出更加靈活的 SQL 了，

一、視圖

本節重點

• 從 SQL 的角度來看，視圖和表是相同的，兩者的區別在于表中保存的是實際的資料，而視圖中保存的是 SELECT 陳述句（視圖本身并不存盤資料），

• 使用視圖，可以輕松完成跨多表查詢資料等復雜操作，

• 可以將常用的 SELECT 陳述句做成視圖來使用，

• 創建視圖需要使用 CREATE VIEW 陳述句，

• 視圖包含“不能使用 ORDER BY”和“可對其進行有限制的更新”兩項限制，

• 洗掉視圖需要使用 DROP VIEW 陳述句，

1.1 視圖和表

我們首先要學習的是一個新的工具——視圖，

視圖究竟是什么呢？如果用一句話概述的話，就是“從 SQL 的角度來看視圖就是一張表”，

實際上，在 SQL 陳述句中并不需要區分哪些是表，哪些是視圖，只需要知道在更新時它們之間存在一些不同就可以了，這一點之后會為大家進行介紹，

至少在撰寫 SELECT 陳述句時并不需要特別在意表和視圖有什么不同，

那么視圖和表到底有什么不同呢？區別只有一個，那就是“是否保存了實際的資料”，

通常，我們在創建表時，會通過 INSERT 陳述句將資料保存到資料庫之中，而資料庫中的資料實際上會被保存到計算機的存盤設備（通常是硬碟）中，

因此，我們通過 SELECT 陳述句查詢資料時，實際上就是從存盤設備（硬碟）中讀取資料，進行各種計算之后，再將結果回傳給用戶這樣一個程序，

但是使用視圖時并不會將資料保存到存盤設備之中，而且也不會將資料保存到其他任何地方，

實際上視圖保存的是 SELECT 陳述句（圖 1），我們從視圖中讀取資料時，視圖會在內部執行該 SELECT 陳述句并創建出一張臨時表，

• 視圖的優點

  視圖的優點大體有兩點，

  第一點是由于視圖無需保存資料，因此可以節省存盤設備的容量，

  例如，我們在 SQL 如何插入、洗掉和更新資料 中創建了用來匯總商品種類（product_type）的表，

  由于該表中的資料最終都會保存到存盤設備之中，因此會占用存盤設備的資料空間，

  但是，如果把同樣的資料作為視圖保存起來的話，就只需要代碼清單 1 那樣的 SELECT 陳述句就可以了，這樣就節省了存盤設備的資料空間，

  代碼清單 1 通過視圖等 SELECT 陳述句保存資料

  SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
  GROUP BY product_type;
  

  由于本示例中表的資料量充其量只有幾行，所以使用視圖并不會大幅縮小資料的大小，但是在實際的業務中資料量往往非常大，這時使用視圖所節省的容量就會非常可觀了，

  法則 1

  表中存盤的是實際資料，而視圖中保存的是從表中取出資料所使用的 SELECT 陳述句，

  第二個優點就是可以將頻繁使用的 SELECT 陳述句保存成視圖，這樣就不用每次都重新書寫了，

  創建好視圖之后，只需在 SELECT 陳述句中進行呼叫，就可以方便地得到想要的結果了，特別是在進行匯總以及復雜的查詢條件導致 SELECT 陳述句非常龐大時，使用視圖可以大大提高效率，

  而且，視圖中的資料會隨著原表的變化自動更新，視圖歸根到底就是 SELECT 陳述句，所謂“參照視圖”也就是“執行 SELECT 陳述句”的意思，因此可以保證資料的最新狀態，

  這也是將資料保存在表中所不具備的優勢 ^[1]，

  法則 2

  應該將經常使用的 SELECT 陳述句做成視圖，

1.2 創建視圖的方法

創建視圖需要使用 CREATE VIEW 陳述句，其語法如下所示，

語法 1 創建視圖的 CREATE VIEW 陳述句

CREATE VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……)
AS
<SELECT陳述句>

SELECT 陳述句需要書寫在 AS 關鍵字之后，

SELECT 陳述句中列的排列順序和視圖中列的排列順序相同，SELECT 陳述句中的第 1 列就是視圖中的第 1 列，SELECT 陳述句中的第 2 列就是視圖中的第 2 列，以此類推，

視圖的列名在視圖名稱之后的串列中定義，

備忘

接下來，我們將會以此前使用的 Product（商品）表為基礎來創建視圖，

如果大家已經根據之前章節的內容更新了 Product 表中的資料，請在創建視圖之前將資料恢復到初始狀態，操作步驟如下所示，

① 洗掉 Product 表中的資料，將表清空

DELETE FROM Product;

② 執行代碼清單 6（向 Product 表中插入資料）中的 SQL 陳述句，將資料插入到空表 Product 中

下面就讓我們試著來創建視圖吧，和此前一樣，這次我們還是將 Product 表（代碼清單 2）作為基本表，

代碼清單 2 ProductSum 視圖

這樣我們就在資料庫中創建出了一幅名為 ProductSum（商品合計）的視圖，

請大家一定不要省略第 2 行的關鍵字 AS，這里的 AS 與定義別名時使用的 AS 并不相同，如果省略就會發生錯誤，雖然很容易混淆，但是語法就是這么規定的，所以還是請大家牢記，

接下來，我們來學習視圖的使用方法，視圖和表一樣，可以書寫在 SELECT 陳述句的 FROM 子句之中（代碼清單 3），

代碼清單 3 使用視圖

執行結果：

 product_type | cnt_product
--------------+------------
 衣服         |           2
 辦公用品     |           2
 廚房用具     |           4

通過上述視圖 ProductSum 定義的主體（SELECT 陳述句）我們可以看出，該視圖將根據商品種類（product_type）匯總的商品數量（cnt_product）作為結果保存了起來，

這樣如果大家在作業中需要頻繁進行匯總時，就不用每次都使用 GROUP BY 和 COUNT 函式寫 SELECT 陳述句來從 Product 表中取得資料了，

創建出視圖之后，就可以通過非常簡單的 SELECT 陳述句，隨時得到想要的匯總結果，并且如前所述，Product 表中的資料更新之后，視圖也會自動更新，非常靈活方便，

之所以能夠實作上述功能，是因為視圖就是保存好的 SELECT 陳述句，

定義視圖時可以使用任何 SELECT 陳述句，既可以使用 WHERE、GROUP BY、HAVING，也可以通過 SELECT * 來指定全部列，

• 使用視圖的查詢

  在 FROM 子句中使用視圖的查詢，通常有如下兩個步驟：

  ① 首先執行定義視圖的 SELECT 陳述句

  ② 根據得到的結果，再執行在 FROM 子句中使用視圖的 SELECT 陳述句

  也就是說，使用視圖的查詢通常需要執行 2 條以上的 SELECT 陳述句 ^[2]，

  這里沒有使用“2 條”而使用了“2 條以上”，是因為還可能出現以視圖為基礎創建視圖的多重視圖（圖 2），

  例如，我們可以像代碼清單 4 那樣以 ProductSum 為基礎創建出視圖 ProductSumJim，

  

  代碼清單 4 視圖 ProductSumJim

  

  -- 確認創建好的視圖
  SELECT product_type, cnt_product
  FROM ProductSumJim;
  

  執行結果：

  product_type | cnt_product
  --------------+------------
  辦公用品     |           2
  

  雖然語法上沒有錯誤，但是我們還是應該盡量避免在視圖的基礎上創建視圖，這是因為對多數 DBMS 來說，多重視圖會降低 SQL 的性能，因此，希望大家（特別是剛剛接觸視圖的讀者）能夠使用單一視圖，

  法則 3

  應該避免在視圖的基礎上創建視圖，

  除此之外，在使用時還要注意視圖有兩個限制，接下來會給大家詳細介紹，

1.3 視圖的限制 ①——定義視圖時不能使用 ORDER BY 子句

雖然之前我們說過在定義視圖時可以使用任何 SELECT 陳述句，但其實有一種情況例外，那就是不能使用 ORDER BY 子句，因此下述視圖定義陳述句是錯誤的，

為什么不能使用 ORDER BY 子句呢？這是因為視圖和表一樣，資料行都是沒有順序的，

實際上，有些 DBMS 在定義視圖的陳述句中是可以使用 ORDER BY 子句的 ^[3]，但是這并不是通用的語法，因此，在定義視圖時請不要使用 ORDER BY 子句，

法則 4

定義視圖時不要使用 ORDER BY 子句，

1.4 視圖的限制 ② ——對視圖進行更新

之前我們說過，在 SELECT 陳述句中視圖可以和表一樣使用，那么，對于 INSERT、DELETE、UPDATE 這類更新陳述句（更新資料的 SQL）來說，會怎么樣呢？

實際上，雖然這其中有很嚴格的限制，但是某些時候也可以對視圖進行更新，標準 SQL 中有這樣的規定：如果定義視圖的 SELECT 陳述句能夠滿足某些條件，那么這個視圖就可以被更新，

下面就給大家列舉一些比較具有代表性的條件，

① SELECT 子句中未使用 DISTINCT

② FROM 子句中只有一張表

③ 未使用 GROUP BY 子句

④ 未使用 HAVING 子句

在前幾章的例子中，FROM 子句里通常只有一張表，因此，大家可能會覺得 ② 中的條件有些奇怪，但其實 FROM 子句中也可以并列使用多張表，大家在學習完 SQL 如何使用行內結、外聯結和交叉聯結 的操作之后就明白了，

其他的條件大多數都與聚合有關，簡單來說，像這次的例子中使用的 ProductSum 那樣，使用視圖來保存原表的匯總結果時，是無法判斷如何將視圖的更改反映到原表中的，

例如，對 ProductSum 視圖執行如下 INSERT 陳述句，

INSERT INTO ProductSum VALUES ('電器制品', 5);

但是，上述 INSERT 陳述句會發生錯誤，這是因為視圖 ProductSum 是通過 GROUP BY 子句對原表進行匯總而得到的，為什么通過匯總得到的視圖不能進行更新呢？

視圖歸根結底還是從表派生出來的，因此，如果原表可以更新，那么視圖中的資料也可以更新，反之亦然，如果視圖發生了改變，而原表沒有進行相應更新的話，就無法保證資料的一致性了，

使用前述 INSERT 陳述句，向視圖 ProductSum 中添加資料 ('電器制品',5) 時，原表 Product 應該如何更新才好呢？按理說應該向表中添加商品種類為“電器制品”的 5 行資料，但是這些商品對應的商品編號、商品名稱和銷售單價等我們都不清楚（圖 3），資料庫在這里就遇到了麻煩，

法則 5

視圖和表需要同時進行更新，因此通過匯總得到的視圖無法進行更新，

• 能夠更新視圖的情況

  像代碼清單 5 這樣，不是通過匯總得到的視圖就可以進行更新，

  代碼清單 5 可以更新的視圖

  

  對于上述只包含辦公用品類商品的視圖 ProductJim 來說，就可以執行類似代碼清單 6 這樣的 INSERT 陳述句，

  代碼清單 6 向視圖中添加資料行

  

注意事項

由于 PostgreSQL 中的視圖會被初始設定為只讀，所以執行代碼清單 6 中的 INSERT 陳述句時，會發生下面這樣的錯誤：

ERROR:  不能向視圖中插入資料
HINT:  需要一個無條件的ON INSERT DO INSTEAD規則

這種情況下，在 INSERT 陳述句執行之前，需要使用代碼清單 A 中的指令來允許更新操作，在 DB2 和 MySQL 等其他 DBMS 中，并不需要執行這樣的指令，

代碼清單 A 允許 PostgreSQL 對視圖進行更新

PostgreSQL

CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO  ProductJim DO INSTEAD
INSERT INTO Product VALUES (
           new.product_id,
           new.product_name,
           new.product_type,
           new.sale_price,
           new.purchase_price,
           new.regist_date);

下面讓我們使用 SELECT 陳述句來確認資料行是否添加成功吧，

• 視圖

  -- 確認資料是否已經添加到視圖中
  SELECT * FROM ProductJim;
  

  執行結果：

  

• 原表

  -- 確認資料是否已經添加到原表中
  SELECT * FROM Product;
  

  執行結果：

  

UPDATE 陳述句和 DELETE 陳述句當然也可以像操作表時那樣正常執行，但是對于原表來說卻需要設定各種各樣的約束（主鍵和 NOT NULL 等），需要特別注意，

1.5 洗掉視圖

洗掉視圖需要使用 DROP VIEW 陳述句，其語法如下所示，

語法 2 洗掉視圖的 DROP VIEW 陳述句

DROP VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……)

例如，想要洗掉視圖 ProductSum 時，就可以使用代碼清單 7 中的 SQL 陳述句，

代碼清單 7 洗掉視圖

DROP VIEW ProductSum;

特定的 SQL

在 PostgreSQL 中，如果洗掉以視圖為基礎創建出來的多重視圖，由于存在關聯的視圖，因此會發生如下錯誤：

ERROR:   由于存在關聯視圖，因此無法洗掉視圖productsum
DETAIL:  視圖productsumjim與視圖productsum相關聯
HINT:    洗掉關聯物件請使用DROP…CASCADE

這時可以像下面這樣，使用 CASCADE 選項來洗掉關聯視圖：

DROP VIEW ProductSum CASCADE;

備忘

下面我們再次將 Product 表恢復到初始狀態（8 行），請執行如下 DELETE 陳述句，洗掉之前添加的 1 行資料，

-- 洗掉商品編號為0009（印章）的資料
DELETE FROM Product WHERE product_id = '0009';

二、子查詢

本節重點

• 一言以蔽之，子查詢就是一次性視圖（SELECT 陳述句），與視圖不同，子查詢在 SELECT 陳述句執行完畢之后就會消失，

• 由于子查詢需要命名，因此需要根據處理內容來指定恰當的名稱，

• 標量子查詢就是只能回傳一行一列的子查詢，

2.1 子查詢和視圖

前一節我們學習了視圖這個非常方便的工具，本節將學習以視圖為基礎的子查詢，子查詢的特點概括起來就是一張一次性視圖，

我們先來復習一下視圖的概念，視圖并不是用來保存資料的，而是通過保存讀取資料的 SELECT 陳述句的方法來為用戶提供便利，

反之，子查詢就是將用來定義視圖的 SELECT 陳述句直接用于 FROM 子句當中，

接下來，就讓我們拿前一節使用的視圖 ProductSum（商品合計）來與子查詢進行一番比較吧，

首先，我們再來看一下視圖 ProductSum 的定義和視圖所對應的 SELECT 陳述句（代碼清單 8），

代碼清單 8 視圖 ProductSum 和確認用的 SELECT 陳述句

-- 根據商品種類統計商品數量的視圖
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

-- 確認創建好的視圖
SELECT product_type, cnt_product
  FROM ProductSum;

能夠實作同樣功能的子查詢如代碼清單 9 所示，

代碼清單 9 子查詢

特定的 SQL

在 Oracle 的 FROM 子句中，不能使用 AS（會發生錯誤），因此，在 Oracle 中執行代碼清單 9 時，需要將 ① 中的“) AS ProductSum;”變為“) ProductSum;”

兩種方法得到的結果完全相同，

執行結果：

 product_type | cnt_product
--------------+------------
 衣服         |           2
 辦公用品     |           2
 廚房用具     |           4

如上所示，子查詢就是將用來定義視圖的 SELECT 陳述句直接用于 FROM 子句當中，

雖然“AS ProductSum”就是子查詢的名稱，但由于該名稱是一次性的，因此不會像視圖那樣保存在存盤介質（硬碟）之中，而是在 SELECT 陳述句執行之后就消失了，

實際上，該 SELECT 陳述句包含嵌套的結構，首先會執行 FROM 子句中的 SELECT 陳述句，然后才會執行外層的 SELECT 陳述句（圖 4），

① 首先執行 FROM 子句中的 SELECT 陳述句（子查詢）

SELECT product_type, COUNT(*) AS cnt_product
  FROM Product
 GROUP BY product_type;

② 根據 ① 的結果執行外層的 SELECT 陳述句

SELECT product_type, cnt_product
  FROM ProductSum;

法則 6

子查詢作為內層查詢會首先執行，

• 增加子查詢的層數

  由于子查詢的層數原則上沒有限制，因此可以像“子查詢的 FROM 子句中還可以繼續使用子查詢，該子查詢的 FROM 子句中還可以再使用子查詢……”這樣無限嵌套下去（代碼清單 10），

  代碼清單 10 嘗試增加子查詢的嵌套層數

  SQL Server DB2 PostgreSQL MySQL

  SELECT product_type, cnt_product
  FROM (SELECT *
          FROM (SELECT product_type, COUNT(*) AS cnt_product
                  FROM Product
                  GROUP BY product_type) AS ProductSum -----①
          WHERE cnt_product = 4) AS ProductSum2; -----------②
  

  特定的 SQL

  在 Oracle 的 FROM 子句中不能使用 AS（會發生錯誤），因此，在 Oracle 中執行代碼清單 10 時，需要將 ① 中的“) AS ProductSum”變為“) ProductSum”，將 ② 中的“) AS ProductSum2;”變為“) ProductSum2;”，

  執行結果：

  product_type | cnt_product
  --------------+------------
  廚房用具     |           4
  

  最內層的子查詢（ProductSum）與之前一樣，根據商品種類（product_type）對資料進行匯總，其外層的子查詢將商品數量（cnt_product）限定為 4，結果就得到了 1 行廚房用具的資料，

  但是，隨著子查詢嵌套層數的增加，SQL 陳述句會變得越來越難讀懂，性能也會越來越差，因此，請大家盡量避免使用多層嵌套的子查詢，

2.2 子查詢的名稱

之前的例子中我們給子查詢設定了 ProductSum 等名稱，原則上子查詢必須設定名稱，因此請大家盡量從處理內容的角度出發為子查詢設定恰當的名稱，

在上述例子中，子查詢用來對 Product 表的資料進行匯總，因此我們使用了后綴 Sum 作為其名稱，

為子查詢設定名稱時需要使用 AS 關鍵字，該關鍵字有時也可以省略 ^[4]，

2.3 標量子查詢

接下來我們學習子查詢中的標量子查詢（scalar subquery），

2.3.1 什么是標量

標量就是單一的意思，在資料庫之外的領域也經常使用，

上一節我們學習的子查詢基本上都會回傳多行結果（雖然偶爾也會只回傳 1 行資料），由于結構和表相同，因此也會有查詢不到結果的情況，

而標量子查詢則有一個特殊的限制，那就是必須而且只能回傳 1 行 1 列的結果，也就是回傳表中某一行的某一列的值，例如“10”或者“東京都”這樣的值，

法則 7

標量子查詢就是回傳單一值的子查詢，

細心的讀者可能會發現，由于回傳的是單一的值，因此標量子查詢的回傳值可以用在 = 或者 <> 這樣需要單一值的比較運算子之中，這也正是標量子查詢的優勢所在，下面就讓我們趕快來試試看吧，

2.3.2 在 WHERE 子句中使用標量子查詢

在 SQL 如何插入、洗掉和更新資料 中，我們練習了通過各種各樣的條件從 Product（商品）表中讀取資料，大家有沒有想過通過下面這樣的條件查詢資料呢？

“查詢出銷售單價高于平均銷售單價的商品，”

或者說想知道價格處于上游的商品時，也可以通過上述條件進行查詢，

然而這并不是用普通方法就能解決的，如果我們像下面這樣使用 AVG 函式的話，就會發生錯誤，

雖然這樣的 SELECT 陳述句看上去能夠滿足我們的要求，但是由于在 WHERE 子句中不能使用聚合函式，因此這樣的 SELECT 陳述句是錯誤的，

那么究竟什么樣的 SELECT 陳述句才能滿足上述條件呢？

這時標量子查詢就可以發揮它的功效了，首先，如果想要求出 Product 表中商品的平均銷售單價（sale_price），可以使用代碼清單 11 中的 SELECT 陳述句，

代碼清單 11 計算平均銷售單價的標量子查詢

SELECT AVG(sale_price)
  FROM Product;

執行結果：

         avg
----------------------
2097.5000000000000000

AVG 函式的使用方法和 COUNT 函式相同，其計算式如下所示，

(1000+500+4000+3000+6800+500+880+100) / 8 = 2097.5

這樣計算出的平均單價大約就是 2100 元，不難發現，代碼清單 11 中的 SELECT 陳述句的查詢結果是單一的值（2097.5），

因此，我們可以直接將這個結果用到之前失敗的查詢之中，正確的 SQL 如代碼清單 12 所示，

代碼清單 12 選取出銷售單價（sale_price）高于全部商品的平均單價的商品

執行結果：

 product_id | product_name | sale_price
------------+--------------+-----------
 0003       | 運動T恤      |       4000
 0004       | 菜刀         |       3000
 0005       | 高壓鍋       |       6800

前一節我們已經介紹過，使用子查詢的 SQL 會從子查詢開始執行，因此，這種情況下也會先執行下述計算平均單價的子查詢（圖 5），

-- ① 內層的子查詢
SELECT AVG(sale_price)
  FROM Product ；

子查詢的結果是 2097.5，因此會用該值替換子查詢的部分，生成如下 SELECT 陳述句，

-- ② 外層的查詢
SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > 2097.5

大家都能看出該 SQL 沒有任何問題可以正常執行，結果如上所述，

2.4 標量子查詢的書寫位置

標量子查詢的書寫位置并不僅僅局限于 WHERE 子句中，通常任何可以使用單一值的位置都可以使用，也就是說，能夠使用常數或者列名的地 方，無論是 SELECT 子句、GROUP BY 子句、HAVING 子句，還是 ORDER BY 子句，幾乎所有的地方都可以使用，

例如，在 SELECT 子句當中使用之前計算平均值的標量子查詢的 SQL 陳述句，如代碼清單 13 所示，

代碼清單 13 在 SELECT 子句中使用標量子查詢

執行結果：

 product_id | product_name  | sale_price |       avg_price
------------+---------------+------------+----------------------
 0001       | T恤衫         |       1000 | 2097.5000000000000000
 0002       | 打孔器        |        500 | 2097.5000000000000000
 0003       | 運動T恤       |       4000 | 2097.5000000000000000
 0004       | 菜刀          |       3000 | 2097.5000000000000000
 0005       | 高壓鍋        |       6800 | 2097.5000000000000000
 0006       | 叉子          |        500 | 2097.5000000000000000
 0007       | 擦菜板        |        880 | 2097.5000000000000000
 0008       | 圓珠筆        |        100 | 2097.5000000000000000

從上述結果可以看出，在商品一覽表中加入了全部商品的平均單價，有時我們會需要這樣的單據，

此外，我們還可以像代碼清單 14 中的 SELECT 陳述句那樣，在 HAVING 子句中使用標量子查詢，

代碼清單 14 在 HAVING 子句中使用標量子查詢

執行結果：

 product_type |         avg
--------------+----------------------
 衣服         | 2500.0000000000000000
 廚房用具     | 2795.0000000000000000

該查詢的含義是想要選取出按照商品種類計算出的銷售單價高于全部商品的平均銷售單價的商品種類，

如果在 SELECT 陳述句中不使用 HAVING 子句的話，那么平均銷售單價為 300 元的辦公用品也會被選取出來，

但是，由于全部商品的平均銷售單價是 2097.5 元，因此低于該平均值的辦公用品會被 HAVING 子句中的條件排除在外，

2.5 使用標量子查詢時的注意事項

最后我們來介紹一下使用標量子查詢時的注意事項，那就是該子查詢絕對不能回傳多行結果，

也就是說，如果子查詢回傳了多行結果，那么它就不再是標量子查詢，而僅僅是一個普通的子查詢了，因此不能被用在 = 或者 <> 等需要單一輸入值的運算子當中，也不能用在 SELECT 等子句當中，

例如，如下的 SELECT 子查詢會發生錯誤，

發生錯誤的原因很簡單，就是因為會回傳如下多行結果：

         avg
----------------------
2500.0000000000000000
 300.0000000000000000
2795.0000000000000000

在 1 行 SELECT 子句之中當然不可能使用 3 行資料，因此，上述 SELECT 陳述句會回傳“因為子查詢回傳了多行資料所以不能執行”這樣的錯誤資訊 ^[5]，

三、關聯子查詢

本節重點

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

• 關聯子查詢和 GROUP BY 子句一樣，也可以對表中的資料進行切分，

• 關聯子查詢的結合條件如果未出現在子查詢之中就會發生錯誤，

3.1 普通的子查詢和關聯子查詢的區別

按此前所學，使用子查詢就能選取出銷售單價（sale_price）高于全部商品平均銷售單價的商品，

這次我們稍稍改變一下條件，選取出各商品種類中高于該商品種類的平均銷售單價的商品，

3.1.1 按照商品種類與平均銷售單價進行比較

只通過語言描述可能難以理解，還是讓我們來看看具體示例吧，我們以廚房用具中的商品為例，該分組中包含了表 1 所示的 4 種商品，

表 1 廚房用具中的商品

因此，計算上述 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 子句中使用子查詢時，該子查詢的結果必須是單一的，

但是，如果以商品種類分組為單位，對銷售單價和平均單價進行比較，除此之外似乎也沒有其他什么辦法了，到底應該怎么辦才好呢？

3.1.2 使用關聯子查詢的解決方案

這時就輪到我們的好幫手——關聯子查詢登場了，

只需要在剛才的 SELECT 陳述句中追加一行，就能得到我們想要的結果了 ^[6]，事實勝于雄辯，還是讓我們先來看看修改之后的 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

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

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

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

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

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

我們首先需要計算各個商品種類中商品的平均銷售單價，由于該單價會用來和商品表中的各條記錄進行比較，因此關聯子查詢實際只能回傳 1 行結果，

這也是關聯子查詢不出錯的關鍵，關聯子查詢執行時，DBMS 內部的執行情況如圖 8 所示，

如果商品種類發生了變化，那么用來進行比較的平均單價也會發生變化，這樣就可以將各種商品的銷售單價和平均單價進行比較了，

關聯子查詢的內部執行結果對于初學者來說是比較難以理解的，但是像上圖這樣將其內部執行情況可視化之后，理解起來就變得非常容易了吧，

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

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

上述 SELECT 陳述句只是將子查詢中的關聯條件移到了外層查詢之中，其他并沒有任何更改，但是，該 SELECT 陳述句會發生錯誤，不能正確執行，

允許存在這樣的書寫方法可能并不奇怪，但是 SQL 的規則禁止這樣的書寫方法，

該書寫方法究竟違反了什么規則呢？那就是關聯名稱的作用域，雖然這一術語看起來有些晦澀難懂，但是一解釋大家就明白了，

關聯名稱就是像 P1、P2 這樣作為表別名的名稱，作用域（scope）就是生存范圍（有效范圍），也就是說，關聯名稱存在一個有效范圍的限制，

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

請大家一定不要忘記關聯名稱具有一定的有效范圍，

如前所述，SQL 是按照先內層子查詢后外層查詢的順序來執行的，這樣，子查詢執行結束時只會留下執行結果，作為抽出源的 P2 表其實已經不存在了 ^[7]，

因此，在執行外層查詢時，由于 P2 表已經不存在了，因此就會回傳“不存在使用該名稱的表”這樣的錯誤，

原文鏈接：https://www.developerastrid.com/sql/sql-view-subqueries/

（完）

標籤：SQL Server

上一篇：什么是 SQL 事務，如何創建 SQL 事務

下一篇：SQL 常用的函式：算術函式、字串函式、日期函式和轉換函式