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

Question

Department 表有 DepartmentID ,DepartmentNameTeacher 表有 DepartmentID ,TeacherID,TeacherName下面的子查询想统计每个系的教师人数select DepartmentID,
(select count(TeacherID) from Teacher  group by DepartmentID) as 教师数
from Department出错：
消息 512，级别 16，状态 1，第 4 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

Answer 1

select DepartmentName,count(TeacherID) from Department,Teacherwhere Teacher.DepartmentID=Department.DepartmentIDgroup by DepartmentName

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

create table Department 
(
DepartmentID int ,
DepartmentName varchar(100))create table Teacher
(
DepartmentID int ,
TeacherID int ,
TeacherName varchar(100)
)DELETE Department
insert into Department
select 1, 'AAA' union all
select 2,'BBB' union all
select 3, 'CCC'insert into Teacher
select 1,101,'WACKY' UNION ALL
SELECT 1,102,'XIAOWANG' UNION ALL
SELECT 2,201,'WIXAOZHANG' UNION ALL
SELECT 3,301,'JACKY' UNION ALL
SELECT 3,302,'ANDY' UNION ALL
SELECT 3,303,'JAKY'

Answer 6

我想要的是在 select 字段中也使用 select 子语句

Answer 7

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

Answer 8

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