mysql - 以下查询的查询是什么？

Question

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_id 15, 16,26相关联的主题。即预期输出印地语，数学

标签： mysqlsqldatabaseqsqlquerymysql-select-db

Answer 1

我们的想法是过滤这三个部分。如果所有三个都存在，则聚合并计数：

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改用。

请注意，表别名的使用使查询更易于编写和阅读。