为满足缺少的月份，请创建一个模板表以进行连接.

To cater for missing months, create a template table to join against.

将其视为缓存.无需遍历和填补空白，只需在数据库中缓存一个日历即可.

Think of it as caching. Rather than looping through and filling gaps, just have a calendar cached in your database.

您甚至可以将多个日历(月初，周初，银行假期，工作​​日等)全部组合到一个表中，并带有一堆搜索标记和索引.

You can even combine multiple calendars (start of month, start of week, bank holidays, working day, etc) all into one table, with a bunch of search flags and indexes.

您最终得到类似...

You end up with something like...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= calendar.date
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= '20110101'
  AND calendar.date <  '20120101'
GROUP BY
  calendar.date

编辑

我刚刚注意到OP希望获得总计.

I just noticed that the OP wants a running total.

这在SQL中是可能的，但是极其效率低下.原因是一个月的结果不用于计算下个月.相反，必须重新计算整个运行总额.

This -is- possible in SQL but it is extremely inefficient. The reason being that the result from one month isn't used to calculate the following month. Instead the whole running-total has to be calculated again.

因此，通常强烈建议您按上述方法计算每月总计，然后使用您的应用程序计算并得出连续的总计值.

For this reason It is normally strongly recommended that you calculate the monthly total as above, then use your application to itterate through and make the running total values.

如果您真的必须在SQL中执行此操作，那将类似于...

If you really must do it in SQL, it would be something like...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= @yourFirstDate
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= @yourFirstDate
  AND calendar.date <  @yourLastDate
GROUP BY
  calendar.date