数据库分组查询报错:
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘fortress.PartnerSetting.Id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SQL.sql 3 16
数据库脚本:
select
DISTINCT(AppId),
Id,
MchId,
CompanyId,
PartnerSettingName,
Provider,
ReturnUrl,
NotifyUrl,
RefundNotifyUrl,
Status
from PartnerSetting
where status = 0 GROUP BY AppId;
解决办法:
1.查询sql_model脚本:
SELECT @@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
2.修改sql_model:
set@@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_model,可以看到去掉了
ONLY_FULL_GROUP_BY
总结:查询出来的结果,不论是什么,把前面的 ONLY_FULL_GROUP_BY 去掉,然后剩下的全部复制,粘贴。在set一下,就可以了