select * from LessonStudent a where exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s1') and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s2') and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s3') and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s4')
您的答案一定會包括(s1, s2, s6, s7)這個課程,但是這個課程只是學生數目等於4,並不是包含了上述的4個學生s1, s2, s3, s4
您沒有使用提供的已知條件。
sorry, 答案錯誤。 :)
where
exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s1')
and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s2')
and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s3')
and exists (select 1 from LessonStudent where LessonID=a.LessonID and StudentID='s4')