MySQL显示所有日期在范围之间 [英] MySQL display all date in between range

问题描述

我希望显示MySQL之间的从和之间的所有日期到日期。

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

例如来自 schedule 的数据来自和到字段是：

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

从日期是 2013-3-13 和

to date是 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)?

编辑：

这里的答案是非常有帮助的，虽然我仍然没有完全了解所需的内容。在此示例中，它仅运行成功但不输出任何内容。而且我不知道这是什么问题。

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

这篇关于MySQL显示所有日期在范围之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！