当前位置: 代码迷 >> Sql Server >> 帮忙看看这个三表查询如何实现
  详细解决方案

帮忙看看这个三表查询如何实现

热度:52   发布时间:2016-04-27 16:27:33.0
帮忙看看这个三表查询怎么实现?
一个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
  相关解决方案