应用场景:用于分页查询时去除重复数据 ,原代码如下:
select mem.*, substring_index(gmo.ou,'.',2) ou,
gmom.power_type powerType,gmom.member_type memberType
from gm_member as mem
left join gm_org_member as gmom on mem.member_id=gmom.member_id
left join gm_org as gmo on gmom.grid_id=gmo.grid_id
where mem.valid = 'Y'
and gmo.ou_uuid like concat('%',#{pOrgId},'%')
GROUP BY mem.member_id
order by gmom.power_type,mem.sort asc
错误日志:#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
错误原因: Mysql5.7版本以上对group by 分组有了新需求,要求group by 后的字段要与select后查询的字段一致,否则就会报错
解决方法 :使用distinct函数取代group by,修改后的代码如下:
select distinct mem.*, substring_index(gmo.ou,'.',2) ou,
gmom.power_type powerType,
gmom.member_type memberType
from gm_member as mem
left join gm_org_member as gmom on mem.member_id=gmom.member_id
left join gm_org as gmo on gmom.grid_id=gmo.grid_id
where mem.valid = 'Y'
and gmo.ou_uuid like concat('%',#{pOrgId},'%')
order by gmom.power_type,mem.sort asc