Shulou(Shulou.com)06/01 Report--

The Group by statement is used in conjunction with aggregate functions such as count,sum,avg,max,min to group result sets according to one or more columns.

(1) remove duplicate values: only one line of results is displayed according to the keywords after group by.

(2) the parameter ONLY_FULL_GROUP_BY is enabled by default for mysql5.7, which means full group by, that is, the column followed by select must be followed by group by, but the column followed by group by does not necessarily need to appear after 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.01sec) 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 | 30970 | d005 | 1986-12-01 | 2017-03-29 | 31112 | d002 | 1986-12-01 | 1993-12-10 | 40983 | d005 | 1986-12-01 | 99-05-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-01 | | 50449 | d005 | 1986-12-01 | 9999-01 | | 10004 | d004 | 1986-12-01 | 9999-01-01 | +-+ 10 rows in set (1986 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 does not report an error when the ONLY_FULL_GROUP_BY parameter is turned off, but the result is incomplete 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.01sec) 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)

(3) mysql5.7group by also has sorting function by default. 8.0 only groups are not sorted by default, and order by is needed to sort. This can be judged by whether there is Using filesort in the execution result.

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 | 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 | 10 | 100.00 | Using temporary Using filesort | +-+- -+ 1 row in set 1 warning (0.00 sec)

(4) whether group by can be sorted will directly affect the paging query result.

Version 8.0.13 mysql > select dept_no,count (*) from t_group group by dept_no limit 1 +-+-+ | dept_no | count (*) | +-+-+ | d006 | 1 | +-+-+ 1 row in set (0.01 sec) version 5.7.16: root@localhost [testdb] > select dept_no,count (*) from t_group group by dept_no limit 1 +-+ | dept_no | count (*) | +-+-+ | d002 | 1 | +-+-+ 1 row in set (0.00 sec)

Reference link

8.2.1.15 GROUP BY Optimization

MySQL 5.7A snippet of group by notes is as follows:

In MySQL, GROUP BY is used for sorting, so the server may also apply ORDER BY optimizations to grouping. However, relying on implicit or explicit GROUP BY sorting is deprecated. See Section 8.2.1.14, "ORDER BY Optimization".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.