Select SNo From SC a 
Inner Join Course b 
On a.CNo=b.CNo
Where b.Teacher = '老师1'
Group By SNo
Having Count(a.CNo) = Count(Distinct b.CNo)

------解决方案--------------------
参考

SQL code
WITH   TT
          AS ( SELECT   cno ,
                        COUNT(1) AS num
               FROM     dbo.course
               WHERE    teacher = '老师1'
             )
             
    SELECT  sno
    FROM    ( SELECT    sno ,
                        COUNT(1) AS numb
              FROM      SC B
              WHERE     EXISTS ( SELECT 1
                                 FROM   TT
                                 WHERE  B.cno = cno )
            ) C
            INNER JOIN TT ON C.numb = TT.num

------解决方案--------------------
不好意思，看错了。上面的不对
SQL code

Select SNo From SC a inner join Course b On a.cno=b.cno
Where b.teacher = '老师1'
Group By sno
having count(a.cno) = count(Distinct b.cno)

------解决方案--------------------
楼主，你的语句好像不能再优化了。

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

SELECT	A.sno
FROM	SC AS A WITH(NOLOCK) INNER JOIN
		Course AS B WITH(NOLOCK) ON A.cno=B.cno INNER JOIN
		Student AS C WITH(NOLOCK) ON A.sno=C.sno
WHERE	B.teacher='老师1'
GROUP BY A.sno
HAVING COUNT(A.sno)>1

------解决方案--------------------
或者这样：
SELECT	A.sno
FROM	SC AS A WITH(NOLOCK) INNER JOIN
		Course AS B WITH(NOLOCK) ON A.cno=B.cno INNER JOIN
		Student AS C WITH(NOLOCK) ON A.sno=C.sno
WHERE	B.teacher='老师1'
GROUP BY A.sno
HAVING COUNT(A.sno)=(
						SELECT	COUNT(1)
						FROM	Course WITH(NOLOCK)
						WHERE	teacher='老师1'
						GROUP BY teacher
					)