假设您可以轻松地从日期时间中提取月份，因此真实的的问题是关于聚合逻辑，并且您可以创建一个数字表。
下面是一个简单的例子来展示这种模式。
小提琴

CREATE TABLE Num (num int);
INSERT INTO Num VALUES (0),(1),(2),(3),(4);

CREATE TABLE Tbl (start int, run int);
INSERT INTO Tbl VALUES (1,2),(2,3);

SELECT start + num active_month
      ,count(*) * 10 income
  FROM Tbl
       INNER JOIN
       Num ON num < run
GROUP BY start + num

和Karl一样，我非常确信某种数字表在这里是必要的。我个人喜欢the approach given here，它定义了一个视图（好吧，有几个）来生成数字，而不是必须实际存储一个充满数字的表。无论你使用表还是视图，当你从它SELECT时，它看起来就像这样：

n
---
 0
 1
 2
 3
 …

使用它，您可以构建如下查询：

SELECT
  purchases.purchase_id,
  purchases.date_purchased,
  purchases.duration,
  -- generator_16 is our numbers table
  generator_16.n,
  -- Below we calculate the year and month (year_mon) in the following way:
  -- (1) Get the first day of the year, e.g. if date_purchased is 2012-12-28,
  --     this gives us 2012-01-01.
  -- (2) Get the month number, e.g. 12 for 2012-12-28) and add that many months
  --     to the first day of the year, which gives us the first day of the
  --     month, 2012-12-01.
  -- (3) Add "n" months, where "n" is the number we get from the numbers table,
  --     starting at 0.
  DATE_ADD(                                              -- (3)
    DATE_ADD(                                            -- (2)
      MAKEDATE( YEAR(purchases.date_purchased), 1 ),     -- (1)
      INTERVAL MONTH(purchases.date_purchased) - 1 MONTH -- (2)
    ),
    INTERVAL generator_16.n MONTH                        -- (3)
  ) AS year_mon,
  purchases.amount_income / purchases.duration AS amount
FROM purchases
-- The below JOIN means that if `purchases.duration` is 3, we get three rows
-- that have 0, 1, and 2 in the `n` column, which we use as the number of dates
-- to add in (3) above.
JOIN generator_16
ON generator_16.n BETWEEN 0 AND purchases.duration - 1
ORDER BY purchases.purchase_id, year_mon;

这会给我们一个如下的结果（SQL Fiddle）：

purchase_id  date_purchased  duration  n      year_mon  amount
-----------  --------------  --------  -  ------------  ------
          1    2013-12-28 …         2  0  2013-12-01 …     7.5
          1    2013-12-28 …         2  1  2014-01-01 …     7.5

          2    2014-01-04 …         1  0  2014-01-01 …      10

          3    2014-02-04 …         6  0  2014-02-01 …  6.6667
          3    2014-02-04 …         6  1  2014-03-01 …  6.6667
          3    2014-02-04 …         6  2  2014-04-01 …  6.6667
          3    2014-02-04 …         6  3  2014-05-01 …  6.6667
          3    2014-02-04 …         6  4  2014-06-01 …  6.6667
          3    2014-02-04 …         6  5  2014-07-01 …  6.6667

我插入了空行来分隔purchase_id组，这样您就可以看到n是如何随组中的每个项目从0增加到duration - 1的。正如您所看到的，year_mon等于date_purchased月的第一天之后的n个月加上n个月，amount等于amount_income / duration。
我们差不多完成了，但是可以看到year_mon有重复：2014-01-01显示了两次，这是一个好消息，因为我们可以使用GROUP BY按该列分组，使用SUM(amount)得到该月的总数：

SELECT
  DATE_ADD(
    DATE_ADD(
      MAKEDATE( YEAR(purchases.date_purchased), 1 ),
      INTERVAL MONTH(purchases.date_purchased) - 1 MONTH
    ),
    INTERVAL generator_16.n MONTH
  ) AS year_mon,
  SUM(purchases.amount_income / purchases.duration) AS total
FROM purchases
JOIN generator_16
ON generator_16.n BETWEEN 0 AND purchases.duration - 1
GROUP BY year_mon
ORDER BY year_mon;

此查询与上个月的唯一区别是，我们先执行GROUP BY year_mon，然后执行SUM(amount_income / duration)，以获得该月的total，生成以下结果（SQL Fiddle）：

year_mon   total
------------  ------
2013-12-01 …     7.5
2014-01-01 …    17.5
2014-02-01 …  6.6667
2014-03-01 …  6.6667
2014-04-01 …  6.6667
2014-05-01 …  6.6667
2014-06-01 …  6.6667
2014-07-01 …  6.6667

...当然，您可以使用DATE_FORMAT和CAST或ROUND来获得格式良好的日期和金额，或者您也可以在前端代码中这样做。

关于：

SELECT a.my_date, a.income, IFNULL(SUM(DISTINCT(a.income)) + sum( b.income ), a.income) as roll_up
FROM (
SELECT purchase_id, DATE_FORMAT( date_purchased, '%y-%m') AS my_date, SUM( amount_income / duration ) AS "income"
FROM incomes
GROUP BY my_date
) AS a
LEFT OUTER JOIN (
SELECT purchase_id, DATE_FORMAT( date_purchased, '%y-%m') AS my_date, SUM(amount_income / duration ) AS "income"
FROM incomes
GROUP BY my_date
) AS b ON ( a.purchase_id > b.purchase_id )

GROUP BY a.采购标识
在一个镜头中完成这一点有点棘手--可能会有所改进--但这会产生以下结果：

my_date  income     roll_up
13-12    8.5000     8.5000
14-01   10.0000     18.5000
14-02   16.6667     35.1667

我的数据集是：