MySQL GROUP BY语法错误排查：仅全分组模式下的查询修复

问题原因 #

• 语法顺序错误：HAVING子句位置违反MySQL执行逻辑，正确顺序应为WHERE → GROUP BY → HAVING，原查询将HAVING放在GROUP BY之前，直接触发语法错误。
• 违反only_full_group_by规则：SELECT列表中的bj.b_s_id字段未包含在GROUP BY列表中，该模式要求所有非聚合函数的字段必须出现在GROUP BY声明里。
• 子查询冗余：WHERE子句中的子查询已通过bjhs.b_j_id = bj.id限定单条记录范围，GROUP BY bjhs.b_j_id和LIMIT 1属于多余写法，无实际作用。

修复方法 #

1. 调整语句顺序：将GROUP BY子句移至HAVING之前。
2. 补全GROUP BY字段：添加bj.b_s_id到GROUP BY列表，满足only_full_group_by模式要求。
3. 简化子查询：移除子查询中的GROUP BY和LIMIT 1，直接取MIN(bjhs.created_at)。

修正后的SQL #

select bj.id,
       bj.m_id,       
       bj.b_s_id,
       bj.contact_phone,
       bj.f_a_date,
       bj.source_request_id,
       mw.w_id,
       COUNT(bjhs.id) as hs_count,
       CAST(IFNULL(SUM(bjhs.status), 0) as UNSIGNED) as hs_status
from b_j bj
inner join m_w mw on bj.m_id = mw.m_id
inner join b_j_h_s bjhs on bj.id = bjhs.b_j_id
inner join m_j mj on mj.m_id = bj.m_id
    and mj.j_id = 4
inner join m_w_j mwj on mwj.m_j_id = mj.id
    and mwj.m_w_id = mw.id
where bj.b_s_id = 2
    and DATEDIFF(CURDATE(), (select min(bjhs.created_at)
        from b_j_h_s bjhs
        where bjhs.b_j_id = bj.id)) >= 35
    and bj.deleted_at is null
group by bj.id,
       bj.m_id,
       bj.b_s_id,
       bj.contact_phone,
       bj.f_a_date,
       bj.source_request_id,
       mw.w_id
having hs_count != hs_status

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