逻辑:
如果一个人所选课程有s001选择的课程,
且没有选择与s001不同的课程,
且和s001选择的课程数量相同,
这个人还不是s001,那这个人选择的课程和s001一样。
表sc(sno 学号,cno 课程编号,score 分数)
表student(sno 学号,sname 姓名,sage 年龄,ssex 性别)
SELECT S.SNO, S.SNAME
FROM (SELECT SC1.SNO
FROM (SELECT SC.*
FROM SC
WHERE SC.CNO IN
(SELECT SC.CNO FROM SC WHERE SC.SNO = 's001') --s001选的课程
AND SC.CNO NOT IN
(SELECT SC.CNO FROM SC WHERE SC.CNO NOT IN (SELECT SC.CNO FROM SC WHERE SC.SNO = 's001')) --s001没有选的课程
AND SC.SNO != 's001') SC1 --不是s001
GROUP BY SC1.SNO
HAVING COUNT(DISTINCT(SC1.CNO)) = (SELECT COUNT(DISTINCT(SC.CNO)) FROM SC WHERE SC.SNO = 's001' GROUP BY SC.SNO)) SC2 --和s001选择的课程相同
JOIN STUDENT S
ON S.SNO = SC2.SNO;