mysql - 在日期范围内运行总计 - 填写缺失的日期

Question

我有下表。

DATE  | AMT
10/10 | 300
12/10 | 300
01/11 | 200
03/11 | 100

如何获得每月总计？结果像 -

DATE | TOT
1010 | 300
1110 | 300
1210 | 600 
0111 | 800
0211 | 800
0311 | 900

像这样的sql语句

SELECT SUM(AMT) FROM TABLE1 WHERE DATE BETWEEN '1010' AND '0111'

将导致 0111 为 800，但是......

注意 没有日期限制。这是我的困境。如何在不对所有日期执行循环的情况下填充此列并同时显示缺少的月份？

Answer 1

为了弥补缺失的月份，创建一个模板表来加入。

将其视为缓存。无需遍历并填补空白，只需将日历缓存在您的数据库中即可。

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

你最终会得到类似...

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 想要一个总计。

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

因此，通常强烈建议您按上述方法计算每月总计，然后使用您的应用程序进行迭代并生成运行总计值。

如果您真的必须在 SQL 中执行它，它会像...

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