MySQL資料庫學習足跡記錄11–分組數據–GROUP BY,HAVING

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)

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。