查找具有相同条目的条目具有多个条目及其详细信息

Question

我需要帮助编写一个查询，这将拉出一个条目，有相同的有很多。教师可以选择教授任何科目，学生可以选择教授任何科目。需要找到一位老师，他已经选择教授与学生选择的相同的科目组合。

表学生

id name
1  x
2  y

表student_Subjects

id subject_id student_id
1  1          1
2  2          1
3  1          2

教师

id name
1  tx
2  ty

表teacher_subjects

id subject_id teacher_id
1  1          2
2  2          2
3  1          1

主题

id name
1  English
2  Maths

现在需要找到一位选择教授与学生x相同的科目的老师，以及学生和老师的名字。

我也问过类似的问题，结果没有学生姓名和老师姓名。以下是对此的查询：

 SELECT GROUP_CONCAT(subject_id ORDER BY subject_id) as teacher_concat ,teacher_id
       FROM teacher_subjects 
       GROUP BY teacher_id 
       HAVING teacher_concat IN
                    (SELECT group_concat(subject_id ORDER BY subject_id)
                     FROM student_subjects GROUP BY student_id)

Answer 1

我认为这应该行得通：

select
        teacher.id, 
        teacher.name,
        student.id,
        student.name
from
        teacher,
        student,
        (select
                teacher_id,
                GROUP_CONCAT(
                        distinct subject_id
                        order by subject_id
                        separator ' '
                ) x
        from
                teacher_subjects
        group by
                teacher_id
        ) t,
        (select
                student_id
                GROUP_CONCAT(
                        distinct subject_id
                        order by subject_id
                        separator ' '
                ) x
        from
                student_subjects
        group by
                student_id
        ) s
where
        t.x=s.x and
        t.teacher_id=teacher.teacher_id and
        s.student_id=student.student_id