name       students    classes
---------- ----------- -----------
王老师        3           2
李老师        2           1
余老师        0           1
张老师        0           1

(4 row(s) affected)

------解决方案--------------------
SQL code

create table teacher
(id int, name varchar(10), sex varchar(4))

insert into teacher
select 1, '王老师', '男' union all
select 2, '李老师', '男' union all
select 3, '余老师', '女' union all
select 4, '张老师', '女'

create table student
(id  int, name varchar(10), teacherId int)

insert into student
select 1, '张三', 1 union all
select 2, '李四', 1 union all
select 3, '王五', 2 union all
select 4, '宋六', 1 union all
select 5, '郑七', 2

create table class
(id int, name varchar(14), teacherId int)

insert into class
select 1, '初二一班', 1 union all
select 2, '初二三班', 1 union all
select 3, '初三一班', 3 union all
select 4, '初一三班', 4 union all
select 5, '初三三班', 2


select t.name,
       (select count(1) from student s where s.teacherId=t.id) 'students',
       (select count(1) from class c where c.teacherId=t.id) 'classes'   
from teacher t

/*
name       students    classes
---------- ----------- -----------
王老师        3           2
李老师        2           1
余老师        0           1
张老师        0           1

(4 row(s) affected)
*/

------解决方案--------------------
SQL code

create table teacher
(id int, name varchar(10), sex varchar(4))

insert into teacher
select 1, '王老师', '男' union all
select 2, '李老师', '男' union all
select 3, '余老师', '女' union all
select 4, '张老师', '女'

create table student
(id  int, name varchar(10), teacherId int)

insert into student
select 1, '张三', 1 union all
select 2, '李四', 1 union all
select 3, '王五', 2 union all
select 4, '宋六', 1 union all
select 5, '郑七', 2

create table class
(id int, name varchar(14), teacherId int)

insert into class
select 1, '初二一班', 1 union all
select 2, '初二三班', 1 union all
select 3, '初三一班', 3 union all
select 4, '初一三班', 4 union all
select 5, '初三三班', 2


select t.name,t.sex,
       (select count(1) from student s where s.teacherId=t.id) 'students',
       (select count(1) from class c where c.teacherId=t.id) 'classes'   
from teacher t

/*
name       sex  students    classes
---------- ---- ----------- -----------
王老师        男    3           2
李老师        男    2           1
余老师        女    0           1
张老师        女    0           1

(4 row(s) affected)
*/

------解决方案--------------------
SQL code
--用到两个表,teacher,student和class

--teacher的字段和值如下：
--id name sex
--1 王老师 男
--2 李老师 男
--3 余老师 女
--4 张老师 女
if OBJECT_ID('teacher') is not null
drop table teacher
go
create table teacher (id int ,name varchar(10),sex char(2))
insert into teacher values(1 ,'王老师' ,'男')
insert into teacher values(2 ,'李老师', '男')
insert into teacher values(3 ,'余老师', '女')
insert into teacher values(4 ,'张老师' ,'女')

--student的字段和值如下：
--id name teacherId
--1 张三 1
--2 李四 1
--3 王五 2
--4 宋六 1
--5 郑七 2

if OBJECT_ID('student') is not null
drop table student
go
create table student (id int, name varchar(10),techerId int)
insert into student values(1,'张三' ,1)
insert into student values(2,'李四' ,1)
i