mysql - 多个连接

Question

给定这些表的定义和关系:

jobs  
tasks  
invoice_item  
invoice_payment  

工作是一切的根本。
每个工作都有多个任务，例如，工作可能是制作一把椅子，而任务可能是所需的步骤。每个任务都有一定的计划价格，因此购买椅子的木头可能需要 400。

每个任务都可以通过发票计费，发票存储在两个表中，一个表包含发票 ID(发票项目)，另一个表包含发票付款详细信息，例如开票金额和开票任务。

每个任务可以有多个发票，例如我们的成本为 400 的任务可以有 4 个发票，每个 100。

要求:

使用以下信息在其自己的行中显示每个作业:
1. 工程计划总价
2. 已为该工作支付的金额
3.职位描述

我创建了这个 sqlfiddle 来提供基本的数据结构。 http://sqlfiddle.com/#!2/0be74该数据集的预期结果是:

(job_description, SUM(planned), SUM(billed))  
VALUES  
('Project A', 2000, 1900),    
('Project B', 3000, 2000) 

我目前唯一且唯一的解决方案非常失败:

Select j.job_description, SUM(t.task_planned_price),SUM(p.invoice_amount)
FROM jobs as j
LEFT JOIN tasks as t ON t.job_id = j.job_id
JOIN invoice_item as i ON i.task_id = t.task_id
JOIN invoice_payment as p ON p.invoice_id = i.invoice_id
GROUP BY j.job_id

对于我做错了什么的任何指导和一些提示(或答案)，我将不胜感激。您可以将我的解决方案直接复制到 sqlfiddle，看看有什么问题。

提前致谢，抱歉问了这么长的问题!

Answer 1

当您将任务加入发票项目和发票付款时，发票金额的总和不正确，因为任务行数乘以发票付款行数 * 发票金额行，您需要将它们单独分组。

select j.job_description, t1.sum_task_planned_price, 
       coalesce(t2.sum_invoice_amount,0) sum_invoice_amount
from jobs j join (
    select job_id, sum(task_planned_price) sum_task_planned_price
    from tasks
    group by job_id
) t1 on t1.job_id = j.job_id
left join (
    select t.job_id, sum(p.invoice_amount) sum_invoice_amount
    from tasks t
    join invoice_item as i on i.task_id = t.task_id
    join invoice_payment as p on p.invoice_id = i.invoice_id
    group by t.job_id
) t2 on t1.job_id = t2.job_id

http://sqlfiddle.com/#!2/0be74/8