MySQL学习足迹记录11--分组数据--GROUP BY，HAVING_MySQL

bitsCN.com

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)

 

bitsCN.com