mysql - 使用 Google Cloud SQL 连接 3 个表，其中多个事件关联到每个表中的相同唯一 ID

Question

我在 Google Cloud SQL 中有三个表：

事件表：

id        Name     Date
A10117    foo      2017-01-01 0:00:00
A10617    bar      2017-01-06 0:00:00
A11017    barfoo   2017-01-10 0:00:00
A11317    foofoo   2017-01-13 0:00:00
A11417    barbar   2017-01-14 0:00:00
A12017    foobar   2017-01-20 0:00:00

收入表：

id        Income
A10117    1000
A10117    200
A10617    5000
A11017    3000
A11317    1500
A11317    560
A11417    2000
A12017    1500

费用表：

id        Expenses
A10117    80
A10617    25
A10617    75
A11017    99
A11317    123
A11317    45
A11417    34
A11417    95
A12017    678

我希望输出是一张大表，其中每个事件只有一行，收入和费用总和正确。

例如，在最终输出中，我应该有一行 ID A10617 ：

id      Name   Date                  Income     Expenses
A10617  bar    2017-01-06 0:00:00    5000       100
etc...

我已经构建了一些查询来尝试整合所有这些 usingWHERE子句，但我的最终输出中有一些重复项。

所以我的问题是：如何在一个查询中有效地合并这 3 个表？我的理解是我需要一个接一个地分别合并每个表。我不知道该怎么做。

我理想的查询是这样的：

SELECT id,Name,Date,sum(Income),sum(Expenses)
FROM Event e, Income i, Expenses ee
WHERE e.id = i.id = ee.id
GROUP BY id,Name,Date

标签： mysqlsqlgoogle-cloud-sql

Answer 1

select e.id, (select sum(Income) from income where id=e.id) as income, sum(ex.Income) as expenses from event as e inner join expenses as ex on e.id=ex.id group by e.id

This assumes all event ids are there in both income and expenses. If it is not the case change inner join to left join

Edit: Little faster query:

select e.id, (select sum(Income) from income where id=e.id) as income, (select sum(Income) from expenses where id=e.id) as expenses from event as e