问题描述
mysql 在给定范围内生成一系列日期的最佳方法是什么?
What is the best way in mysql to generate a series of dates in a given range?
我想到的应用是写一个报表查询,每个日期返回一行,不管是否有数据要报表.最简单的形式:
The application I have in mind is to write a report query that returns a row for every date, regardless of whether there is any data to report. In its simplest form:
select dates.date, sum(sales.amount)
from <series of dates between X and Y> dates
left join sales on date(sales.created) = dates.date
group by 1
我曾尝试创建一个包含很多日期的表格,但这似乎是一个糟糕的解决方法.
I have tried creating a table with lots of dates, but that seems like a poor workaround.
推荐答案
我认为有一个日历表是个好主意;您可以获得大量报告和查询功能,尤其是在填充稀疏数据范围时.
I think having a calendar table is a good idea; you can gain a lot of reporting and query functionality, especially when filling sparse data ranges.
我发现这篇文章似乎是一个很好的例子.
I found this article with what seems to be a good example.
这篇关于生成一系列日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!