mysql - 如何对两个具有多对多关系的 MySQL 表中的列求和

Question

我需要检索每个学生的值，如下所示

student full name | costs | discounts | net Value | payments | remaining

当我尝试时

SELECT 
    concat_ws(' ',students.firstname,students.secondname,students.thirdname,students.forthname,students.lastname),
        students.birthdate,
        sum(costs.value),
        sum(discounts.value),
        sum(costs.value)-sum(discounts.value),
        sum(payments.amount),
        sum(costs.value)-sum(discounts.value)-sum(payments.amount)     

FROM students
JOIN coststudents 
       ON  coststudents.student_id = students.student_id 
 JOIN costs  
    ON  costs.cost_id = coststudents.cost_id
JOIN discountstudent 
       ON  discountstudent.student_id = students.student_id 
JOIN discounts  
    ON  discounts.id = discountstudent.id
JOIN payments 
       ON  payments.student_id = students.student_id 

它只给我一行，与查询不对应。请帮忙。

Answer 1

将 GROUP BY Students.student_id 放在代码末尾。

编辑:使用 LEFT JOIN 代替 JOIN。