3.9 SQL Server 子查詢 · 軟件測試

>[danger] ## [子查詢]- 介紹子查詢概念并向您展示如何使用各種子查詢類型來查詢數據。 #### 1\. SQL Server子查詢簡介子查詢是嵌套在另一個語句(如：SELECT，INSERT，UPDATE或DELETE中的查詢。現在來看下面的例子，考慮示例數據庫中的`orders` 和 `customers`表，它們的結構和關系如下： ![](https://www.yiibai.com/uploads/article/2019/02/21/105721_78873.png) 以下語句顯示如何在`SELECT`語句的WHERE子句中使用子查詢來查找位于紐約(`New York`)的客戶的銷售訂單： ~~~sql SELECT order_id, order_date, customer_id FROM sales.orders WHERE customer_id IN ( SELECT customer_id FROM sales.customers WHERE city = 'New York' ) ORDER BY order_date DESC; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/03/2e/032e9fa2cc3c261df0912e535dfa558f_1386x710.png) 在此示例中，以下語句是子查詢： ~~~sql SELECT customer_id FROM sales.customers WHERE city = 'New York' ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/cf/d7/cfd78bd644a0cd5dab983512df7cbc4f_1386x711.png) 請注意，必須始終將子查詢的`SELECT`查詢括在括號`()`中。子查詢也稱為內部查詢或內部選擇，而包含子查詢的語句稱為外部選擇或外部查詢： ![SQL Server子查詢](https://www.yiibai.com/uploads/article/2019/02/21/110434_49562.png) SQL Server執行上面的整個查詢示例，如下所示：首先，它執行子查詢以獲取城市為`New Year`的客戶的客戶標識號列表。 ~~~sql SELECT customer_id FROM sales.customers WHERE city = 'New York' ~~~ 其次，SQL Server替換[IN](http://www.yiibai.com/sqlserver/sql-server-in.html "IN")運算符中子查詢返回的客戶標識號，并執行外部查詢以獲取最終結果集。如您所見，通過使用子查詢，可以將兩個步驟組合在一起。子查詢消除了選擇客戶標識號并將其插入外部查詢的需要。此外，只要客戶數據發生變化，查詢本身就會自動進行調整。 #### 2\. 嵌套子查詢子查詢可以嵌套在另一個子查詢中。 SQL Server最多支持`32`個嵌套級別。請考慮以下示例： ~~~sql SELECT product_name, list_price FROM production.products WHERE list_price > ( SELECT AVG (list_price) FROM production.products WHERE brand_id IN ( SELECT brand_id FROM production.brands WHERE brand_name = '上海永久' OR brand_name = '鳳凰' ) ) ORDER BY list_price; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/f8/b5/f8b5beb916402979c891618ffe5100b4_1391x712.png) 上面語句有些復雜，如果第一眼沒有看明白，沒有關系，可通過以下步驟一步步地理解。 **首先**，SQL Server執行以下子查詢以獲取品牌名稱為`'上海永久'`和`'鳳凰'`的品牌標識號列表： ~~~sql SELECT brand_id FROM production.brands WHERE brand_name = '上海永久' OR brand_name = '鳳凰'; ~~~ 執行上面查詢語句，得到以下結果： ![](https://www.yiibai.com/uploads/article/2019/02/21/111813_90306.png) **第二步**，SQL Server計算屬于這些品牌的所有產品的平均價格。 ~~~sql SELECT AVG (list_price) FROM production.products WHERE brand_id IN (1,2) ~~~ **第三步**，SQL Server查找價格高于`'上海永久'`和`'鳳凰'`品牌的所有產品的平均定價的產品。 #### 3\. SQL Server子查詢類型可以在許多地方使用子查詢： * 代替表達 * 使用IN或NOT IN * ANY或ALL * 有EXISTS或`NOT EXISTS`語句中。 * 在UPDATE，DELETE或INSERT語句中。 **3.1. SQL Server子查詢用于代替表達式** 如果子查詢返回單個值，則可以在使用表達式的任何位置使用它。 ~~~sql SELECT order_id, order_date, ( SELECT MAX (list_price) FROM sales.order_items i WHERE i.order_id = o.order_id ) AS max_list_price FROM sales.orders o order by order_date desc; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/c5/71/c5719c575fb1b6cb927056d847783504_1392x713.png) **3.2. SQL Server子查詢與IN運算符** 與`IN`運算符一起使用的子查詢返回一組零個或多個值。子查詢返回值后，外部查詢將使用它們。以下查詢查找出售的所有山地自行車和公路自行車產品的名稱。 ~~~sql SELECT product_id, product_name FROM production.products WHERE category_id IN ( SELECT category_id FROM production.categories WHERE category_name = 'Mountain Bikes' OR category_name = 'Road Bikes' ); ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/f4/0f/f40fa845077f23d15ebe9f2e2f1d68de_640x527.png) 此查詢分兩步進行評估： * 首先，內部查詢返回與名稱`Mountain Bikes`和`Road Bikes`相匹配的類別標識號列表。 * 其次，這些值被替換為外部查詢，外部查詢查找具有類別標識號與列表中的一個值匹配的產品名稱。 **3.2. SQL Server子查詢與ANY運算符一起使用** 使用`ANY`運算符引入子查詢的語法： ~~~sql scalar_expression comparison_operator ANY (subquery) ~~~ 假設子查詢返回值為：`v1`，`v2`，`... vn`的列表。如果比較`scalar_expression`中的一個評估為`TRUE`，則`ANY`運算符返回`TRUE`; 否則，它返回`FALSE`。例如，以下查詢查找價格大于或等于任何產品品牌的平均價格的產品。 ~~~sql SELECT product_name, list_price FROM production.products WHERE list_price >= ANY ( SELECT AVG (list_price) FROM production.products GROUP BY brand_id ) ~~~ 執行上面示例代碼，得到以下結果：子查詢與ANY運算符一起使用對于每個品牌，子查詢都會找到平均價格。外部查詢使用這些最大價格并確定哪個單獨產品的清單價格大于或等于任何品牌的平均價格。 **SQL Server子查詢與ALL運算符一起使用** `ALL`運算符與`ANY`運算符具有相同的語法： ~~~sql scalar_expression comparison_operator ALL (subquery) ~~~ 如果所有比較`scalar_expression`的計算結果為`TRUE`，則`ALL`運算符返回`TRUE`; 否則，它返回`FALSE`。以下查詢查找列表價格大于或等于子查詢返回的平均價格的產品： ~~~sql SELECT product_name, list_price FROM production.products WHERE list_price >= ALL ( SELECT AVG (list_price) FROM production.products GROUP BY brand_id ) ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/7f/f1/7ff1b76f7d4dc8c6d4c127f3a536da21_1391x713.png) **3.3. SQL Server子查詢與EXISTS或NOT EXISTS一起使用** 以下語句使用EXISTS運算符引入的子查詢的語法： ~~~sql WHERE [NOT] EXISTS (subquery) ~~~ 如果子查詢返回結果，則`EXISTS`運算符返回`TRUE`; 否則返回`FALSE`。另一方面，`NOT EXISTS`與`EXISTS`運算符相反。以下查詢查找`2017`年購買產品的客戶： ~~~sql SELECT customer_id, first_name, last_name, city FROM sales.customers c WHERE EXISTS ( SELECT customer_id FROM sales.orders o WHERE o.customer_id = c.customer_id AND YEAR (order_date) = 2017 ) ORDER BY first_name, last_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/98/23/98230e5f0d4976f7b2fc2f4d34f284b7_1392x711.png) 如果使用`NOT EXISTS`，可以查找`2017`年未購買任何產品的客戶。 ~~~sql SELECT customer_id, first_name, last_name, city FROM sales.customers c WHERE NOT EXISTS ( SELECT customer_id FROM sales.orders o WHERE o.customer_id = c.customer_id AND YEAR (order_date) = 2017 ) ORDER BY first_name, last_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/8c/d1/8cd1b51b9d222e23794caaac6950e644_1389x712.png) >[danger] ## [相關子查詢] - 介紹相關子查詢概念以及如何使用。相關子查詢是使用外部查詢的值的[子查詢](http://www.yiibai.com/sqlserver/sql-server-subquery.html "子查詢")。換句話說，它取決于外部查詢的值。由于這種依賴性，相關子查詢不能作為簡單子查詢獨立執行。此外，對外部查詢評估的每一行重復執行一次相關子查詢。相關子查詢也稱為**重復子查詢**。請考慮示例數據庫中的以下`products`表： ![](https://www.yiibai.com/uploads/article/2019/02/21/185730_30277.png) 以下示例查找價格等于其類別的最高價格的產品。 ~~~sql SELECT product_name, list_price, category_id FROM production.products p1 WHERE list_price IN ( SELECT MAX (p2.list_price) FROM production.products p2 WHERE p2.category_id = p1.category_id GROUP BY p2.category_id ) ORDER BY category_id, product_name; ~~~ 執行上面查詢語句，得到以下結果: ![](https://img.kancloud.cn/9d/25/9d25d389799ccc5e2eaf60cfbfa1963d_1387x707.png) 在此示例中，對于由外部查詢評估的每個產品，子查詢查找其類別中所有產品的最高價格。如果當前產品的價格等于其類別中所有產品的最高價格，則產品將包含在結果集中。此過程將繼續進行下一個產品，依此類推。 >[danger] ## [EXISTS] - 測試子查詢返回的行的存在性。 **1\. 帶子查詢的EXISTS返回NULL示例** 請參閱示例數據庫中的`customers`表。 Exists返回NULL示例以下示例返回`customers`表中的所有行： ~~~sql SELECT customer_id, first_name, last_name FROM sales.customers WHERE EXISTS (SELECT NULL) ORDER BY first_name, last_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/32/46/324608fcec4ee081efa6b1b337fa8d8e_1387x717.png) 在此示例中，子查詢返回包含`NULL`的結果集，這也導致`EXISTS`運算符計算為`TRUE`。 **1.2. EXISTS帶有相關子查詢示例** 考慮以下`customers`和`orders`表，它們的結構如下所示： ![](https://www.yiibai.com/uploads/article/2019/02/21/191511_86408.png) 以下示例查找已下過兩個以上訂單的所有客戶： ~~~sql SELECT customer_id, first_name, last_name FROM sales.customers c WHERE EXISTS ( SELECT COUNT (*) FROM sales.orders o WHERE customer_id = c.customer_id GROUP BY customer_id HAVING COUNT (*) > 2 ) ORDER BY first_name, last_name; ~~~ 執行上面查詢語句，得到以下結果： EXISTS帶有相關子查詢示例在這個例子中，我們有一個相關的子查詢，它返回下過兩個以上訂單的客戶。如果客戶下達的訂單數小于或等于`2`，則子查詢返回一個空結果集，該結果集導致`EXISTS`運算符計算為`FALSE`。根據`EXISTS`運算符的結果，客戶是否包含在結果集中。 **1.3. EXISTS 與 IN示例** 以下語句使用`IN`運算符查找城市為`San Jose`的客戶訂單： ~~~sql SELECT * FROM sales.orders WHERE customer_id IN ( SELECT customer_id FROM sales.customers WHERE city = 'San Jose' ) ORDER BY customer_id, order_date; ~~~ 以下語句使用返回相同結果的`EXISTS`運算符： ~~~sql SELECT * FROM sales.orders o WHERE EXISTS ( SELECT customer_id FROM sales.customers c WHERE o.customer_id = c.customer_id AND city = 'San Jose' ) ORDER BY o.customer_id, order_date; ~~~ 執行上面查詢語句，得到以下結果： EXISTS 與 IN示例 **1.4. EXISTS與JOIN** `JOIN`子句從另一個表返回行記錄，`EXISTS`運算符返回`TRUE`或`FALSE`。可以使用`EXISTS`運算符來測試子查詢是否返回行，并盡快進行短路。另一方面，使用`JOIN`將結果集與另一個相關表中的列組合來擴展結果集。 >[danger] ## [ANY]- 將值與子查詢返回的單列值集進行比較，如果值與集合中的任何值匹配則并返回`TRUE`。 ## SQL Server ANY運算符簡介 `ANY`運算符是一個邏輯運算符，它將標量值與子查詢返回的單列值集進行比較。以下是`ANY`運算符的語法： ~~~sql scalar_expression comparison_operator ANY (subquery) ~~~ 在上面語法中， * `scalar_expression` - 是任何有效的表達式。 * `comparison_operator` - 是任何比較運算符。 * `subquery`是一個SELECT語句，它返回單個列的結果集，其數據與標量表達式的數據類型相同。假設子查詢返回值列表`v1，v2，...，vn`。如果`ANY`比較(`scalar_expression，vi`)返回`TRUE`，則`ANY`運算符返回`TRUE`。否則它返回`FALSE`。請注意，`SOME`運算符等效于`ANY`運算符。 ## SQL Server ANY運算符示例請參閱示例數據庫中的以下`products`表，結構如下所示： ![](https://www.yiibai.com/uploads/article/2019/02/21/213633_57820.png) 以下示例查找銷售訂單中銷售數量超過`2`個的產品： ~~~sql SELECT product_name, list_price FROM production.products WHERE product_id = ANY ( SELECT product_id FROM sales.order_items WHERE quantity >= 2 ) ORDER BY product_name; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/05/31/0531e9ea135a908a7a22e9aff594ef87_1387x708.png) >[danger] ## [ALL]- 將值與子查詢返回的單列值集進行比較，如果值與集合中的所有值匹配并返回`TRUE`。 SQL Server `ALL`運算符是一個邏輯運算符，它將標量值與子查詢返回的單列值列表進行比較。以下是`ALL`運算符語法： ~~~sql scalar_expression comparison_operator ALL ( subquery) ~~~ 在上面語法中， * `scalar_expression`是任何有效的表達式。 * `comparison_operator`是任何有效的比較運算符，包括等于(`=`)，不等于(`<>`)，大于(`>`)，大于或等于(`>=`)，小于(`<`)，小于或等于(`<=`)。 * 括號內的子查詢(`subquery`)是一個[SELECT](http://www.yiibai.com/sqlserver/sql-server-select.html "SELECT")語句，它返回單個列的結果。此外，返回列的數據類型必須與標量表達式的數據類型相同。如果所有比較對`(scalar_expression，v)`的計算結果為`TRUE`，則`ALL`運算符返回`TRUE`; `v`是單列結果中的值。如果其中一對`(scalar_expression，v)`返回`FALSE`，則`ALL`運算符返回`FALSE`。 ## SQL Server ALL運算符示例請考慮[示例數據庫](https://www.yiibai.com/sqlserver/sql-server-sample-database.html "示例數據庫")中的以下`products`表。 ![](https://www.yiibai.com/uploads/article/2019/02/22/080928_81734.png) 以下查詢語句返回每個品牌的產品平均價格： ~~~sql SELECT AVG (list_price) avg_list_price FROM production.products GROUP BY brand_id ORDER BY avg_list_price; ~~~ 執行上面查詢語句，得到以下結果： ![](https://img.kancloud.cn/5a/ef/5aefd963f435c7030d11b7056be09cd0_641x530.png) **1\. scalar\_expression > ALL ( subquery )** 如果`scalar_expression`大于子查詢返回的最大值，則表達式返回`TRUE`。例如，以下查詢查找價格大于所有品牌產品的平均價格的產品： ~~~sql SELECT product_name, list_price FROM production.products WHERE list_price > ALL ( SELECT AVG (list_price) avg_list_price FROM production.products GROUP BY brand_id ) ORDER BY list_price; ~~~ 執行上面查詢語句，得到以下結果： ![大于所有品牌產品的平均價格](https://www.yiibai.com/uploads/article/2019/02/22/081259_29909.png "大于所有品牌產品的平均價格") **2\. scalar\_expression < ALL ( subquery )** 如果標量表達式(`scalar_expression`)小于子查詢(`subquery`)返回的最小值，則表達式求值為`TRUE`。以下示例按品牌查找價格低于平均價格中最低價格的產品： ~~~sql SELECT product_name, list_price FROM production.products WHERE list_price < ALL ( SELECT AVG (list_price) avg_list_price FROM production.products GROUP BY brand_id ) ORDER BY list_price DESC; ~~~ 執行上面查詢語句，得到以下結果： ![低于平均價格中最低價格](https://www.yiibai.com/uploads/article/2019/02/22/081512_91044.png "低于平均價格中最低價格") 類似地，也可以使用以下比較運算符之一來使用`ALL`運算符，例如等于(`=`)，大于或等于(`>=`)，小于或等于(`<=`)和不等于(`<>`)。