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

 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)

 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)

 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)

 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)