请教下子查询的用法-- 想统计每个系的教师人数

Department   表有   DepartmentID   ,DepartmentName

Teacher   表有   DepartmentID   ,TeacherID,TeacherName

下面的子查询想统计每个系的教师人数

select   DepartmentID,
(select   count(TeacherID)   from   Teacher     group   by   DepartmentID)   as   教师数
from   Department

出错：
消息   512，级别   16，状态   1，第   4   行
子查询返回的值不止一个。当子查询跟随在   =、!=、 <、 <=、> 、> =   之后，或子查询用作表达式时，这种情况是不允许的。


------解决方案--------------------
select
D.DepartmentID,D.DepartmentName,count(T.TeacherID) as 教师数
from
Department D,Teacher T
where
D.DepartmentID=T.DepartmentID
group by
D.DepartmentID,D.DepartmentName
------解决方案--------------------
select DepartmentID,
(select count(TeacherID) from Teacher group by DepartmentID) as 教师数
from Department

你这个
(select count(TeacherID) from Teacher group by DepartmentID)
会返回多个值,不同的DepartmentID,不同的count(TeacherID)

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

select DepartmentID,

(select count(TeacherID) from Teacher b
where a.DepartmentID=b.DepartmentID
group by DepartmentID) as 教师数

from Department a
------解决方案--------------------

select A.DepartmentID, (select count(TeacherID) from Teacher B where A.DepartmentID=B.DepartmentID
group by DepartmentID) as 教师数

from Department A