GROUP BY 语句根据一个或多个字段对结果集进行分组(也就是把值相同放到一个组中,显示组中一条记录),实现对每个组而不是对整个结果集统计。SELECT子句中的列名必须为分组列、聚合函数或常量。
语法形式为:
SELECT [field1, field12, ..., ] fun_name
FROM tablename
JOIN ON join_condition
WHERE where_conditon
GROUP BY field1, field12, ..., fieldn
[WITH ROLLUP]
[HAVING having_condition]
[ORDER BY field1, field12, ..., fieldn [ASC | DESC]]
参数说明:
SELECT 子句中的列必须为分组列(写在GROUP BY 后面的列)、聚合函数(SUM、MAX、AVG等)或常量(MySql5.7以后版本,默认是开启了only_full_group_by 模式的)。
fun_name 表示聚合函数,常见的有:SUM(总和)、AVG(平均值)、COUNT(行数)、MAX(最大值)、MIN(最小值)、GROUP_CONCAT(串联的字符串)、JSON_ARRAYAGG(将结果集作为单个JSON数组返回)、JSON_OBJECTAGG(将结果集作为单个JSON对象返回)。
GROUP BY 表示一个或多个字段对结果集进行分组,比如按照部门统计人数。
WITH ROLLUP 是可选的,表示是否对分组后的结果再进行统计。
HAVING 表示对分组的结果再进行条件过滤,比如统计部门人数大于10人的部门。(HAVING和WHERE的区别,HAVING 是对分组后的结果进行条件的过滤,而WHERE是在分组前进行条件的过滤,如果可以,我们应尽可能用WHERE先过滤记录,这样结果集减少,提高分组效率。)
GROUP BY用法举例
我们以MySql示例employees库演示,employees库表描述可以参考:mysql示例employees数据库安装和介绍
统计公司的总人数
SELECT COUNT(1) FROM employees ;
注意:使用count(列名)当某列出现null值的时候,count(*)仍然会计算,但是count(列名)不会。
统计各个部门人数
SELECT `dept_no`,COUNT(1) FROM `dept_emp` GROUP BY `dept_no`;
通过统计公司总人数和统计各个部门人数,我们能看到GROUP BY 作用,实现对每个组而不是对整个结果集统计。
在对结果集数据分组以后,SELECT 子句中的列必须为分组列、聚合函数(SUM、MAX、AVG等)或常量。如果我们在统计部门人数增加没有出现在GROUP BY后面列emp_no(员工id),会怎样?
报错了:列emp_no不在GROUP BY子句中,并且也不是聚合函数,这与sql_mode=only_full_group_by 不兼容(在MySql5.7以后版本,默认开启了only_full_group_by 模式的,以前的版本没有开启only_full_group_by 模式,不会报错)。
为什么分组后不能再引用原表中的列(不在 GROUP BY 子句的列)?
GROUP BY的作用是将表中结果集分组, GROUP BY 操作的对象是由多个员工组成的小组,小组的属性只能是SUM、AVG、MAX等统计的属性,而emp_no(员工id)是每个员工属性,并不是小组的属性。
假如分组后分组的某个非分组的列值都一样,或者说只想随机选择一个列值,那该怎么操作?有两种方法:
1、使用ANY_VALUE()函数
SELECT ANY_VALUE(`emp_no`) `emp_no`, `dept_no`,COUNT(1) FROM `dept_emp` GROUP BY `dept_no`;
注意,如果是在MySql5.7版本以下使用ANY_VALUE()函数会报错(代码演示的MySql5.5版本)。
2、关闭only_full_group_by 模式
通过select @@sql_mode查出sql_mode,去掉ONLY_FULL_GROUP_BY后复制过来
SELECT @@sql_mode;
修改my.cnf文件,在mysqld下面增加
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
然后重启MySql服务
建议不要在SELECT子句中引用不在GROUP BY子句中列,它会返回一个不确定的值,这可能不是您想要的。
GROUP_CONCAT()函数使用
作用将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
语法group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )。
统计每个部门历任经理,并按照当经理时间从前到后排序
SELECT GROUP_CONCAT(`emp_no` ORDER BY `from_date` ASC SEPARATOR ',') `emp_no`, `dept_no` FROM `dept_manager` GROUP BY `dept_no`;
说明:
group_concat长度:group_concat默认最大长度是1024,超过截取前1024长度字符。
查询group_concat:show variables like 'group_concat_max_len';
修改group_concat:修改配置文件my.conf(需要重启mysql服务) group_concat_max_len = 2048;
WITH ROLLUP子句实现在分组统计数据基础上再进行相同的统计
既统计部门人数,也统计所有部门总人数
SELECT `dept_no`, COUNT(1) FROM `dept_emp` GROUP BY `dept_no` WITH ROLLUP;
WITH ROLLUP子句可以实现在分组统计数据基础上再进行相同的统计,像上面的例子,统计所有部门总人数,NULL 331603,如何把字段WITH ROLLUP汇总字段NULL改成total,方法有很多,比如使用IFNULL(expr1,expr2)、IF(expr1,expr2,expr3)、GROUPING(expr [, expr] ...)、COALESCE(expr1,expr2,expr3,....,exprn)函数。
下面例子我们使用COALESCE()函数
COALESCE(a, b, c, ...., n) 作用是将返回传入的参数中第一个非null的值。
SELECT COALESCE(`dept_no`,'total') `dept_no`, COUNT(1) FROM `dept_emp` GROUP BY `dept_no` WITH ROLLUP;
使用ROLLUP时的其他注意事项
在MySQL 8.0.12之前,使用时ROLLUP,不能同时使用ORDER BY子句对结果进行排序。
SELECT COALESCE(`dept_no`,'total') `dept_no`, COUNT(1) FROM `dept_emp` GROUP BY `dept_no` WITH ROLLUP ORDER BY COUNT(1) DESC;
但是你可以采用子查询的办法实现排序
SELECT * FROM (SELECT COALESCE(`dept_no`,'total') `dept_no`, COUNT(1) `cnt` FROM `dept_emp` GROUP BY `dept_no` WITH ROLLUP) AS `sub` ORDER BY `cnt` DESC;
从MySQL 8.0.12开始,ORDER BY并且 ROLLUP可以一起使用
SELECT COALESCE(`dept_no`,'total') `dept_no`, COUNT(1) FROM `dept_emp` GROUP BY `dept_no` WITH ROLLUP ORDER BY COUNT(1) DESC;
要注意:与SELECT子句列一样,ORDER BY子句的列也必须为分组列、聚合函数(SUM、MAX、AVG等)。
HAVING 子句对分组结果进行筛选
统计2000年以后进入部门且数量大于500人的部门
SELECT `dept_no`, COUNT(1) `cnt` FROM `dept_emp` WHERE Year(`from_date`) > 2000 GROUP BY `dept_no` HAVING `cnt` > 500;
WHERE和HAVING区别
WHERE在查询返回之前对数据库中的查询条件进行过滤,即在结果返回之前起作用,且where后面不能使用聚合函数,WHERE不可以用字段别名(where后面不能使用聚合函数是因为where的执行顺序在聚合函数之前)。
HAVING 在查询数据库的结果分组之后进行过滤,即在结果返回之后起作用,所以HAVING 后面可以使用聚合函数,HAVING子句可以使用字段别名。