sql select语句优化有关问题

sql select语句优化问题
我在用下面的sql语句查询数据的时候发现 select DISTINCT cno from Course where teacher='老师1' 语句执行了两次的样子
SELECT sno from SC  
WHERE cno in (select DISTINCT cno from Course where teacher='老师1') GROUP BY sno 
having COUNT(cno) = (select count(DISTINCT cno) from Course where teacher='老师1');

在执行计划里能看到两个参数一样的表扫描,请问有什么办法能合并?
还有我这个语句还有其他可以优化的地方吗?

[解决办法]

SQL code

Select SNo From SC a Inner Join Course b On a.CNo=b.CNoWhere b.Teacher = '老师1'Group By SNoHaving 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.cnoWhere b.teacher = '老师1'Group By snohaving count(a.cno) = count(Distinct b.cno)
[解决办法]
楼主，你的语句好像不能再优化了。
[解决办法]
SELECTA.sno
FROMSC 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
WHEREB.teacher='老师1'
GROUP BY A.sno
HAVING COUNT(A.sno)>1
[解决办法]
或者这样：
SELECTA.sno
FROMSC 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
WHEREB.teacher='老师1'
GROUP BY A.sno
HAVING COUNT(A.sno)=(
SELECTCOUNT(1)
FROMCourse WITH(NOLOCK)
WHEREteacher='老师1'
GROUP BY teacher
)