mysql - 减去具有不同行数的不同表的两列

Question

如何编写单个查询，按 product_id 给出 SUM(Entrance.quantity) - SUM(Buying.quantity) 组。

问题出在第一个或第二个表中不存在的行中。可以这样做吗？

入口:

+---+--------------+---------+
| id | product_id | quantity|
+---+--------------+---------+
| 1 | 234 | 15 |
| 2 | 234 | 35 |
| 3 | 237 | 12 |
| 4 | 237 | 18 |
| 5 | 101 | 10 |
| 6 | 150 | 12 |
+---+--------------+---------+

购买:

+---+------------+-------------+
| id | product_id | quantity|
+---+------------+-------------+
| 1 | 234 | 10 |
| 2 | 234 | 20 |
| 3 | 237 | 10 |
| 4 | 237 | 10 |
| 5 | 120 | 15 |
+---+------------+------------+

期望的结果:

+--------------+-----------------------+
| product_id | quantity_balance |
+--------------+-----------------------+
| 234 | 20 |
| 237 | 10 |
| 101 | 10 |
| 150 | 12 |
| 120 | -15 |
+--------------+-----------------------+

Answer 1

这很棘手，因为产品可能在一张表中，但不在另一张表中。一种方法使用 union all 和 group by:

select product_id, sum(quantity)
from ((select e.product_id, quantity
       from entrance e
      ) union all
      (select b.product_id, - b.quantity
       from buying b
      )
     ) eb
group by product_id;