生成一系列日期 [英] Generating a series of dates

问题描述

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.

这篇关于生成一系列日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！