浅谈MySQL中的group by

吾爱主题阅读：191 2024-04-02 08:07:16 评论：0

1、前言

mysql的group by用于对查询的数据进行分组；此外mysql提供having子句对分组内的数据进行过滤。

mysql提供了许多select子句关键字，

它们在语句中的顺序如下所示：

子句	作用	是否必须/何时使用
select	查询要返回的数据或者表达式	是
from	指定查询的表	否
where	指定行级过滤	否
group by	分组	否/对数据分组时使用
having	分组过滤	否/对分组后的数据过滤使用
order by	返回数据时指定排序规则	否
limit	指定返回数据的行数	否

2、准备user表

准备一张user表，其ddl和表数据如下所示

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 set names utf8mb4; set foreign_key_checks = 0; -- ---------------------------- -- table structure for user -- ---------------------------- drop table if exists ` user `; create table ` user ` ( `id` bigint (20) not null auto_increment comment '主键' , ` name ` varchar (255) character set utf8 collate utf8_general_ci not null comment '用户名' , `nation` varchar (255) character set utf8 collate utf8_general_ci null default null comment '民族' , `age` int (11) null default null comment '年龄' , `height` double null default null comment '身高' , `sex` smallint (6) null default null comment '性别' , primary key (`id`) using btree ) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic ; -- ---------------------------- -- records of user -- ---------------------------- insert into ` user ` values (1, '李子捌' , '汉族' , 18, 180, 1); insert into ` user ` values (2, '张三' , '回族' , 20, 175, 1); insert into ` user ` values (3, '李四' , '维吾尔族' , 45, 168, 0); insert into ` user ` values (4, '王五' , '蒙古族' , 18, 177, 1); insert into ` user ` values (5, '赵六' , '汉族' , 16, 184, 0); insert into ` user ` values (6, '田七' , '维吾尔族' , 27, 192, 1);

user表中数据如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select * from user ; + ----+--------+----------+------+--------+------+ | id | name | nation | age | height | sex | + ----+--------+----------+------+--------+------+ | 1 | 李子捌 | 汉族 | 18 | 180 | 1 | | 2 | 张三 | 回族 | 20 | 175 | 1 | | 3 | 李四 | 维吾尔族 | 45 | 168 | 0 | | 4 | 王五 | 蒙古族 | 18 | 177 | 1 | | 5 | 赵六 | 汉族 | 16 | 184 | 0 | | 6 | 田七 | 维吾尔族 | 27 | 192 | 1 | + ----+--------+----------+------+--------+------+ 6 rows in set (0.00 sec)

2.1 group by规则

使用group by之前需要先了解group by使用的相关规则

group by子句置于where之后，order by子句之前
having 子句置于group by 之后，order by子句之前
group by子句中的每个列都必须是select的检索列或者有效表达式，不能使用聚集函数
select中使用的表达式，在group by子句中必须出现，并且不能使用别名
group by分组的数据中包含null值，null值被分为一组
group by子句可以嵌套，嵌套的分组在最后分组上汇总

2.2 group by使用

需求：

统计不同民族的用户数

语句：

1 2 3 4 5 6 7 8 9 10 mysql> select nation, count (*) from user group by nation; + ----------+----------+ | nation | count (*) | + ----------+----------+ | 汉族 | 2 | | 回族 | 1 | | 维吾尔族 | 2 | | 蒙古族 | 1 | + ----------+----------+ 4 rows in set (0.00 sec)

group by可以结合where一起使用，不过where不能在group by之后进行过滤，使用where子句之后，分组的数据是where子句过滤后的数据集。

1 2 3 4 5 6 7 8 mysql> select nation, count (*) as nation_num from user where sex = 0 group by nation; + ----------+------------+ | nation | nation_num | + ----------+------------+ | 维吾尔族 | 1 | | 汉族 | 1 | + ----------+------------+ 2 rows in set (0.00 sec)

2.3 having使用

对group by分组后的数据还需要再次过滤，就必须使用having子句。group by子句后使用where子句mysql服务器会抛出异常

1 2 mysql> select nation, count (*) as nation_num from user group by nation where nation = '汉族' ; error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'where nation = ' 汉族 '' at line 1

此时只需要将上面where子句替换成having子句即可，having子句支持所有的where操作符，通俗的说where子句能用的地方只有替换成having就可以在group by子句后使用了

1 2 3 4 5 6 7 vmysql> select nation, count (*) as nation_num from user group by nation having nation = '汉族' ; + --------+------------+ | nation | nation_num | + --------+------------+ | 汉族 | 2 | + --------+------------+ 1 row in set (0.00 sec)

2.4 order by与limit

分组后的数据需要排序可以使用order by，order by子句需要更在having子句之后。

1 2 3 4 5 6 7 8 9 mysql> select nation, count (*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc ; + ----------+------------+ | nation | nation_num | + ----------+------------+ | 维吾尔族 | 2 | | 回族 | 1 | | 蒙古族 | 1 | + ----------+------------+ 3 rows in set (0.00 sec)

对于输出的结果需要指定返回的行数，可以使用limit，limit子句在整个语句的最后。

1 2 3 4 5 6 7 8 mysql> select nation, count (*) as nation_num from user group by nation having nation != '汉族' order by nation_num desc limit 2; + ----------+------------+ | nation | nation_num | + ----------+------------+ | 维吾尔族 | 2 | | 回族 | 1 | + ----------+------------+ 2 rows in set (0.00 sec)

2.5 with rollup

在group by子句中，with rollup 可以实现在分组统计数据基础上再进行相同的统计（sum,avg,count…）

比如max()：

1 2 3 4 5 6 7 8 9 10 11 mysql> select nation, max (height) as nation_num from user group by nation with rollup ; + ----------+------------+ | nation | nation_num | + ----------+------------+ | 回族 | 175 | | 汉族 | 184 | | 维吾尔族 | 192 | | 蒙古族 | 177 | | null | 192 | + ----------+------------+ 5 rows in set (0.00 sec)

比如avg()：

1 2 3 4 5 6 7 8 9 10 11 mysql> select nation, avg (height) as nation_num from user group by nation with rollup ; + ----------+--------------------+ | nation | nation_num | + ----------+--------------------+ | 回族 | 175 | | 汉族 | 182 | | 维吾尔族 | 180 | | 蒙古族 | 177 | | null | 179.33333333333334 | + ----------+--------------------+ 5 rows in set (0.00 sec)

比如count()：

1 2 3 4 5 6 7 8 9 10 11 mysql> select nation, count (*) as nation_num from user group by nation with rollup ; + ----------+------------+ | nation | nation_num | + ----------+------------+ | 回族 | 1 | | 汉族 | 2 | | 维吾尔族 | 2 | | 蒙古族 | 1 | | null | 6 | + ----------+------------+ 5 rows in set (0.00 sec)