学步园

select t1.teacherId,t1.teacherName,sum(case when t2.teacherId=t1.teacherId then 1 else 0 end) as Num
from testTeacher as t1,testStudent as t2

group by t1.teacherId,t1.teacherName
order by t1.teacherId

select t.teacherId,t.teacherName,isnull(counts,0) as stuNum from testTeacher as t
left join
(
select count(teacherId) counts,teacherId from testStudent
group by teacherId
) as c on t.teacherId=c.teacherId