MySQL:如何从表中选择两个字段，以及两个相关表中的总金额列？

Question

我有三个表:

项目购买A购买B

项目架构:

id | user_id | title | description | create_date

采购架构A:

id | project_id | amount | create_date

采购方案B:

id | project_id | amount | create_date

Project 有一堆独特的行，详细说明了不同的项目。这些项目中的每一个在 PurchaseA 和 PurchaseB 中都有很多行。我想要做的是从 Project 中选择所有包含字段 id 和 title 的行，以及两者的总和PurchaseA中的相关记录和PurchaseB中的相关记录总数。

我可以让它彼此独立地工作，但是当我有两个连接时，数字最终会被大量膨胀。

期望的结果

21732 Test Project A          84274.48
21747 Test Project B          18237.47
34714 Test Project C         183747.18

其中带有 FLOATS 的列是 PurchaseA 和 PurchaseB 中相关行的总和。

编辑

这是我当前的 SQL:

select 
    projects.id,
    projects.title,
    COALESCE(SUM(PurchaseA.amount), 0) as pledgetotal,
    COALESCE(SUM(PurchaseB.amount), 0) as purchasetotal
from
    projects
        left join
    PurchaseA ON (PurchaseA.project_id = projects.id)
        left join
    PurchaseB ON (PurchaseB.project_id = projects.id)
        left join

WHERE
    projects.title IS NOT NULL

    projects.end_date < NOW()
group by projects.id

测试数据

Projects:

id          title           description         create_date

623         Test Project A  This is a test A    2013-01-01
624         Test Project B  This is a test B    2013-01-02


PurchaseA

id          project_id      amount              create_date
1           623             100                 2013-01-02
2           623             125                 2013-01-02
3           624             200                 2013-01-03
4           623             150                 2013-01-03
5           624             50                  2013-01-04

PurchaseB

id          project_id      amount              create_date
1           623             110                 2013-01-02
2           624             105                 2013-01-02
3           623             215                 2013-01-03
4           623             100                 2013-01-03
5           624             150                 2013-01-04

如您所见，每个项目在每个相关表中都有多行。我想从每个表中获取总和，然后将它们相加。

Answer 1

我相信您的部分问题来自 projects 和 purchase. 表之间存在的一对多关系。我的建议是将 sum() 移动到子查询:

select 
    p.id,
    p.title,
    a.pledgetotal,
    b.purchasetotal
from projects p
left join
(
  select project_id, sum(amount) pledgetotal
  from PurchaseA
  group by project_id
) a
  ON (a.project_id = p.id)
left join
( 
  select project_id, sum(amount) purchasetotal
  from PurchaseB
  group by project_id
) b
  ON (B.project_id = p.id)
WHERE p.title IS NOT NULL;

参见 SQL Fiddle with Demo