SQL 50 题 第九题

Question

原文 https://blog.csdn.net/qq_41080850/article/details/84648897 最后

 select * from student where sid in
    (select sid from
    (select sid,cid from grade where sid in
    (select sid from grade where sid !='06' group by sid
    having count(*)=(select count(*) from grade where sid='06' group by sid))) as t1
    inner join (select cid from grade where sid='06') as t2
    on t1.cid = t2.cid
    group by t1.sid 
    having count(*)=(select count(*) from grade where sid='06'));

这是原文最后的另一种求法，最后一句 为什么不可以改成 having count(cid)=(select count(*) from grade where sid='06'));

其他的都可以将 （*）换成（cid）

另外 用 on t1.cid = t2.cid 的原因是因为固定课程吗？这里用cid相同有点懂又有点不懂。

Answer 1

不是不可以。其实是可以的。只是你改成having count(cid)=(select count(*) from grade where sid='06'))会报错：

Column 'cid' in having clause is ambiguous意思是存在多个相同的字段名。

你需要指明某一个表中的字段名就好了。指定t1或者t2的。

 having count(t1.cid)=(select count(*) from grade where sid='06'));