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

偶然看到这样的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');

【【SQL笔试】1.显示的字段要有老师name|【SQL笔试】1.显示的字段要有老师name, age 及每个老师所带的学生人数 2.只列出老师age为40以下学生age为12以上的记录】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');

表数据截图文件

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

文章图片

解题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