SELECT student.name FROM student WHERENOTEXISTS
(
SELECT * FROM course WHERENOTEXISTS
(
SELECT * FROM student_course WHERE student_course.stuid=student.id
AND course.id=student_course.courseid
)
)
筛选至少选秀了学生A选修的所有课程的学生
SELECT * FROM student,
(SELECTDISTINCT stuid FROM student_course sca
WHERENOTEXISTS
(
SELECT * FROM student_course scb WHERE (scb.stuid=2 ANDNOTEXISTS (SELECT * FROM student_course scc
WHERE scc.stuid=sca.stuid AND scc.courseid=scb.courseid)
)
)) AS temp WHERE temp.stuid=student.id
优化:筛选至少选秀了学生A选修的所有课程的学生
SELECT [name] AS StudentName FROM student WHERE id in
(SELECTDISTINCT stuid FROM student_course sca
WHERENOTEXISTS
(
SELECT * FROM student_course scb WHERE (scb.stuid=2 ANDNOTEXISTS (SELECT * FROM student_course scc
WHERE scc.stuid=sca.stuid AND scc.courseid=scb.courseid)
)
))
筛选选修全部课程的学生 SELECT student.name FROM student WHERE NOT EXISTS( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM student_course WHERE student_course.stuid=stude...