mysql - 将一张桌子加入多张桌子

i have 3 tables as the following:

学生

---------------
id     name
---------------
1      Joe
2      mark
3      mike
---------------

主题

-----------------------------------
id    student_id    name
-----------------------------------
1         1          math
2         1          english
3         1          french
4         1          history
5         2          math
6         2          english
7         2          french
8         3          math
9         3          english
-----------------------------------

考试

-----------------------------------
ID  Student_id   mark
-----------------------------------
1    1           10
2    1           10
3    1           10
4    1           10
5    2           5
6    2           5
7    2           5
8    3           2
9    3           2
-----------------------------------

SELECT
    student.id, student.name AS 'NAME',
    COUNT(subject.id) AS 'SUBJECTS',
    SUM(exam.mark) AS 'MARKS'
FROM
    'student'
INNER JOIN subject
ON   subject.student_id = student.id

LEFT JOIN exam
ON exam.student_id = student.id
GROUP BY
    student.id

我想把学生和科目，学生和考试联系起来
我的问题是当我加入第三张桌子的时候
复制结果。
我成功地连接了前两个表，但当我将>第三个表与它们连接时，它会重复计数。
我需要的结果是：

-----------------------------------
ID       NAME     SUBJECTS     MARKS
-----------------------------------
1         joe        4          40
2         mark       3          15
3         mike       2           4

最佳答案

这里的一个简单方法是对subject和exam表分别进行聚合，然后将它们连接起来：

SELECT
    s.id,
    s.name AS NAME,
    COALESCE(su.cnt, 0) AS SUBJECTs,
    COALESCE(e.marks, 0) AS MARKS
FROM student s
LEFT JOIN
(
    SELECT student_id, COUNT(*) AS cnt
    FROM subject
    GROUP BY student_id
) su
    ON s.id = su.student_id
LEFT JOIN
(
    SELECT student_id, SUM(mark) AS marks
    FROM exam
    GROUP BY student_id
) e
    ON s.id = e.student_id;

Demo
注意，我使用上面的左连接，因为可能给定的学生在subject或exam表中根本没有任何条目。在这种情况下，默认情况下，我们会将他的count/sum赋值为零。