mysql - 来自不同表的求和和分组

Question

我有三张 table 。

Table (Store)
+-----+-----+
| ID  | Qty |
+-----+-----+
| 001 |   4 |
| 002 |   3 |
| 003 |   5 |
| 004 |   6 |
+-----+-----+

Table (Received)
+-----+-----+
| ID  | Qty |
+-----+-----+
| 001 |   2 |
| 005 |   9 |
| 001 |   5 |
+-----+-----+

Table (Issued)
+-----+-----+
| ID  | Qty |
+-----+-----+
| 001 |   3 |
| 003 |   2 |
| 001 |   1 |
| 005 |   2 |
+-----+-----+

我想对 (Store + Received) - Issued 中的数量求和，按 ID 分组我希望结果如下所示。请帮忙。

Table (Stock)
+-----+-----+
| ID  | Qty |
+-----+-----+
| 001 |   7 |
| 002 |   3 |
| 003 |   3 |
| 004 |   6 |
| 005 |   7 |
+-----+-----+

Answer 1

你可以这样做

select 
x.id , coalesce(sum(qty),0)-coalesce(i_qty,0) from
(
 select id,qty from store
 union all 
 select id,qty from received
)x
left join(
 select id,sum(qty) as i_qty 
 from issued group by id
)y on x.id = y.id
group by id