Mysql 两个表之间重叠日期的 Sum 列

Question

表 A 按天存储每种库存数量:

  +---+------------+----------+-------------+
  |id | Stock_id   | amount   |  Date       |
  +---+------------+----------+-------------+
  | 1 |          1 |      100 | 2017-09-05  |
  | 2 |          1 |      200 | 2017-09-06  |
  | 3 |          1 |      300 | 2017-09-07  |
  | 4 |          1 |      200 | 2017-09-08  |
  | 5 |          1 |      200 | 2017-09-09  |
  | 6 |          1 |      200 | 2017-09-10  |
  | 7 |          2 |      300 | 2017-09-06  |
  +---+------------+----------+-------------+

表B包含库存ID和类别之间的关系。添加日期表示库存添加到类别的时间，删除日期表示库存被删除的日期。如果删除日期为 null ，则表示该库存仍在该类别中。

  +---+----------+  ------- +------------+------------+
  |id | Stock_id |Category  | Add Date   | Remove Date|
  +---+----------+--------  +------------+------------+
  | 1 |        1 | Category1| 2017-09-03 | 2017-09-07 |
  | 2 |        1 | Category1|2017-09-09  | null       |
  +---+----------+--------  +------------+------------+

我的问题是给出一个时间范围，比如从2017-09-05到2017-09-08。首先对于Stock1和Category1，我想计算与表b的时间重叠，即<2017-09-05到2017-09-06>。然后将表A中2017-09-05至2017-09-06的金额相加。结果为 (100+200) = 300。如果时间范围为 2017-09-06 至 2017-09-10，则重叠部分为 <2017-09-06, 2017-09-09 至 2017-09-10>。总和结果为 (200+200+200)=600。

我该怎么做？谢谢大家!

Answer 1

如果我理解正确的话，您希望了解给定日期范围内每个类别的库存量。如果是这样，这就是正确连接表并聚合的问题:

select a.stock_id, b.category, sum(a.amount)
from a join
     b
     on a.stock_id = b.stock_id and a.date >= a.add_date and 
        (a.date <= b.remove_date or b.remove_date is null)
where a.date >= '2017-09-05' and b.date <= '2017-09-08'
group by a.stock_id, b.category;