1.显示的字段要有老师name, age 每个老师所带的学生人数,2 只列出老师age为40以下学生age为12以上的记录

drop table if exists tea_stu;
drop table if exists teacher;
drop table if exists student;


create table teacher(teaID int primary key,name varchar(50),age int);
create table student(stuID int primary key,name varchar(50),age int);
create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));

delete from teacher;
delete from student;
delete from tea_stu;

insert into teacher(teaID, name, age) values(1,'zxx',45), (2,'lhm',25), (3,'wzg',26) , (4,'tg',27);
insert into student(stuID, name, age) values(1,'wy',11), (2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);
insert into tea_stu(teaID, stuID) values(1,1), (1,2), (1,3);
insert into tea_stu(teaID, stuID) values(2,2), (2,3), (2,4);
insert into tea_stu(teaID, stuID) values(3,3), (3,4), (3,1);
insert into tea_stu(teaID, stuID) values(4,4), (4,1), (4,2) , (4,3);

select * from teacher;
select * from student;
select * from tea_stu;

/*1.显示的字段要有老师name, age 每个老师所带的学生人数 
 *2.只列出老师age为40以下学生age为12以上的记录
 */
 
/*要会统计分组信息*/
select teaid, count(*) from tea_stu group by teaid;
+-------+----------+
| teaid | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        3 |
|     4 |        4 |
+-------+----------+

select * from tea_stu,teacher where tea_stu.teaID=teacher.teaID;
+-------+-------+-------+------+------+
| teaID | stuID | teaID | name | age  |
+-------+-------+-------+------+------+
|     1 |     1 |     1 | zxx  |   45 |
|     1 |     2 |     1 | zxx  |   45 |
|     1 |     3 |     1 | zxx  |   45 |
|     2 |     2 |     2 | lhm  |   25 |
|     2 |     3 |     2 | lhm  |   25 |
|     2 |     4 |     2 | lhm  |   25 |
|     3 |     3 |     3 | wzg  |   26 |
|     3 |     4 |     3 | wzg  |   26 |
|     3 |     1 |     3 | wzg  |   26 |
|     4 |     4 |     4 | tg   |   27 |
|     4 |     1 |     4 | tg   |   27 |
|     4 |     2 |     4 | tg   |   27 |
|     4 |     3 |     4 | tg   |   27 |
+-------+-------+-------+------+------+

/*再接着去掉大于40的老师：*/
select * from tea_stu,teacher where tea_stu.teaID=teacher.teaID and teacher.age<=40;
+-------+-------+-------+------+------+
| teaID | stuID | teaID | name | age  |
+-------+-------+-------+------+------+
|     2 |     2 |     2 | lhm  |   25 |
|     2 |     3 |     2 | lhm  |   25 |
|     2 |     4 |     2 | lhm  |   25 |
|     3 |     3 |     3 | wzg  |   26 |
|     3 |     4 |     3 | wzg  |   26 |
|     3 |     1 |     3 | wzg  |   26 |
|     4 |     4 |     4 | tg   |   27 |
|     4 |     1 |     4 | tg   |   27 |
|     4 |     2 |     4 | tg   |   27 |
|     4 |     3 |     4 | tg   |   27 |
+-------+-------+-------+------+------+

/*再对上面的结果去掉小于12的学生*/
select * from 
(
	select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.age
	from tea_stu,teacher where tea_stu.teaID=
	teacher.teaID and teacher.age<40
) as t,
student 
where t.stuid=student.stuid and student.age>12;
+-------+-------+------+------+-------+------+------+
| teaID | stuID | name | age  | stuID | name | age  |
+-------+-------+------+------+-------+------+------+
|     2 |     2 | lhm  |   25 |     2 | dh   |   25 |
|     4 |     2 | tg   |   27 |     2 | dh   |   25 |
|     2 |     3 | lhm  |   25 |     3 | ysq  |   26 |
|     3 |     3 | wzg  |   26 |     3 | ysq  |   26 |
|     4 |     3 | tg   |   27 |     3 | ysq  |   26 |
|     2 |     4 | lhm  |   25 |     4 | mxc  |   27 |
|     3 |     4 | wzg  |   26 |     4 | mxc  |   27 |
|     4 |     4 | tg   |   27 |     4 | mxc  |   27 |
+-------+-------+------+------+-------+------+------+


/*再对上面的结果进行统计，显示的是老师的id和组信息*/
select t.teaID,count(*) from 
(
	select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.age
	from tea_stu,teacher where tea_stu.teaID=
	teacher.teaID and teacher.age<40
) as t,
student 
where t.stuid=student.stuid and student.age>12
group by t.teaID;
+-------+----------+
| teaID | count(*) |
+-------+----------+
|     2 |        3 |
|     3 |        2 |
|     4 |        3 |
+-------+----------+

/*然后对上面的东西进行改写，改写成显示老师的名字*/
select teacher.name,t2.c from
(
	select t.teaID,count(*) c from 
	(
		select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.age 
		from tea_stu,teacher where tea_stu.teaID=
		teacher.teaID and teacher.age<40
	) as t,
	student 
	where t.stuid=student.stuid and student.age>12
	group by t.teaID
) as t2,
teacher
where teacher.teaID=t2.teaID;
+------+---+
| name | c |
+------+---+
| lhm  | 3 |
| wzg  | 2 |
| tg   | 3 |
+------+---+

/*第二种写法：*/
select teacher.teaID, teacher.name, t1.total 
from teacher,
(
	select teaID,count(tea_stu.stuID) total 
	from tea_stu, student 
	where tea_stu.stuID = student.stuID and student.age>12
	group by teaID 
) as t1
where teacher.teaID = t1.teaID and teacher.age<40 ;