查询和“s002”号的同学学习的课程完全相同的其他同学的个人信息

马里奥——马

已于 2023-03-01 13:35:18 修改

阅读量332

点赞数 3

文章标签：学习数据库 sql

于 2022-08-22 12:31:27 首次发布

本文链接：https://blog.csdn.net/cy__CSDN/article/details/126463473

版权

select    
    sth.sno,
    sth.sname 
from student sth,
    (select * from sc where sno='s002') a,
    (select * from sc where sno<>'s002') b
where 
    b.sno=sth.sno
having
    sum(case when a.cno=b.cno then 0 else 1 end)    --使用case进行计数
    =
    (select count(cno) from sc where sno='s002')
group by sth.sno,sth.sname

select student.sno,student.sname from student where student.sno in(
select sc.sno from sc where sc.sno != 's002'
and sc.cno in (
select cno from sc where sc.sno = 's002'
) group by sc.sno
having count(sc.cno) = (select count(cno) from sc where sc.sno = 's002' )
intersect
select sc.sno from sc where sc.sno != 's002' group by sc.sno
having count(sc.cno) = (select count(cno) from sc where sc.sno = 's002')
);