生成一系列日期
2021-11-20 00:00:00
mysql
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.
相关文章