多表查询sql语句(5表)

学生表student(id,name)
老师表teacher(id,name)
课程表lesson(id,name)
老师和课程关联表(id,teacher_id,lesson_id)
学生和课程关联表(id,student_id,lesson_id)

创新互联是一家专业提供日照企业网站建设,专注与网站建设、成都做网站html5、小程序制作等业务。10年已为日照众多企业、政府机构等服务。创新互联专业的建站公司优惠进行中。

查询王老师的课程
SELECT t.name AS '老师',l.name AS '课程' FROM teacher t
LEFT JOIN teacher_lesson teal ON t.id = teal.teacher_id
LEFT JOIN lesson l ON l.id = teal.lesson_id WHERE t.name = '王老师'

查询上了王老师或张老师课的学生

SELECT s.name AS '学生',l.name AS '课程',t.name AS '老师' FROM student s
LEFT JOIN student_lesson stul ON s.id = stul.student_id
LEFT JOIN lesson l ON l.id = stul.lesson_id
LEFT JOIN teacher_lesson teal ON teal.lesson_id = l.id
LEFT JOIN teacher t ON t.id = teal.teacher_id WHERE t.name = '王老师' OR t.name = '张老师'

查询即上王老师又上张老师课的学生
SELECT * FROM student s WHERE EXISTS (
SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id
JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = '王老师'
) AND EXISTS (
SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id
JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = '张老师'
);


分享题目:多表查询sql语句(5表)
转载注明:http://pwwzsj.com/article/gipdih.html