MySQL显示范围内的所有日期

2022-01-24 00:00:00 date range mysql

我想显示 MySQL 中 from 和 to 日期之间的所有日期.

I want to display all dates between a from and to dates from MySQL.

例如 schedule 表中具有 from 和 to 字段的数据是:

For example the data from schedule table that has from and to fields are:

from 日期为 2013-3-13，且

到日期是2013-3-20，

我想要的结果是:

 2013-3-13
 2013-3-14
 2013-3-15
 2013-3-16
 2013-3-17
 2013-3-18
 2013-3-19
 2013-3-20

如何仅使用 MySQL 查询来实现这一点(不必使用存储过程，因为我不熟悉它)?

How can I achieve this using MySQL query only (without having to use stored procedure 'cause I'm not familiar with it)?

这里的答案很有帮助，虽然我还没有完全明白想要什么.在this sample中，它只运行成功，没有输出任何东西.而且我不知道问题出在哪里.

The answer here is very helpful, though I still don't fully get what is desired. In this sample, it only runs successfully but doesn't output anything. And I don't know what seems to be the problem.

请帮忙.谢谢！

推荐答案

您可以使用以下方法来生成日期列表:

You can use the following to generate your list of dates:

select a.Date,  s.*
from 
(
  select curdate() + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
  from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
inner join schedule s
  on a.Date >= s.fromDate 
  and a.Date <= s.toDate

请参阅 SQL Fiddle with Demo

相关文章