数据库|mysql教程
mysql,记录
数据库-mysql教程
易语言图标替换器源码,ubuntu 16卡顿,爬虫禁止js执行,php最优,安顺企业seolzw
MySQL学习足迹记录11–分组数据–GROUP BY，HAVING
wordpress源码阅读,vscode编辑器很慢,ubuntu上python,tomcat请求无反应,爬虫写到kafka,什么是php高并发,浙江短视频seo优化推广,做一个自动采集的淘宝客网站,discuz 模板 显示邮箱lzw
1.创建分组GROUP BY
仿面包网网站源码,前端vscode常用包,最小构建ubuntu,tomcat 改图标,sqlite3是什么格式,爬虫软件怎么做工程图,php 字符串与数字比较,运城seo优化推荐咨询,商城手机网站建设,免费响应式模板网站模板下载lzw
先列出所有的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)
