mysql中GROUP BY结合GROUP_CONCAT的使用时蒙逼

Question

SELECT
    a.id,
    a.school_id,
    a.branch_id,
    a.branch_class_no,
    GROUP_CONCAT( DISTINCT a.id ) AS class_ids,////////////////注意这里
    b.id frist_lesson_id,
    b.classin_file_id,
    b.start_time
FROM
    a
    RIGHT JOIN (select * from bbbb where start_time > 1550160000 AND start_time < 1561910400 AND `status` = 3 and classin_file_name like "%NCE1%" ORDER BY start_time asc ) b ON a.id = b.class_id
WHERE
    a.branch_class_no <> '' 
    AND a.school_id = 54
GROUP BY
    a.school_id,
    a.branch_id,
    a.branch_class_no

正确结果：

当去掉AND a.school_id = 54时取到的last_lesson_id结果是错误

再去掉GROUP_CONCAT( DISTINCT a.id ) AS class_ids 时，结果又是对的。

求大神指导啊。

Answer 1

group_concat():将group by产生的同一个分组中的值连接起来，返回一个字符串结果.

Answer 2

建议启用严格模式

SHOW VARIABLES LIKE '%sql_mode%';

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

https://dev.mysql.com/doc/ref...