sql查询中的错误（group by）

查询语句

SELECT
        r.post,
        t.management,
        t.fcreate_username,
        (SUM(CASE WHEN t.attendance_month = '1' THEN r.monthly ELSE 0 END)
             +SUM(CASE WHEN t.attendance_month = '2' THEN r.monthly ELSE 0 END)
             +SUM(CASE WHEN t.attendance_month = '3' THEN r.monthly ELSE 0 END))as totalAll
        FROM
        biz_contractor_attendance_record_remark r
        JOIN biz_contractor_attendance t ON r.attendance_id = t.attendance_id
where  t.attendance_id >0
        GROUP BY
        r.post,
        t.management
        HAVING
        totalAll <> 0

错误 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ots.t.fcreate_username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

经学习之后的解决方法：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

用了之后确实 在navicat中不管运行多少次都不会报错，去网上问了一下 也没有解决

最后问了身边的大佬才知道 原来在新sql这种时候要把查询的字段全部需要groub by分组

解决方法：

查询什么就分组什么

SELECT
        r.post,
        t.management,
        t.fcreate_username,
        (SUM(CASE WHEN t.attendance_month = '1' THEN r.monthly ELSE 0 END)
             +SUM(CASE WHEN t.attendance_month = '2' THEN r.monthly ELSE 0 END)
             +SUM(CASE WHEN t.attendance_month = '3' THEN r.monthly ELSE 0 END))as totalAll
        FROM
        biz_contractor_attendance_record_remark r
        JOIN biz_contractor_attendance t ON r.attendance_id = t.attendance_id
where  t.attendance_id >0
		GROUP BY
		r.post,
		t.management,t.fcreate_username
		HAVING
		totalAll <>0