mysql - 根据其他表选择数据

Question

我仍然是数据库和 mysql 方面的新手，我想学习如何使用 JOIN。很抱歉，我只是不知道如何用语言表达这种情况。希望大家看了这些数据都能明白。这是带有记录的表格:

Table student 
student_id | student_name 
-------------------------
1            Ana
2            Billy
3            Connor

Table comp 
comp_id | subj_id | comp_name  
--------------------------
1         24         Run       
2         24         Swim       
3         24         Jump
4         25         Eat    

Table comp_mark 
semester | subj_id | student_id | comp_id | mark
-------------------------------------------------
1          24        1            1         7
1          24        1            2         4
1          24        1            3         6
1          24        2            1         4
1          24        2            2         8
1          24        3            1         9

我期望从选择 comp_mark 表(更新)的查询中得到类似这样的结果:

student_name | semester | subject_id | comp_id  | mark
-------------------------------------------------------
Connor        1          24           1          9
Connor        null       24           2          null
Connor        null       24           3          null        

有没有人可以帮我解决这个问题？非常感谢

更新

我试过这个(Guillaume Poussel 的查询)并按 student_name 订购:

 SELECT s.student_name, cm.semester, c.subj_id AS subject_id, c.comp_id, cm.mark
 FROM student s
 CROSS JOIN comp c
 LEFT JOIN comp_mark cm ON s.student_id = cm.student_id
 AND cm.subj_id = c.subj_id
 AND cm.comp_id = c.comp_id
 ORDER BY s.student_name

结果:

Answer 1

试试这个:

SELECT s.student_name, cm.semester, c.subj_id AS subject_id, c.comp_id, cm.mark
FROM student s
CROSS JOIN comp c
LEFT JOIN comp_mark cm ON s.student_id = cm.student_id AND cm.subj_id = c.subj_id AND cm.comp_id = c.comp_id