具有两个相关表的表的总和 [英] Sum total of table with two related tables

问题描述

我在这里(我确定是)这个简单的问题，我不知道该怎么解决.

I'm here with this (I'm sure it is) simple question I can't figure out how to solve.

我有这个模式:

使用此数据:

我的预期结果是:

对于"JOHN NASH":

For "JOHN NASH":

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

对于"OSCAR WILDE":

For "OSCAR WILDE":

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

提前谢谢.

推荐答案

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

这篇关于具有两个相关表的表的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！