php - 从MySQL中的日期范围计算付款

Question

我想计算选定日期内的付款，但我不知道该怎么做。

这是我的表中的示例数据

id    starts_from     payment_per_day
=======================================
1     2012-01-01      10,000.00
2     2012-01-15      10,500.00
3     2012-02-01      11,000.00
4     2012-02-15      11,500.00
5     2012-03-01      12,000.00

我如何计算从 2012-01-21 到 2012-02-20 的总付款？

支付总额应该是338,500

from 2012-01-21 to 2012-01-31 = 11 days * 10,500
from 2012-02-01 to 2012-02-14 = 14 days * 11,000
from 2012-02-15 to 2012-02-20 =  6 days * 11,500

但如果我这样做:

SELECT SUM(payment_per_day) as total FROM table 
WHERE starts_from BETWEEN '2012-01-21' AND '2012-02-20'

结果只有22,500

有什么想法吗？

Answer 1

SELECT SUM(payment_per_day) as total FROM table 
WHERE starts_from BETWEEN '2012-01-21' AND '2012-02-20';