MySQL 自学第十三章 分组数据
1、数据分组
数据分组 可以理解为excel 中的的数据透视,通过进行数据分组统计,达到对某一类数据进行分类汇总;
2、创建分组
引入GROUP BY子句实现分组
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
3、过滤分组
之前过滤数据使用的是WHERE,但是由于WHERE过滤指定的是行而不是分组,所以引入是HAVING子句;
HAVING和WHERE的差别
- WHERE在数据分组前进行过滤,
- HAVING在数据分组后进行过滤。
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
组合使用
由此例可以看出,WHERE和HAVING可以组合使用,但是需要注意先进行WHERE过滤之后,再使用ORDER BY 与HAVING一起使用;
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
4、分组和排序
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
5、SELECT子句顺序