多表查询sql语句
发布时间：2022-03-18 13:01:47 所属栏目：MySql教程 来源：互联网
导读：学生表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
|
学生表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 = '张老师' ); （编辑：滁州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读