今天公司把 mysql 从 5.6 升级到 5.7 后,报错 1055:Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wechat_data.mal.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 。我查了下大部分的回答都是改 mysql 配置的,主管不让改配置。请问 V 友如何在不改配置只改 sql 语句使其正常运行呢?
以下是 sql 语句: SELECT mp.id, mp.mp_name, mp.weixinname, mp.biz, ifnull(mp.head_img_url, '') AS head_img_url, ifnull(mp.qr_code_url, '') AS qr_code_url, mp.introduce, mp.company_name, mp.first_average_num, mp.average_num, mp.souce_mp, mp.souce_article, mp.is_auth, mp.push_num, ifnull(mp.update_time, '') AS update_time, ifnull(mp.create_time, '') AS create_time,
IF (mal.biz 'null', 1, 0) AS is_storage,
ifnull(mal.create_time, '') AS import_time,
ifnull(art.read_num, 0) AS read_num,
ifnull(art.like_num, 0) AS like_num
FROM
wd_mp_library
mp
LEFT JOIN wd_mp_library_formal mal ON mp.biz = mal.biz
LEFT JOIN wd_mp_library_about_articles art ON mp.biz = art.biz
WHERE
mp.isdel = 0
GROUP BY
mp.id
ORDER BY
mp.update_time DESC,
mp.create_time DESC,
art.read_num DESC
LIMIT 0,
20
6
mchl 2017-02-22 00:20:21 +08:00 via Android
select distinct …
|
7
vincik 2017-02-22 10:47:06 +08:00
select distinct field
|
8
734695609 2019-06-03 11:11:38 +08:00
一个简单的例子
mp.mp_name 改成 max(mp.mp_name) as mp_name 其他字段也相同。 因为我们这边也不给改 sql_mode,所以搜索出来的方法,亲测可用,因为聚合函数没有不许放到 group by 后面的限制 不过要注意你的 max(field)中的字段的内容是否相同,我这边都是相同的,如果不一样,max(field)会拿最大的,需要注意一下 详细可以看官网文档的 group by 部分 https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html 我看的 8.0 文档,你换成自己版本就好了 |