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)