environment
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16 |
+-----------+
Execute query statement
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name | class_id | age | gender |
+----+--------+----------+------+--------+
| 1 | 刘备 | 1 | 18 | 2 |
| 2 | 李四 | 1 | 19 | 1 |
| 3 | 王五 | 2 | 20 | 2 |
| 4 | 张飞 | 2 | 21 | 1 |
| 5 | 关羽 | 1 | 22 | 2 |
| 6 | 曹操 | 1 | 20 | NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)
-- Requirement: After sorting by age, take out the oldest students in each class
select * from (
select * from my_student order by age desc
) as t group by t.class_id;
report errors
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 't.id'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
reason:
MySQL 5.7.5 and above function dependency detection function
Solution:
-- View current configuration items
select @@global.sql_mode
-- remove ONLY_FULL_GROUP_BY
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
After setting, if it does not take effect, you can exit and log in again
