SELECT s.subject_id
, s.subject_name
, t.class_id
, t.section_id
, c.teacher_id
FROM school_timetable_content c
JOIN s
ON c.subject_id = s.subject_id
JOIN school_timetables t
ON t.timetable_id = c.timetable_id
WHERE c.teacher_id = 184
AND t.class_id = 24
AND t.school_id = 28
从上面的查询中，我得到了如下所示的结果:-
同样，从上面的结果中，我想获得与所有唯一的section_id15，16，26相关联的主题。 即预期输出印地语，数学
我们的想法是过滤这三个部分。 如果三者都存在，则进行聚合和计数:
SELECT s.subject_id, s.subject_name
FROM school_timetable_content tc JOIN
school_subjects s
ON tc.subject_id = s.subject_id JOIN
school_timetables t
ON t.timetable_id = tc.timetable_id
WHERE tc.teacher_id = 184 AND
t.class_id = 24 AND
t.school_id = 28 AND
t.section_id IN (15, 16, 26)
GROUP BY s.subject_id, s.subject_name
HAVING COUNT(*) = 3;
这假定section_id不重复。 如果可能的话，请使用having(COUNT(DISTINCT section_id))=3。
请注意，使用表别名使查询更易于编写和读取。
