你可以用 CTE 以避免重复代码。

with
    cte
as
(
    select
        studentID
    from
        [Apply]
    where
        cName   in('College1', 'College2')
)
select
    s.sName
from
    cte
inner join
    Student s
on
    cte.studentID = s.studentID;

您可以使用以下两个条件检查它是否存在：

select * from Student s
where exists 
(select * from Apply a where a.StudentId = s.StudentId and a.cName = 'College1') and
exists 
(select * from Apply a where a.StudentId = s.StudentId and a.cName = 'College2');

通过执行两个子查询（一个子查询获取应用于college1的studentid，另一个子查询获取应用于college2的studentid），可以确保从student返回的学生在两个子查询结果集中都存在studentid。

SELECT sName
FROM Student 
WHERE studentID IN (
    SELECT DISTINCT studentID FROM Apply WHERE cName='College1'
)
AND studentID IN (
    SELECT DISTINCT studentID FROM Apply WHERE cName='College2'
)