mysql - 基于三个关系表连接一个报表

Question

下面是给定的表格:

`student`                  `subject`
-------------------        ------------------- 
id    name                 id    subject
-------------------        -------------------
1     Alice                1     Maths
2     Bob                  2     Science
3     Eve                  3     Economics
-------------------        -------------------

`marks`
-----------------------------------------------------
id     student_id      subject_id       marks
-----------------------------------------------------
1      1               1                30
2      1               2                40
3      2               3                50
4      3               1                60
5      3               2                70
-----------------------------------------------------

我需要一个如下所示的输出:

`o/p`
----------------------------------------------
name          subject          marks
----------------------------------------------
Alice         Maths            30
Alice         Science          10
Alice         Economics        NULL
Bob           Maths            NULL
Bob           Science          NULL
Bob           Economics        50
Eve           Maths            60
Eve           Science          70
Eve           Economics        NULL
----------------------------------------------

请注意，我的目标是 MySQL 5.6.x 语法，并且我创建了上面的 SQL fiddle here以方便访问问题。

Answer 1

给你

select 
st.name,
su.subject,
m.marks
from student as st
cross join subject as su
left join marks as m on m.student_id=st.id and m.subject_id=su.id
order by st.name, su.subject