mysql 使用group by报错 sql_mode=only_full_group_by 在navicat中修改session 中的sql_mode
程序员文章站
2022-11-21 07:58:08
查看 select @@sql_mode;mysql8返回的sql_mode结果去掉 ONLY_FULL_GROUP_BY 这个sql_mode,操作如下:SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';然后再执行sql语句就不会报错了,这种只是修改本次连接中的sql_mode。当断开mysql连接...
查看
select @@sql_mode;
mysql8返回的sql_mode结果
去掉 ONLY_FULL_GROUP_BY 这个sql_mode,操作如下:
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
然后再执行sql语句就不会报错了,这种只是修改本次连接中的sql_mode。当断开mysql连接后再次连接以上设置会失效。
UPDATE users,
(
SELECT
IFNULL( sum( temp_duration ), 0 ) AS total_duration,
user_id
FROM
(
SELECT
activity_registrations.*
FROM
activity_registrations
LEFT JOIN activities ON activities.id = activity_registrations.activity_id
WHERE
area_3 = 441948
AND activities.enabled = 1
AND checked_out_at IS NOT NULL
GROUP BY
join_activity_time,
activity_registrations.user_id
ORDER BY
join_activity_time ASC
) t
GROUP BY
user_id
) a
SET volunteer_time = total_duration , rank_score = ROUND(total_duration / 3600)
WHERE
users.id = a.user_id
以上语句就可以执行成功了!
本文地址:https://blog.csdn.net/Ty201313/article/details/108992430