mysql查询中oder by 和group by 的问题【要先分组再排序】

做网上的mysql语句练习题，题目如下，

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

我写的mysql语句如下，

SELECT student.`*` FROM student
LEFT JOIN score ON student.s_id = score.s_id
ORDER BY score.c_id
WHERE student.s_id <> '01'
GROUP BY student.s_id
HAVING GROUP_CONCAT(score.c_id) = '01,02,03'

报错如下，

 

 

经过尝试，把这句排序的语句去掉就能正常运行了，ORDER BY score.c_id

为什么order by 语句不能用在那里呢？

走过路过，请大神们指点迷津...

------------------------------------------------------------------------我是第一道分割线----------------------------------------------------------------------

好像是order by 语句与group by 语法不能在一起使用，如下，

SELECT student.`*` FROM student
LEFT JOIN score ON student.s_id = score.s_id
WHERE student.s_id != '01'
ORDER BY score.c_id
GROUP BY student.s_id

这种语句是要报错的。

具体原因待继续定位。。。。。。

----------------------------------------------------------------------我是第二道分割线----------------------------------------------------------------------- 

后面发现 group_concat 函数本来就有排序的功能了，我想要的排序功能可以按照如下语句实现，

SELECT student.* FROM student
LEFT JOIN score ON student.s_id = score.s_id
WHERE student.s_id <> '01'
GROUP BY student.s_id
HAVING GROUP_CONCAT(score.c_id ORDER BY score.c_id SEPARATOR '_') = '01_02_03'

----------------------------------------------------------------------我是最后一道分割线-----------------------------------------------------------------------

问题就是order by 和group by 的问题。但是不是我之前以为的这两个语法不能连用，而是，要先分组再排序！！！！！！！

 改成如下语句就可以了：

SELECT student.`*` FROM student
LEFT JOIN score ON student.s_id = score.s_id
WHERE student.s_id <> '01'
GROUP BY student.s_id
HAVING GROUP_CONCAT(score.c_id) = '01,02,03'
ORDER BY score.c_id