mysql 根据某字段去重时遇到的问题

需要根据某个字段进行去重,但是会报错,

Expression #1 of ORDER BY clause is not in SELECT list, references column 'a.time' which is not in SELECT list; this is incompatible with DISTINCT

SELECT list is not in GROUP BY clause and contains nonaggregated column ‘a.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible withsql_mode=only_full_group_by

第一条报错是使用了DISTINCT,第二条报错是使用了group by去重

百度上去找原因,都是在说由于mysql5.7.5及以上版本实现了对功能依赖的检测。默认启用了ONLY_FULL_GROUP_BY SQL模式

需要修改

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

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
但是我执行力之后无效,而且我查询sql_mode中并没有only_full_group_by,原因未知

于是尝试了第二种方法,在报错字段上面加ANY_VALUE(),问题解决

原sql:

select Id,time from
(SELECT a.xxx Id,a.time
from xxxx join xxxxx where xxxxxx

) a group by Id order by a.time desc limit 0,10;

新sql:

select ANY_VALUE(Id),ANY_VALUE(time) from
(SELECT a.xxx Id,a.time
from xxxx join xxxxx where xxxxxx

) a group by Id order by ANY_VALUE(a.time) desc limit 0,10;

ANY_VALUE这个函数好像只有在5.7版本以上才有