Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggrega
使用mybatis-plus 的groupby查询报错
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggrega
这个错是因为MySQL 5.7.5之后的版本中sql_mode中默认包含ONLY_FULL_GROUP_BY规则
SELECT @@global.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
第一种
直接修改mysql配置文件,在my.cnf中添加配置
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
网上有些博主说可以直接用SQL语句去set,我试过不行,可能我的MySQL有什么问题,我直接改的配置文件,重启mysql就可以了
第二种
有的公司是不允许修改配置文件的,那只能在SQL上做修改,遇到这种错误,只需要把多余的要查询的字段用any_value包起来就行
SELECT target_code, ANY_VALUE(target_name)
FROM target
GROUP BY target_code
我的情况是又没有写SQL又不想改配置文件,因为我用的mybatis-plus的lambda表达式
网上有人说在group by 之后加select,像这样
.groupBy(Target::getTargetCode, Target::getId, Target::getTargetName)
.select(Target::getTargetCode, Target::getId, Target::getTargetName)
但是这样的话mybatis-plus最后打印的语句会是
SELECT id,target_code,target_name
FROM target
WHERE (delete_flag = 0 AND use_flag = 0) GROUP BY target_code,id,target_name;
这样查出来的结果并不是我想要的
最后也没有在mybatis-plus的lambda中找到解决办法,最后我的解决办法是放弃lambda表达式,采用普通的mybatis-plus查询方法拼接any_value
return Target.list(new QueryWrapper<Target>()
.select("ANY_VALUE(id) AS id, ANY_VALUE(target_code) AS targetCode, ANY_VALUE(target_name) AS targetName")
.eq("delete_flag", 0)
.eq("use_flag", 0)
.groupBy("target_code"));
这样拼接出来的接口才是我想要的
SELECT ANY_VALUE(id) AS id, ANY_VALUE(target_code) AS targetCode, ANY_VALUE(target_name) AS targetName
FROM target
WHERE (delete_flag = 0 AND use_flag = 0) GROUP BY target_code;