请教下子查询的用法-- 想统计每个系的教师人数
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
