溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
學生表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 = '張老師'
);
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
