mysql 使用group by报错 sql_mode=only_full_group_by 在navicat中修改session 中的sql_mode

查看

 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

以上语句就可以执行成功了！