环境:mysql5.7.30,cmd命令中进⾏演⽰。
目录
1、分组查询语法
2、聚合函数
3、准备数据
4、单字段分组
5、多字段分组
6、分组前筛选数据
7、分组后筛选数据
8、 where和having的区别
9、分组后排序
10、where & group by & having & order by & limit 一起协作
11、mysql分组中的坑
12、总结
1、分组查询语法
语法:
SELECT column,group_function,..... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];
说明:
graph_function:聚合函数;group_by_expression:分组表达式,多个之间用逗号隔开;group_condition:分支之后对数据进行过滤;分组之后,select后面只能有两种类型的列:1)出现在group by后的列;2)或者使用聚合函数的列。
2、聚合函数
分组时,可以使用上面的聚合函数。
3、准备数据
drop table if exists t_order;-- 创建订单表create table t_order(id int not null AUTO_INCREMENT COMMENT '订单id',user_id bigint not null comment '下单⼈id',user_name varchar(16) not null default '' comment '⽤户名',price decimal(10,2) not null default 0 comment '订单⾦额',the_year SMALLINT not null comment '订单创建年份',PRIMARY KEY (id)) comment '订单表';-- 插⼊数据insert into t_order(user_id,user_name,price,the_year) values(1001,'myron Java',11.11,''),(1001,'myron Java',22.22,''),(1001,'myron Java',88.88,''),(1002,'刘德华',33.33,''),(1002,'刘德华',12.22,''),(1002,'刘德华',16.66,''),(1002,'刘德华',44.44,''),(1003,'张学友',55.55,''),(1003,'张学友',66.66,'');mysql> select * from t_order;+----+---------+------------+-------+----------+| id | user_id | user_name | price | the_year |+----+---------+------------+-------+----------+| 1 | 1001 | myron Java | 11.11 | || 2 | 1001 | myron Java | 22.22 | || 3 | 1001 | myron Java | 88.88 | || 4 | 1002 | 刘德华| 33.33 | || 5 | 1002 | 刘德华| 12.22 | || 6 | 1002 | 刘德华| 16.66 | || 7 | 1002 | 刘德华| 44.44 | || 8 | 1003 | 张学友| 55.55 | || 9 | 1003 | 张学友| 66.66 | |+----+---------+------------+-------+----------+9 rows in set (0.00 sec)mysql>
4、单字段分组
需求:查询每个用户下单数量,输出:用户id、下单数量,如下:
mysql> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_orderGROUP BY user_id;+----------+--------------+| 用户id | 下单数量|+----------+--------------+|1001 | 3 ||1002 | 4 ||1003 | 2 |+----------+--------------+3 rows in set (0.04 sec)mysql>
5、多字段分组
需求:查询每个用户每年下单数量,输出字段:用户id,年份,下单数量,如下:
mysql> SELECTuser_id 用户id, the_year 年份, COUNT(id) 下单数量FROMt_orderGROUP BY user_id , the_year;+----------+--------+--------------+| 用户id | 年份 | 下单数量|+----------+--------+--------------+|1001 | | 1 ||1001 | | 2 ||1002 | | 3 ||1002 | | 1 ||1003 | | 1 ||1003 | | 1 |+----------+--------+--------------+6 rows in set (0.00 sec)mysql>
6、分组前筛选数据
分组前对数据进行筛选,使用where关键字。
需求:需要查询每个用户下单数量,输出:用户id,下单数量,如下:
mysql> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_order tWHEREt.the_year=GROUP BY user_id;+----------+--------------+| 用户id | 下单数量|+----------+--------------+|1001 | 2 ||1002 | 3 ||1003 | 1 |+----------+--------------+3 rows in set (0.00 sec)mysql>
7、分组后筛选数据
分组后对数据筛选,使用having关键字。
需求:查询订单数量大于1的用户,输出:用户id,下单数量,如下:
方式1:
mysql> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_order tWHEREt.the_year=GROUP BY user_idHAVING count(id)>=2;+----------+--------------+| 用户id | 下单数量|+----------+--------------+|1001 | 2 ||1002 | 3 |+----------+--------------+2 rows in set (0.00 sec)
方式2:
mysql> SELECTuser_id 用户id, count(id) 下单数量FROMt_order tWHEREt.the_year=GROUP BY user_idHAVING 下单数量>=2;+----------+--------------+| 用户id | 下单数量|+----------+--------------+|1001 | 2 ||1002 | 3 |+----------+--------------+2 rows in set (0.00 sec)mysql>
8、 where和having的区别
where是在分组(聚合)前对记录进行筛选, 而having是在分组结束后的结果里筛选,最后返回整个sql的查询结果。
可以把having理解为两级查询,即含having的查询操作先获得不含having子句时的sql查询结果表,然后在这个结果表上使用having条件筛选出符合的记录,最后返回这些记录,因此,having后是可以跟聚合函数的,并且这个聚合函数不必与select后面的聚集函数相同。
9、分组后排序
需求:获取每个用户最大金额,然后按照最大金额倒序,输出:用户id,最大金额,如下:
mysql> SELECTuser_id 用户id, max(price) 最大金额FROMt_order tGROUP BY user_idORDER BY 最大金额 desc;+----------+--------------+| 用户id | 最大金额|+----------+--------------+|1001 | 88.88 ||1003 | 66.66 ||1002 | 44.44 |+----------+--------------+3 rows in set (0.00 sec)mysql>
10、where & group by & having & order by & limit 一起协作
where、group by、having、order by 、limit这些关键字一起使用时,先后顺序有明确的限制,语法如下:
select 列 from 表名 where [查询条件] group by [分组表达式] having [分组过滤条件] order by [排序条件] limit [offset,] count;
注意:写法上面必须按照上面的顺序来写。
示例:
需求:查询出,下单数量大于等于2的,按照下单数量降序排序,最后只输出第1条记录,显示:用户id,下单数量,如下:
mysql> select * from t_order;+----+---------+------------+-------+----------+| id | user_id | user_name | price | the_year |+----+---------+------------+-------+----------+| 1 | 1001 | myron Java | 11.11 | || 2 | 1001 | myron Java | 22.22 | || 3 | 1001 | myron Java | 88.88 | || 4 | 1002 | 刘德华| 33.33 | || 5 | 1002 | 刘德华| 12.22 | || 6 | 1002 | 刘德华| 16.66 | || 7 | 1002 | 刘德华| 44.44 | || 8 | 1003 | 张学友| 55.55 | || 9 | 1003 | 张学友| 66.66 | |+----+---------+------------+-------+----------+9 rows in set (0.00 sec)mysql> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_order tWHEREt.the_year=GROUP BY user_idHAVING count(id)>=2ORDER BY 下单数量 DESCLIMIT 1;+----------+--------------+| 用户id | 下单数量|+----------+--------------+|1002 | 3 |+----------+--------------+1 row in set (0.00 sec)mysql>
11、mysql分组中的坑
本文开头有介绍,分组中select后面的列只能是2种:
1)出现在group by后面的列;2)使用聚合函数的列。
Oracle、SqlServer、db2中也是按照这种规范来的。
文中使用的是5.7版本,默认是按照这种规范来的。
mysql早期的一些版本,没有上面这些要求,select后面可以跟任何合法的列。
建议:在写分组查询的时候,最好按照标准的规范来写,select后面出现的列必须在group by中或者必须使用聚合函数
12、总结
1)在写分组查询的时候,,最好按照标准的规范来写,select后出现的列必须在group by中或者必须使用聚合函数。
2)select语法顺序:select、from、where、group by、having、order by、limit,顺序不能搞错了,否则报错。
如果觉得《MySQL系列复习(9)分组查询(group by having)》对你有帮助,请点赞、收藏,并留下你的观点哦!