MySQL5.7对分组查询结果列计数/求和报GROUP BY错误如何解决

核心问题说明 #

你遇到的1111错误并非嵌套查询的固有语法问题，报错通常是因为混用了聚合函数与非聚合字段的返回结果。多层嵌套无法动态传入userid的问题，可通过关联派生表的方式解决，无需升级MySQL版本。

可直接落地的实现方案 #

方案1：调整分组逻辑直接统计（无需多层嵌套） #

如果统计规则为用户只要有任意一次尝试通过就算该课程通过，可直接修改原查询的聚合逻辑，支持直接接收外层传入的动态userid：

SELECT 
  COUNT(DISTINCT CASE 
    WHEN 
      COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND t1.value IN ('passed','completed') THEN 1 ELSE NULL END) = 0 
      OR MOD(
        COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND t1.value IN ('passed','completed') THEN 1 ELSE NULL END),
        (SELECT COUNT(sc9.id) FROM mdl_scorm s9 LEFT JOIN mdl_scorm_scoes sc9 ON s9.id = sc9.scorm WHERE s9.course = c1.id AND sc9.launch = '')
      ) > 0
    THEN NULL ELSE c1.id END
  ) AS complete
FROM mdl_user u1
LEFT JOIN mdl_scorm_scoes_track t1 ON t1.userid = u1.id
LEFT JOIN mdl_scorm s1 ON s1.id = t1.scormid
LEFT JOIN mdl_course c1 ON c1.id = s1.course
LEFT JOIN mdl_scorm_scoes sc1 ON s1.id = sc1.scorm
WHERE u1.id = @dynamic_user_id -- 此处可直接替换为外层传入的动态用户ID
  AND sc1.launch = ''
GROUP BY c1.id, t1.attempt

方案2：关联派生表实现动态传参 #

如果需要保留单门课程单次尝试的通过状态计算逻辑，可通过关联子查询的方式实现参数透传：

SELECT 
  u.id AS user_id,
  COUNT(CASE WHEN t.refname = 1 THEN 1 END) AS complete
FROM mdl_user u
LEFT JOIN (
  SELECT 
    t1.userid,
    c1.id AS course_id,
    CASE
      WHEN COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND t1.value IN ('passed','completed') THEN 1 END) = 0 THEN 0
      WHEN MOD(
        COUNT(CASE WHEN t1.element = 'cmi.core.lesson_status' AND t1.value IN ('passed','completed') THEN 1 END),
        (SELECT COUNT(sc9.id) FROM mdl_scorm s9 LEFT JOIN mdl_scorm_scoes sc9 ON s9.id = sc9.scorm WHERE s9.course = c1.id AND sc9.launch = '')
      ) > 0 THEN 0
      ELSE 1
    END AS refname
  FROM mdl_scorm_scoes_track t1
  LEFT JOIN mdl_scorm s1 ON s1.id = t1.scormid
  LEFT JOIN mdl_course c1 ON c1.id = s1.course
  LEFT JOIN mdl_scorm_scoes sc1 ON s1.id = sc1.scorm
  WHERE sc1.launch = ''
  GROUP BY t1.userid, c1.id, t1.attempt
) t ON t.userid = u.id
WHERE u.id = @dynamic_user_id -- 此处可直接替换为外层传入的动态用户ID
GROUP BY u.id

海量数据场景优化建议 #

可提前为以下字段添加联合索引，大幅提升查询效率：

• mdl_scorm_scoes_track(userid, scormid, element, value)
• mdl_scorm_scoes(scorm, launch)
• mdl_scorm(course, id)

内容的提问来源于stack exchange，提问作者CDP