mysql - 表与两个相关表的总和

Question

我在这里提出这个(我确信是)简单的问题，我不知道如何解决。

我有这个架构:

有了这些数据:

我的预期结果是:

对于“约翰·纳什”:

PERSON_NAME | TOTAL_FRUIT | TOTAL COOKIE
----------------------------------------
JOHN NASH   |       10    |     38  

对于“奥斯卡·王尔德”:

PERSON_NAME | TOTAL_FRUIT | TOTAL COOKIE
----------------------------------------
OSCAR WILDE |       28    |      0

提前致谢。

Answer 1

SELECT name, IFNULL(f.total, 0) AS total_fruit, IFNULL(c.total, 0) AS total_cookie
FROM person AS p
LEFT JOIN (SELECT person_idperson, SUM(cost) AS total
           FROM fruit
           GROUP BY person_idperson) AS f
ON p.idperson = f.person_idperson
LEFT JOIN (SELECT person_idperson, SUM(cost) AS total
           FROM cookie
           GROUP BY person_idperson) AS c
ON p.idperson = c.person_idperson