mysql - (MySQL) 按周期对两个表进行收敛和求和

Question

我有两张 table 。

+----+------------+------------+
| id | pay_date   | payment    | 
+----+------------+------------+
|  1 | 2010-01-01 | 20000      |
|  1 | 2010-01-02 | 30000      |
|  1 | 2010-01-03 | 30000      |
|  1 | 2010-01-06 | 40000      |
|  2 | 2010-01-01 | 10000      |
|  2 | 2010-01-03 | 30000      |
|  2 | 2010-01-06 | 70000      |
+----+------------+------------+


+----+------------+------------+
| id | start_date | end_date   |
+----+------------+------------+
|  1 | 2010-01-01 | 2010-01-05 |
|  1 | 2010-01-06 | 2010-01-30 |
|  2 | 2010-01-01 | 2010-01-05 |
|  2 | 2010-01-06 | 2010-01-10 |
+----+------------+------------+

通过合并两个表格，我想制作下表。

+----+------------+------------+------------+
| id | start_date | end_date   | payment    |
+----+------------+------------+------------+
|  1 | 2010-01-01 | 2010-01-05 | 80000      |
|  1 | 2010-01-06 | 2010-01-30 | 40000      |
|  2 | 2010-01-01 | 2010-01-05 | 40000      |
|  2 | 2010-01-06 | 2010-01-10 | 70000      |
+----+------------+------------+------------+

此表为同一id在固定期限内的支付金额。

如何制作这个表格？

Answer 1

对于您提供给我们的确切数据，我们可以将第二个表连接到第一个表，条件是付款日期位于第一个表的开始日期和结束日期之间，并且 id值匹配。但这里立即出现了一个明显的边缘情况问题。如果发薪日期恰好与第二个表中的开始日期和结束日期重叠，会发生什么情况？那么就不清楚我们应该将该付款分配到哪个范围。如果我们只使用BETWEEN，我们最终会重复计算付款。

因此，在下面的查询中，我假设如果支付日期大于或等于开始日期但严格小于结束日期，则分配该支付日期。这可能不是您想要的逻辑，但如果您有重叠的数据，您可能会做出与此类似的假设。

SELECT
    t2.id, t2.start_date, t2.end_date, SUM(t1.payment) AS payment
FROM table2 t2
LEFT JOIN table1 t1
    ON t1.id = t2.id AND
       t1.pay_date >= t2.start_date AND
       t1.pay_date < t2.end_date
GROUP BY
    t2.id, t2.start_date, t2.end_date
ORDER BY
    t2.id, t2.start_date;

输出:

此处演示:

Rextester