一个teacher表，表中有teacherId作为主键；一个class表，表中有classId作为主键，并有teacherId字段；一个student表，表中有studentId作为主键，并有classId字段。
现在要查询：teacher表中的所有记录，并显示每个teacher所带的class总数，student总数。
请问怎么实现？
------解决方案--------------------
select *,
[class总数]=(select count(*) from class where teacherId=tmp.teacherId)
[student总数]=(select count(*) from student left class on student.classId=class.classId where class.teacherId=tmp.teacherId)
from teacher as tmp
------解决方案--------------------
不考虑效率
select t.*,
(select count(*) from class where teacherId=t.teacherId) as class总数,
(select count(distinct studentId) from student s,class c where c.teacherId=t.teacherId and s.classid=c.classid) as student总数
teacher t
------解决方案--------------------
select a.*,
(select count(*) from class b where b.teacherId=a.teacherId) as class,
(select count(*) from student c where c.classId in (select classId from class where class.teacherId=a.teacherId)) as student
from teacher a