sql - 对两个不同表中的单独列求和

Question

我有 table

Allocations:
|allocationid|     item     |     job     |    amount   |
|1           |     item1    |     jobname |      50     |
|2           |     item1    |     jobname |      30     |
|3           |     item2    |     jobname |      20     |

和 table

InventoryAdjustLog:
|   logid    |      item    |     customer |   usage    |
|   1        |     item1    |     jobname  |    70      |
|   2        |     item1    |     jobname  |     5      |
|   3        |     item2    |     jobname  |     15     |

前表是对工作所需 Material 的估计，后表是实际使用情况。

我想对此数据运行一个查询，该查询将返回 Allocations 表中所有类似项目的总和，并对 InventoryAdjustLog 表中的所有类似项目进行另一个总和。

使用此数据返回的一个示例是

 |   Item    |     Job       |     Allocated     |     Usage       |
 |   item1   |     jobname   |     80            |    75           |
 |   item2   |     jobname   |     20            |    15           |

我写了以下查询

Select Allocations.item as Item ,Allocations.job as Job 
,sum(Allocations.ammount)as  Allocated,
sum(InventoryAdjustLog.usage) as Usage
from Allocations 
inner join InventoryAdjustLog on Allocations.job = InventoryAdjustLog.customer  
where Allocations.job= 'jobname' group by Allocations.item;

它仅部分有效，除了它将已经求和的行的每个实例求和。如下所示。由于有两行连接，并且这两行都包含 80 的总和值，因此它将两行相加为一。

 |   Item    |     Job       |     Allocated     |     Usage       |
 |   item1   |     jobname   |240 (Should be 80) |    etc...       |
 |   item2   |     jobname   | 60 (Should be 20) |    etc...          |

对我的查询进行哪些修改或更改将返回所需的结果，如倒数第二张表所示？

我绝不是 sql 专家，因此非常感谢任何有关此事的指导。

Here is a SqlFiddle example

Answer 1

您有问题，因为您在两个表中都有重复项。一种解决方案是使用 union all 组合数据。在进行聚合之前:

Select aia.item, aia.job,
       sum(aia.allocation)as  allocation,
       sum(aia.usage) as Usage 
from (select item, amount as allocation, 0 as usage, job
      from Allocations 
      union all
      select item, 0, usage, customer
      from InventoryAdjustLog 
     ) aia  
where aia.job = 'jobname'
group by aia.item, aia.job;

Here是一个 SQL fiddle 。