用到两个表,teacher,student和class
teacher的字段和值如下：
id name sex
1 王老师 男
2 李老师 男
3 余老师 女
4 张老师 女
student的字段和值如下：
id name teacherId
1 张三 1
2 李四 1
3 王五 2
4 宋六 1
5 郑七 2
class的字段和值如下：
id name teacherId
1 初二一班 1
2 初二三班 1
3 初三一班 3
4 初一三班 4
5 初三三班 2
我想查出每个老师在表student和class中出现的次数加老师本身的资料，比如这个例子的结果显示为：
id name sex students classes
1 王老师 男 3 2
2 李老师 男 2 1
3 余老师 女 0 1
4 张老师 女 0 1
这个SQL语句怎么写？
谢谢！
------解决方案--------------------
- SQL code
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 teacherselect 1, '王老师', '男' union allselect 2, '李老师', '男' union allselect 3, '余老师', '女' union allselect 4, '张老师', '女'create table student(id int, name varchar(10), teacherId int)insert into studentselect 1, '张三', 1 union allselect 2, '李四', 1 union allselect 3, '王五', 2 union allselect 4, '宋六', 1 union allselect 5, '郑七', 2create table class(id int, name varchar(14), teacherId int)insert into classselect 1, '初二一班', 1 union allselect 2, '初二三班', 1 union allselect 3, '初三一班', 3 union allselect 4, '初一三班', 4 union allselect 5, '初三三班', 2select 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 teacherselect 1, '王老师', '男' union allselect 2, '李老师', '男' union allselect 3, '余老师', '女' union allselect 4, '张老师', '女'create table student(id int, name varchar(10), teacherId int)insert into studentselect 1, '张三', 1 union allselect 2, '李四', 1 union allselect 3, '王五', 2 union allselect 4, '宋六', 1 union allselect 5, '郑七', 2create table class(id int, name varchar(14), teacherId int)insert into classselect 1, '初二一班', 1 union allselect 2, '初二三班', 1 union allselect 3, '初三一班', 3 union allselect 4, '初一三班', 4 union allselect 5, '初三三班', 2select 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 nulldrop table teachergocreate 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 郑七 2if OBJECT_ID('student') is not nulldrop table studentgocreate table student (id int, name varchar(10),techerId int)insert into student values(1,'张三' ,1)insert into student values(2,'李四' ,1)insert into student values(3,'王五' ,2)insert into student values(4,'宋六' ,1)insert into student values(5,'郑七', 2)--class的字段和值如下：--id name teacherId--1 初二一班 1--2 初二三班 1--3 初三一班 3--4 初一三班 4--5 初三三班 2if OBJECT_ID('class') is not nulldrop table classgocreate table class (id int,name varchar(10), techerId int)insert into class values(1, '初二一班', 1)insert into class values(2 ,'初二三班', 1)insert into class values(3, '初三一班', 3)insert into class values(4, '初一三班', 4)insert into class values(5, '初三三班', 2)--我想查出每个老师在表student和class中出现的次数加老师本身的资料，比如这个例子的结果显示为：--name students classes--王老师 3 2--李老师 2 1--余老师 0 1--张老师 0 1--这个SQL语句怎么写？--谢谢！ select t.name,count(distinct(s.id)) as students,count(distinct(c.name)) as classes from teacher t left join student s on t.id=s.techerId left join class c on t.id=c.techerId group by t.namename students classes---------- ----------- -----------李老师 2 1王老师 3 2余老师 0 1张老师 0 1警告: 聚合或其他 SET 操作消除了 Null 值。(4 行受影响)