MySQL分组查询每组最新的记录

发表于 | 分类于 | 评论数： | 阅读次数：
本文字数： 2.4k | 阅读时长 ≈ 4 分钟

前言

近来项目组工作比较繁忙，很长时间没有更新博客了。言归正传，今天这篇主要是总结下在工作中遇到的问题，避免下次再遇到浪费时间。

问题

MySQL 版本 5.7，用 group by 查询时报错

1
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sfmgpssit1.award_info.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因

MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的)

查看sql mode

1
SELECT @@sql_mode;

可以看到sql mode为ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

only_full_group_by说明：
only_full_group_by ：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置目前个人感觉和distinct差不多的，所以去掉就好

以下是MySQL官网的原文：

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

大致是说选择列表，HAVING条件或ORDER BY列表引用的非聚集列既不在GROUP BY子句中命名，也不在功能上依赖于GROUP BY列（由其唯一确定）的查询是不支持的。MySQL在5.7.5之后默认的SQL模式含有ONLY_FULL_GROUP_BY，而在5.7.5之前默认是不启用的。

解决

执行以下两个命令

1
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
1
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

然后执行group by语句，发现可以正常执行了

1
SELECT award_name,CREATE_TIME FROM(SELECT * FROM award_info ORDER BY create_time DESC LIMIT 10000) a WHERE CREATE_TIME IS NOT NULL GROUP BY award_name;
0%