Group by 语句用于结合聚合函数（如count，sum，avg，max，min），根据一个或多个列对结果集进行分组。

（1）去掉重复值：根据group by后面的关键字只显示一行结果；

（2）mysql5.7默认开启参数ONLY_FULL_GROUP_BY，表示完全group by，即select后面跟的列group by后面也必须有，但是group by后面跟的列，select后面不一定需要出现；

mysql>select@@version;+-----------+|@@version|+-----------+|8.0.13|+-----------+1rowinset(0.00sec)mysql>showvariableslike'%sql_mode%';+---------------+-----------------------------------------------------------------------------------------------------------------------+|Variable_name|Value|+---------------+-----------------------------------------------------------------------------------------------------------------------+|sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|+---------------+-----------------------------------------------------------------------------------------------------------------------+1rowinset(0.01sec)mysql>select*fromt_group;+--------+---------+------------+------------+|emp_no|dept_no|from_date|to_date|+--------+---------+------------+------------+|22744|d006|1986-12-01|9999-01-01||24007|d005|1986-12-01|9999-01-01||30970|d005|1986-12-01|2017-03-29||31112|d002|1986-12-01|1993-12-10||40983|d005|1986-12-01|9999-01-01||46554|d008|1986-12-01|1992-05-27||48317|d008|1986-12-01|1989-01-11||49667|d007|1986-12-01|9999-01-01||50449|d005|1986-12-01|9999-01-01||10004|d004|1986-12-01|9999-01-01|+--------+---------+------------+------------+10rowsinset(0.00sec)mysql>selectdept_no,count(*)fromt_groupgroupbydept_no;+---------+----------+|dept_no|count(*)|+---------+----------+|d006|1||d005|4||d002|1||d008|2||d007|1||d004|1|+---------+----------+6rowsinset(0.00sec)mysql>selectdept_no,emp_no,count(*)fromt_groupgroupbydept_no;ERROR1055(42000):Expression#2ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn'employees.t_group.emp_no'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by关闭ONLY_FULL_GROUP_BY参数后，不报错，但是结果是不完全groupby;mysql>setsessionsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';QueryOK,0rowsaffected(0.01sec)mysql>selectdept_no,emp_no,count(*)fromt_groupgroupbydept_no;+---------+--------+----------+|dept_no|emp_no|count(*)|+---------+--------+----------+|d006|22744|1||d005|24007|4||d002|31112|1||d008|46554|2||d007|49667|1||d004|10004|1|+---------+--------+----------+6rowsinset(0.00sec)

（3）mysql5.7group by默认还有排序功能，8.0默认只分组不排序，需要加order by才排序，这点可以从执行结果是否有Using filesort来判断

mysql>select@@version;+-----------+|@@version|+-----------+|8.0.13|+-----------+1rowinset(0.00sec)mysql>selectdept_no,count(*)fromt_groupgroupbydept_no;+---------+----------+|dept_no|count(*)|+---------+----------+|d006|1||d005|4||d002|1||d008|2||d007|1||d004|1|+---------+----------+6rowsinset(0.00sec)mysql>descselectdept_no,count(*)fromt_groupgroupbydept_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|SIMPLE|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+1rowinset,1warning(0.00sec)root@localhost[testdb]>select@@version;+------------+|@@version|+------------+|5.7.16-log|+------------+1rowinset(0.00sec)root@localhost[testdb]>selectdept_no,count(*)fromt_groupgroupbydept_no;+---------+----------+|dept_no|count(*)|+---------+----------+|d002|1||d004|1||d005|4||d006|1||d007|1||d008|2|+---------+----------+6rowsinset(0.00sec)root@localhost[testdb]>descselectdept_no,count(*)fromt_groupgroupbydept_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary;Usingfilesort|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+1rowinset,1warning(0.00sec)

(4) group by是否能排序会直接影响分页查询结果

8.0.13版本mysql>selectdept_no,count(*)fromt_groupgroupbydept_nolimit1;+---------+----------+|dept_no|count(*)|+---------+----------+|d006|1|+---------+----------+1rowinset(0.01sec)5.7.16版本：root@localhost[testdb]>selectdept_no,count(*)fromt_groupgroupbydept_nolimit1;+---------+----------+|dept_no|count(*)|+---------+----------+|d002|1|+---------+----------+1rowinset(0.00sec)

参考链接

8.2.1.15 GROUP BY Optimization

MySQL 5.7有关group by说明的片段如下：

In MySQL,GROUP BYis used for sorting, so the server may also applyORDER BYoptimizations to grouping. However, relying on implicit or explicitGROUP BYsorting is deprecated. SeeSection8.2.1.14, “ORDER BY Optimization”.