SQL SUM Group by - 基于另一个表中的 'group'

Question

我希望我解释正确。

我有 2 个表，有

第一个表(table1)

+------------+------+-------+-------+
|    Date    | Item | Block | Total |
+------------+------+-------+-------+
| 2017-01    | a1   | B1    |  10.0 |
| 2017-01    | a2   | B1    |  20.0 |
| 2017-01    | a3   | B2    |  30.0 |
| 2017-02    | a1   | B1    |  40.0 |
| 2017-02    | a2   | B1    |  50.0 |
| 2017-02    | a3   | B2    |  60.0 |
+------------+------+-------+-------+

第二个表(table2)

+------------+------+
| Item Group | Item |
+------------+------+
| IG1        | a1   |
| IG1        | a2   |
| IG2        | a2   |
| IG2        | a3   |
+------------+------+

*Note that, one item group has multiple items. 
The items may appear several time in different item groups.

现在，我需要根据项目组 (table2)、Date 和 Block 对总数 (table1) 求和，最后，最终表:

+---------+------------+-------+-------+
|  Date   | Item Group | Block | Total |
+---------+------------+-------+-------+
| 2017-01 | IG1        | B1    |  30.0 |
| 2017-01 | IG2        | B1    |  20.0 |
| 2017-01 | IG1        | B2    |   0.0 |
| 2017-01 | IG2        | B2    |  30.0 |
+---------+------------+-------+-------+

如何使用 SQL 查询实现这一点？

编辑:

好的。看起来这是一件容易的事。我真丢人。我不知道 join 和 Group By 可以那样应用。 SQL 真的很棒。这样可以节省大量编码。

Answer 1

join 和简单的 group by 在这种情况下应该适合您:

select t1.Date, t2.ItemGroup, t1.Block, sum(t1.Total) Total
from table1 t1 join table2 t2 on t1.Item = t2.Item
group by t1.Date, t2.ItemGroup, t1.Block