在mysql上查询，出现的一个问题

Question

select s_id, s_name, s_sex
from student
where s_id in (select s_id
        from score
        group by s_id
        having c_id in (select c_id #这里有问题
                from score
                where s_id = 01)
            and count(c_id) = 3)
    and s_id != 01;

执行时报错
ERROR 1054 (42S22) at line 1 in file: 'src1/step2/query2.sql': Unknown column 'c_id' in 'IN/ALL/ANY subquery'
但是单独执行下面的语句的时候，能正常显示出c_id

select c_id
from score
where s_id = 01;

结果如下

+------+
| c_id |
+------+
| 01   |
| 02   |
| 03   |
+------+

https://www.educoder.net/task...这上面的一个题目

---

我还是不理解，如果在having中单独使用的话，而不是通过聚类函数，一定也要在select出现

select s_sex, s_name #这里没有s_name就报错
from student
group by s_sex
having s_name != 'Mia';

select s_sex #这条语句执行正常
from student
group by s_sex
having count(s_name) > 0;

Answer 1

# 1、答：
select s_id, s_name, s_sex
from student
where s_id in (
    select 
        s_id,c_id  # 这里添加c_id即可
    from 
        score
    group by s_id
    having c_id in (select c_id from score where s_id = 01)

    and count(c_id) = 3

) and s_id != 01;

# 2、答：
mysql执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY
也就是说你select的字段必须在group by字段上，不然他不知道，但很有意思，mysql有个sql mode 可以控制这一条件（ONLY_FULL_GROUP_BY ），在5.7.5之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。
也就是说：不开启就可以select不在group by里的字段，因为他是随机找的；但在开启的情况下就会报错。

Answer 2

中间这个字查询有点问题；

select s_id
        from score
        group by s_id
        having c_id in...

having判断的字段应该出现在select的字段当中，比如

select s_id
    ,c_id
    from score...

具体应该怎么处理，还需要题主适当调整一下