【MySQL學習】分組資料，使用子查詢

分組資料
- 資料分組
- 創建分組
- 過濾分組
- 分組和排序
- SELECT字句順序
使用子查詢
- 使用子查詢進行過濾
- 作為計算欄位使用

分組資料

介紹兩個新的SELECT 陳述句字句，分別是GROUP BY 和 HAVING，

資料分組

之前我們介紹過使用聚集函式匯總資料，比如對1003供應商進行匯總

SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id =1003;

說明這個供應商提供7個產品

+-----------+
| num_prods |
+-----------+
|         7 |
+-----------+

當我們需要回傳每個供應商提供的產品數目，或者回傳只提供單項產品的供應商所提供的產品，或者回傳提供十個以上產品的供應商，我們就需要進行資料分組，以便能對每個組進行聚集計算，

創建分組

分組使用 SELECT 陳述句中的 GROUP BY 字句建立的
舉個例子

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

這個陳述句制定了資料庫按照供應商id排序并進行資料分組，導致每次計算產品數量不是對著所有產品，而是對某個供應商的所有產品計數

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

這個陳述句有這些注意事項

GROUP BY 子句可以包含任意數目的列，這使得能對分組進行嵌套，為資料分組提供更細致的控制，
如果在 GROUP BY 子句中嵌套了分組，資料將在最后規定的分組上進行匯總，換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料），
GROUP BY 子句中列出的每個列都必須是檢索列或有效的運算式（但不能是聚集函式），如果在 SELECT 中使用運算式，則必須在GROUP BY 子句中指定相同的運算式，不能使用別名，
除聚集計算陳述句外， SELECT 陳述句中的每個列都必須在 GROUP BY 子句中給出，
如果分組列中具有 NULL 值，則 NULL 將作為一個分組回傳，如果列中有多行 NULL 值，它們將分為一組，
GROUP BY 子句必須出現在 WHERE 子句之后， ORDER BY 子句之前，
使用 WITH ROLLUP 關鍵字，可以得到每個分組以及每個分組匯總級別（針對每個分組）的值

過濾分組

我們已經看到了 WHERE 子句的作用，但是，在這個例子中 WHERE 不能完成任務，因為 WHERE 過濾指定的是行而不是分組，事實上， WHERE 沒有分組的概念，想要過濾分組，則使用 HAVING 子句, HAVING 非常類似于 WHERE ，事實上，目前為止所學過的所有型別的 WHERE 子句都可以用 HAVING 來替代，唯一的差別是
WHERE 過濾行，而 HAVING 過濾分組，

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

這樣輸出的就是含有兩個以上訂單的組，


+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

HAVING 和 WHERE 的差別這里有另一種理解方法， WHERE 在資料分組前進行過濾， HAVING 在資料分組后進行過濾，這是一個重要的區別， WHERE 排除的行不包括在分組中，這可能會改變計算值，從而影響 HAVING 子句中基于這些值過濾掉的分組，

為了理解這兩種子句的不同，我們再看一個例子，如何列出具有兩個以上、價格為10的產品供應商

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

這里的 WHERE 只呼叫價格大于等于10的商品，而 HAVING 則篩選了有兩個以上（價格大于等于10）的商品的供應商，

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

同時我們應該注意到，WHERE 的觸發是在前面的，我們去掉 WHERE 子句來看看結果：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

輸出是：

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

分組和排序

分組和排序分別采用 GROUP BY 和 ORDER BY，兩者常常聯合起來完成作業，因為ORDER BY 是唯一能保證資料正確排序的方法，

我們舉一個簡單的例子，下面的例子檢索總計訂單價格大于等于50的訂單的訂單號和總計訂單價格：

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

在這個例子中， GROUP BY 子句用來按訂單號（ order_num 列）分組資料，以便 SUM(*) 函式能夠回傳總計訂單價格， HAVING 子句過濾資料，使得只回傳總計訂單價格大于等于 50 的訂單，最后，用 ORDER BY 子句排序輸出，

SELECT字句順序

這里回顧一下 SELECT 陳述句中的子句的順序

子句	說明	是否必須使用
SELECT	要回傳的列或者運算式	是
FROM	從中檢索資料的表	僅在從表中選擇資料時使用
WHERE	行級過濾	否
GROUP BY	分組說明	僅在按組計算聚集時使用
HAVING	組級過濾	否
ORDER BY	輸出排序順序	否
LIMIT	檢索行數	否

使用子查詢

MySQL 4.1引入了對子查詢的支持，所以要想使用下面描述的SQL，必須使用MySQL 4.1或更高級的版本，SELECT陳述句是SQL的查詢，迄今為止我寫的所有 SELECT 陳述句都是簡單查詢，即從單個資料庫表中檢索資料的單條陳述句，

SQL還允許創建子查詢（subquery），即嵌套在其他查詢中的查詢，為什么要這樣做呢？理解這個概念的最好方法是考察幾個例子，

使用子查詢進行過濾

測驗資料中中使用的資料庫表都是關系表（關于每個表及關系的描述，請參閱《MySQL必知必會》附錄B），訂單存盤在兩個表中，對于包含訂單號、客戶ID、訂單日期的每個訂單， orders 表存盤一行，各訂單的物品存盤在相關的orderitems 表中， orders 表不存盤客戶資訊，它只存盤客戶的ID，實際的客戶資訊存盤在 customers 表中，

現在，假如需要列出訂購物品 TNT2 的所有客戶，應該怎樣檢索？下面列出具體的步驟，

檢索包含物品 TNT2 的所有訂單的編號，
檢索具有前一步驟列出的訂單編號的所有客戶的ID，
檢索前一步驟回傳的所有客戶ID的客戶資訊，

上述每個步驟都可以單獨作為一個查詢來執行，可以把一條 SELECT陳述句回傳的結果用于另一條 SELECT 陳述句的 WHERE 子句，

也可以使用子查詢來把3個查詢組合成一條陳述句，

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id ='TNT2');

值得注意的是，子查詢總是自內向外處理，

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

現在我們得到了所有訂購物品 TNT2 的客戶的 ID （10001,10004），下一步是檢索這些客戶的資訊：

SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
					FROM orders
					WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id ='TNT2'));

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

我們成功得到了結果，可見，在 WHERE 子句中使用子查詢能夠撰寫出功能很強并且很靈活的SQL陳述句，對于能嵌套的子查詢的數目沒有限制，不過在實際使用時由于性能的限制，不能嵌套太多的子查詢，

雖然子查詢一般與 IN 運算子結合使用，但也可以用于測驗等于（ = ）、不等于（ <> ）等，

作為計算欄位使用

使用子查詢的另一方法是創建計算欄位，假如需要顯示 customers表中每個客戶的訂單總數，訂單與相應的客戶ID存盤在 orders 表中，

為了執行這個操作，遵循下面的步驟，

從 customers 表中檢索客戶串列，
對于檢索出的每個客戶，統計其在 orders 表中的訂單數目，

正如前兩章所述，可使用 SELECT COUNT ( *) 對表中的行進行計數，并且通過提供一條 WHERE 子句來過濾某個特定的客戶ID，可僅對該客戶的訂單進行計數，

例如，下面的代碼對客戶 10001 的訂單進行計數：

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001

為了對每個客戶執行 COUNT() 計算，應該將 COUNT() 作為一個子查詢，

請看下面的代碼：

SELECT cust_name,
			cust_state,
			(SELECT COUNT(*) AS orders
			FROM orders
			WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;


+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

這條 SELECT 語句對 customers 表中每個客戶返回 3 列：cust_name 、 cust_state 和 orders ， orders 是一個計算欄位，它是由圓括號中的子查詢建立的，該子查詢對檢索出的每個客戶執行一次，在此例子中，該子查詢執行了5次，因為檢索出了5個客戶，

子查詢中的 WHERE 子句與前面使用的 WHERE 子句稍有不同，因為它使用了完全限定列名（在第4章中首次提到），下面的陳述句告訴SQL比較orders 表中的 cust_id 與當前正從 customers 表中檢索的 cust_id ：


WHERE orders.cust_id = customers.cust_id

這種型別的子查詢稱為相關子查詢 (correlated subquery) ，任何時候只要列名可能有多義性，就必須使用這種語法（表名和列名由一個句點分隔)，

SELECT cust_name,
			cust_state,
			(SELECT COUNT(*) AS orders
			FROM orders
			WHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;

表中有兩個相同的 cust_id 列，一個在 customers 中，另一個在orders 中，需要比較這兩個列以正確地把訂單與它們相應的顧客匹配，如果不完全限定列名，MySQL將假定你是對 orders 表中的 cust_id 進行自身比較，而 SELECT COUNT(*) FROM orders WHERE cust_id = cust_id;總是回傳 orders 表中的訂單總數（因為MySQL查看每個訂單的 cust_id是否與本身匹配，當然，它們總是匹配的），

所以輸出是：

`+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      5 |
| E Fudd         | IL         |      5 |
| Mouse House    | OH         |      5 |
| Wascals        | IN         |      5 |
| Yosemite Place | AZ         |      5 |
+----------------+------------+--------+

值得注意的是，雖然這里給出的樣例代碼運行良好，但它并不是解決這種資料檢索的最有效的方法，在后面的章節中我們還要遇到這個例子

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/267173.html

標籤：其他

上一篇：MySQL8 常用命令之DQL（多表聯查）

下一篇：C語言中的指標與陣列