SQL查询以打印作为对称对的一部分的学生的名称。

Question

给定表， science_marks

学生

• student_id (主密钥)
• student_name

mathematics_marks

• student_id (主密钥)
• (5,2)商业、金融、商业、金融等行业的商品、商业、金融、商业、商业、金融、金融等行业的自愿性、中转率、中转率、转轨性、无偿性、目地性、成品性、无偿性、无偿性、无偿

science_marks

• student_id (主密钥)
• (5,2)商业、金融、商业、金融等行业的商品、商业、金融、商业、商业、金融、金融等行业的自愿性、中转率、中转率、转轨性、无偿性、目地性、成品性、无偿性、无偿性、无偿

学生被称为对称对的一部分，如果该学生在科学中获得的分数等于其他学生在数学中获得的分数，而在数学中获得的分数与另一名科学学生获得的分数相同。

我试图用以下查询来解决上面的问题：

SELECT s.student_name 
FROM student s
LEFT JOIN (mathematics_marks m CROSS JOIN science_marks sc) 
    ON (s.student_id = m.student_id AND m.student_id = sc.student_id)
    WHERE EXISTS(SELECT * FROM mathematics_marks m 
                    WHERE sc.score=m.score 
                    AND m.score=sc.score)
    ORDER BY student_name;

我没有得到正确的输出。有人能帮我找出我哪里出错了吗？

Answer 1

以一种非常简单的方式

select s.student_name 'student_name'
from student s
inner join mathematics_marks m
on m.student_id = s.student_id
inner join science_marks sc 
on sc.student_id = s.student_id
where m.score in ( select score from science_marks ) and 
      sc.score in ( select score from mathematics_marks )
ORDER BY student_name;

Answer 2

我会这样做：

SELECT s.student_name
FROM student s
LEFT JOIN mathematics_marks m ON m.student_id = s.student_id
LEFT JOIN science_marks sc ON sc.student_id = s.student_id
WHERE m.score IN (SELECT score FROM science_marks) OR sc.score IN (SELECT score FROM mathematics_marks)

Answer 3

交叉连接所有学生，然后将每个学生的分数与其他学生进行比较。

select student1.student_name, student2.student_name
from (
         select s1.student_id, s1.student_name, m1.score "m_score", sc1.score "sc_score"
         from student s1
                  join mathematics_marks m1 on s1.student_id = m1.student_id
                  join science_marks sc1 on s1.student_id = sc1.student_id) student1,
     (
         select s2.student_id, s2.student_name, m2.score "m_score", sc2.score "sc_score"
         from student s2
                  join mathematics_marks m2 on s2.student_id = m2.student_id
                  join science_marks sc2 on s2.student_id = sc2.student_id) student2

where student1.student_id<>student2.student_id
  and student1.m_score = student2.sc_score
  and student1.sc_score = student2.m_score;