偶然看到这样的SQL笔试题

---

数据库有3个表，teacher表 student表 teacher_student关系表  

teacher表 teaID name age  

student表 stuID name age

teacher_student表 teaID stuID  

---

1.显示的字段要有老师name, age 及每个老师所带的学生人数 

2.只列出老师age为40以下学生age为12以上的记录  

首先创建相关表并插入一些测试数据

---

teacher表

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('1', '老师一', '28'); 

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('2', '老师二', '35'); 

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('3', '老师三', '40'); 

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('4', '老师四', '25'); 

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('5', '老师五', '43');

INSERT INTO `test`.`teacher` (`teaId`, `name`, `age`) VALUES ('6', '老师六', '32'); 

---

studen表

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('1', '张三', '15'); 

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('2', '李四', '16');

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('3', '王五', '15'); 

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('4', '赵六', '16');

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('5', '孙七', '15');

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('6', '张三三', '10'); 

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('7', '李四四', '12');

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('8', '王五五', '13'); 

INSERT INTO `test`.`student` (`stuId`, `name`, `age`) VALUES ('9', '赵六六', '10'); 

---

teacher_student关系表

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('1', '3'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('1', '3'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('1', '3'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('2', '4'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('3', '5'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('4', '7'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('4', '9'); 

INSERT INTO `test`.`teacher_student` (`teaId`, `stuId`) VALUES ('5', '8'); 

---

表数据截图文件

---

解题1.显示的字段要有老师name, age 及每个老师所带的学生人数(应该考虑没有学生的老师)

//需要考虑。老师是否有学生，所以需要加入左右连接进行查询。测试只是用左连接实现了 

SELECT t. NAME, t.age, ts.numFROM teacher t

LEFT JOIN ( SELECT teaId, count(stuId) AS num FROM teacher_student GROUP BY teaId) ts ON t.teaId = ts.teaId

---

解题2.只列出老师age为40以下学生age为12以上的记录(只需要列出即可，所以是考虑合并查询，用UNION)

select CONCAT(name,' teacher') as Name,age as tAge from teacher where teacher.age<40

UNION ALL

select CONCAT(name,' student') as Name,age as tAge from student where student.age>12

对数据进行一个区分。使用到了MySQL的拼接字符串函数

以上就是鄙人对这2个题的见解。如有不对 请指出哦。