偶然看到这样的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 及每个老师所带的学生人数(应该考虑没有学生的老师)
//需要考虑。老师是否有学生,所以需要加入左右连接进行查询。测试只是用左连接实现了 如果为了null显示0 MySQL使用ifnull即可 Oracle使用nvl
SELECT t. NAME, t.age, ts.num FROM 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 > 1
对数据进行一个区分。使用到了MySQL的拼接字符串函数
以上就是鄙人对这2个题的见解。如有不对 请指出哦。