学生表student(id,name)老师表teacher(id,name)课程表lesson(id,name)老师和课程关联表(id,teacher_id,lesson_id)学生和课程关联表(id,st
学生表student(id,name)
老师表teacher(id,name)
课程表lesson(id,name)
老师和课程关联表(id,teacher_id,lesson_id)
学生和课程关联表(id,student_id,lesson_id)
查询王老师的课程
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 = '张老师'
);
--结束END--
本文标题: 多表查询sql语句(5表)
本文链接: https://www.lsjlt.com/news/50312.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0