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 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like '%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 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from t_group;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 22744 | d006 | 1986-12-01 | 9999-01-01 |
| 24007 | d005 | 1986-12-01 |