我保存了几个条目,如下所示:
ID | StartDate | Enddate | Amount
我现在确实想查询这段时间内的每一天,以在这些日期之间分配总金额。
例如
1 | 2012-01-01 | 2012-01-05 | 10
2 | 2012-01-04 | 2012-01-05 | 20
应该成为
2012-01-01 | 2
2012-01-02 | 2
2012-01-03 | 2
2012-01-04 | 12
2012-01-05 | 12
这可能吗?我无法想象如何从周期到单日值得出结论。提前感谢!
当您需要包含所有日期的查询结果时,必须具有日期表(几乎没有帮助)。我给你答案,不使用日期表。
SQLFIDDLEExample 1 with Date Table
查询:
SELECT
DATE_FORMAT(td.TimeKey, '%Y-%m-%d') as Date,
SUM(Amount/(SELECT COUNT(tdc.TimeKey)
FROM Table1 t1c, TDate tdc
WHERE t1c.StartDate<= tdc.TimeKey
AND t1c.Enddate >= tdc.TimeKey
AND t1c.ID = t1.ID )) as Total_Amount
FROM Table1 t1, TDate td
WHERE t1.StartDate<= td.TimeKey
AND t1.Enddate >= td.TimeKey
GROUP BY Date
ORDER BY Date
结果:
| DATE | TOTAL_AMOUNT |
-----------------------------
| 2012-01-01 | 2 |
| 2012-01-02 | 2 |
| 2012-01-03 | 2 |
| 2012-01-04 | 12 |
| 2012-01-05 | 12 |
在不使用日期表的情况下,结果相同:
SQLFIddle 示例仅使用 SQL(不带日期表)
您可以考虑使用整数表来创建日期范围。 请参阅我的答案 这里 了解我在说什么。
在您的情况下,您将确定每个日期范围内的天数,并将其用于最大整数。 该最大整数既是将行数限制为适当的天数,也是计算日期范围内每天的平均值。