本文將介紹如何分組資料,以便能彙總表內容的子集,這涉及兩個新SELECT語句子句,分別是 GROUP BY 子句和HAVING子句。
1.1 建立分組
分組是在SELECT語句的GROUP BY子句中建立的。
輸入:
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
輸出:
vend_id | num_prods |
---|---|
1001 | 3 |
1002 | 2 |
1003 | 7 |
1005 | 2 |
分析:上面的SELECT語句指定了兩個列,vend_id包含產品供應商ID,num_prods為計算欄位(**用COUNT(*)函式建立**)GROUP BY 子句指示MySQL按vend_id排序並分組資料。這導致對每個vend_id而不是整個表計算num_prods一次。從輸出看出,供應上1001有3個產品,供應商1002有2個產品,供應商1003有7個產品,而供應商1005有2個產品。
使用GROUP BY子句之前,需要知道一些重要規定:
- GROUP BY 子句可以包含任意數目的列。這使得能對分組進行巢狀,為資料分組提供更細緻的控制。
- 如果在GROUP BY子句中巢狀了分組,資料將在最後規定的分組上進行彙總。換句話說,在建立分組時,指定的所有類都一起計算(所以不能從個別的列取回資料)。
- GROUP BY 子句中列出的每個列都必須是檢索列或有效的表示式(但不能是聚集函式)。如果在SELECGT中使用表示式,則必須在GROUP BY子句中指定相同的表示式。不能使用別名。
- 除聚集計算語句外,在SELECT中的每個列都必須在GROUP BY子句中給出。
- 如果分組類中具有NULL值,則NULL將最為一個分組返回。如果類中有多行NULL值,他們將分為一組。
- GROUP BY 子句必須出現在WHERE子句之後,ORDER BY 子句之前。
? 使用ROLLUP 使用WITH ROLLUP 關鍵字,可以得到每個分組以及每個分組彙總級別(針對每個分組)的值,如下所示:
SELECT prod_id ,COUNT(*) AS num
FROM ACTIVITY20180508—UUUser
GROUP BY prod_id WITH ROLLUP
輸出:
分析:
解釋一下出現的兩個null,第一個null 是prod_id的值為null的行數有2行,
最後一個null,是每個行數量的總和為8。
1.2 過濾分組
HAVING類似與WHERE,之前所有的型別的WHERE子句都可以用HAVING來替代,唯一的差別是WHERE過濾行,而HAVING過濾分組。
輸入:
SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVUNG COUNT(*) >= 2;
輸出:
cust_id | orders |
---|---|
1000 | 2 |
分析:HAVING子句,它過濾COUNT(*)>=2(兩個以上的訂單)的那些分組。
✏️ HAVING和WHERE的差別 這裡有另一種理解方法,WHERE資料分組前進行過濾,HAVING在資料分組後進行過濾。這是一個重要的區別,WHERE排除的行不包括在分組中。這可能會改變計算值,從而影響HAVING子句中給予這些值過濾掉的分組。
HAVING和WHERE一起使用的例子:
列出具有2個(含)以上、價格為10(含)以上的產品的供應商:
輸入:
SELECT vend_id ,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2
輸出:
vend_id | num_prods |
---|---|
1003 | 4 |
1005 | 2 |
分析:WHERE子句過濾所有prod_price至少為10行。然後按vend_id分組資料,HAVING子句過濾計數2或2以上的分組。
不加HAVING過濾的結果是:
輸入:
SELECT vend_id ,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
輸出:
vend_id | num_prods |
---|---|
1001 | 3 |
1002 | 2 |
1003 | 4 |
1005 | 2 |
1.3分組和排序
ORDER BY 和 GROUP BY
ORDER BY | GROUP BY |
---|---|
排序產生的輸出 | 分組行。但輸出可能不是分組的順序 |
任意列都可以使用(甚至非選擇的列也可以使用) | 只可能使用選擇列或表示式列,而且必須使用每個選擇列表示式 |
不一定需要 | 如果與聚集函式一起使用列(或表示式),則必須使用 |
?不要忘記ORDER BY 一般在使用GROUP BY子句時,應該也給出ORDER BY子句,以保證資料正確的排序。
例子:檢索總計訂單價格大於等於50的訂單的訂單號和總計訂單價格:
輸入:
SELECT order_num,SUN(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 |
20007 | 1000.0 |
20008 | 125.0 |
為按總計訂單價格排序輸出,需要新增ORDER BY子句,如下所示:
輸入:
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price ) >= 50
OREDER BY ordertotal
輸出:
order_num | ordertotal |
---|---|
20005 | 149.87 |
20006 | 55 |
20007 | 1000.0 |
20008 | 125.0 |
分析:在這個例子中,GROUP BY 子句用來按訂單號(order_num列)分組資料,以便使用SUM(*)函式能夠返回總計訂單價格。HAVING子句過濾資料,使得只返回總計訂單價格大於等於50的訂單。最後,用ORDER BY 子句排序輸出。
1.4 SELECT 子句順序
子句 | 說明 | 是否必須使用 |
---|---|---|
SELET | 要返回的列或表示式 | 是 |
FROM | 從檢索資料的表 | 僅在從表選擇資料時使用 |
WHERE | 行級過濾 | 否 |
GROUP BY | 分組說明 | 僅在按組計算聚集時使用 |
HAVING | 組級過濾 | 否 |
ORDER BY | 輸出排序順序 | 否 |
LIMIT | 要檢索的行數 | 否 |
1.5 小結
本章講述瞭如何使用GROUP BY子句對資料組進行彙總計算,返回每個組的結果。我們看到了如何使用HAVING子句過濾特定的組,還知道了ORDER BY 和GROUP BY 之間以及WHERE 和HAVING 之間的差異。