这样的SQL如何写呢

这样的SQL怎么写呢？
我有两张表tblClass_Student和tblGroup_Student
其中tblClass_Student有两个列ClassID,StudentID;
tblGroup_Student有两个列GroupID,StudentID

我现在想知道那些ClassID包含了所有GroupID=1的学生。

谢谢

------解决方案--------------------

SQL code

SELECT 
    DISTINCT ClassID
FROM tblClass_Student AS a
    INNER JOIN tblGroup_Student AS b ON a.StudentID=b.StudentID
WHERE b.GroupID=1

------解决方案--------------------
上一种方法效率高

再舉一方法

SQL code

Select  [ClassID]
from tblClass_Student AS a
WHERE  EXISTS(SELECT 1 FROM tblGroup_Student  WHERE [GroupID]=1 AND [StudentID]=a.[StudentID]) 
GROUP BY [ClassID]
HAVING COUNT([StudentID])=(SELECT COUNT([StudentID]) FROM tblGroup_Student WHERE [GroupID]=1)

------解决方案--------------------
Select  [ClassID]
from tblClass_Student AS a
WHERE  EXISTS(SELECT 1 FROM tblGroup_Student  WHERE [GroupID]=1 AND [StudentID]=a.[StudentID])  
GROUP BY [ClassID]
HAVING COUNT([StudentID])=(SELECT COUNT([StudentID]) FROM tblGroup_Student WHERE [GroupID]=1)
同意楼上

相关推荐