相同点:
(1)去掉重复值:根据group by后面的关键字只显示一行结果;
(2)默认开启参数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 | 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 |
+--------+---------+------------+------------+
10 rows in set (0.00 sec)
mysql> select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006 | 1 |
| d005 | 4 |
| d002 | 1 |
| d008 | 2 |
| d007 | 1 |
| d004 | 1 |
+---------+----------+
6 rows in set (0.00 sec)
mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.t_group.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
关闭ONLY_FULL_GROUP_BY参数后,不报错,但是结果是不完全group by;
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)
mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;
+---------+--------+----------+
| dept_no | emp_no | count(*) |
+---------+--------+----------+
| d006 | 22744 | 1 |
| d005 | 24007 | 4 |
| d002 | 31112 | 1 |
| d008 | 46554 | 2 |
| d007 | 49667 | 1 |
| d004 | 10004 | 1 |
+---------+--------+----------+
6 rows in set (0.00 sec)
不同点:mysql5.7group by 默认还有排序功能,8.0默认只分组不排序,需要加order by才排序,这点可以从执行结果是否有Using filesort来判断
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.00 sec)
mysql> select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006 | 1 |
| d005 | 4 |
| d002 | 1 |
| d008 | 2 |
| d007 | 1 |
| d004 | 1 |
+---------+----------+
6 rows in set (0.00 sec)
mysql> desc select dept_no,count(*) from t_group group by dept_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 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [testdb]>select @@version;
+------------+
| @@version |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)
root@localhost [testdb]>select dept_no,count(*) from t_group group by dept_no;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002 | 1 |
| d004 | 1 |
| d005 | 4 |
| d006 | 1 |
| d007 | 1 |
| d008 | 2 |
+---------+----------+
6 rows in set (0.00 sec)
root@localhost [testdb]>desc select dept_no,count(*) from t_group group by dept_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 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)