MySQL學習足跡記錄11--分組數據--GROUP BY，HAVING 1.創建分組GROUP BY 先列出所有的vend_id，以便作對比
mysql> SELECT vend_id FROM products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1005 |
| 1005 |
+---------+
14 rows in set (0.00 sec)
用GROUP BY進行分組
mysql> SELECT vend_id,COUNT(*) AS num_prods
-> FROM products #先分組，再分別計算COUNT（*）
-> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
4 rows in set (0.00 sec)
TIPS： *如果列中有多行NULL值，它們將分為一組 *GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前 2.過濾分組HAVING *HAVING 與 WHERE 的區別： WHERE過濾行，而HAVING過濾分組
eg:
mysql> SELECT vend_id,COUNT(*) AS num_prods
-> FROM products
-> GROUP BY vend_id
-> HAVING COUNT(*)>2; #從結果中過濾不符合COUNT(*)>2的組
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1003 | 7 |
+---------+-----------+
2 rows in set (0.00 sec)
* WHERE在數據分組前進行過濾，HAVING在數據分組後進行過濾，所以，WHERE排除的行不包括在分組中
eg:
先列出原始數據作對比
mysql> SELECT vend_id,prod_price FROM products
-> ORDER BY prod_price;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
| 1003 | 2.50 |
| 1003 | 2.50 |
| 1002 | 3.42 |
| 1003 | 4.49 |
| 1001 | 5.99 |
| 1002 | 8.99 |
| 1001 | 9.99 |
| 1003 | 10.00 |
| 1003 | 10.00 |
| 1003 | 13.00 |
| 1001 | 14.99 |
| 1005 | 35.00 |
| 1003 | 50.00 |
| 1005 | 55.00 |
+---------+------------+
14 rows in set (0.00 sec)
mysql> SELECT vend_id,COUNT(*) AS num_prods
-> FROM products
-> WHERE prod_price >14 #WHERE過濾後只剩下上表中最後3條記錄，
-> GROUP BY vend_id #HAVING再過濾分組後vend_id為不符合COUNT(*) >=2組
-> HAVING COUNT(*) >=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1005 | 2 |
+---------+-----------+
1 row in set (0.00 sec)
3. 分組和排序 GROUP BY和ORDER BY的區別 *ORDER BY指定的條件可以是任意列 *GROUP BY指定的條件只可能使用選擇列或列表達式 TIPS： 一般在使用GROUP BY子句時，也應該給出ORDER BY子句 Examples: 先列出原始數據：
mysql> SELECT order_num,quantity,item_price FROM orderitems;
+-----------+----------+------------+
| order_num | quantity | item_price |
+-----------+----------+------------+
| 20005 | 10 | 5.99 |
| 20005 | 3 | 9.99 |
| 20005 | 5 | 10.00 |
| 20005 | 1 | 10.00 |
| 20006 | 1 | 55.00 |
| 20007 | 100 | 10.00 |
| 20008 | 50 | 2.50 |
| 20009 | 1 | 10.00 |
| 20009 | 1 | 8.99 |
| 20009 | 1 | 4.49 |
| 20009 | 1 | 14.99 |
+-----------+----------+------------+
11 rows in set (0.00 sec)
mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal
-> FROM orderitems
-> GROUP BY order_num
-> HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+ #未用ORDERBY指定排序，結果可能不是想要的，例如按ordertotal升序
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
4 rows in set (0.00 sec)
mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal
-> FROM orderitems
-> GROUP BY order_num
-> HAVING SUM(quantity*item_price) >=50
-> ORDER BY ordertotal; # 用ORDERBY指定排序方式
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
4 rows in set (0.01 sec)
