如何在表格中显示多个两个日期之间的所有日期？

I want to display all the dates between two dates for a particular record

我想显示特定记录的两个日期之间的所有日期

And this is the table :

这是表:

ID Start_Date End_Date

1 2013-01-14 2013-01-18
2 2013-02-01 2013-02-04

and now i want to get all the dates between from date and to date.

现在我想得到从日期到日期之间的所有日期。

Expected output

ID Date
1 2013-01-14
1 2013-01-15
1 2013-01-16
1 2013-01-17
1 2013-01-18
2 2013-02-01
2 2013-02-02
2 2013-02-03
2 2013-02-04

guide me to write query for that with out creating any extra tables.

指导我编写查询,而不创建任何额外的表。

I have already tried this following query

我已经尝试过以下查询

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

its working fine for single record. but i want get all the date intervals from my table

它的单一记录工作正常。但我想从我的表中获取所有日期间隔

Updated

i have 6 qty of chairs for all days. So one user book 3 chars on 2013-01-14 to 2013-01-18 another person book 2 chars on 2013-01-17 to 2013-01-20. So my expected output shown below.

我整天都有6把椅子。因此,2013-01-14至2013-01-18的另一个用户预订3个字符在2013-01-17至2013-01-20预订2个字符。所以我的预期输出如下所示。

ID Date         Available
1 2013-01-14     3
1 2013-01-15     3
1 2013-01-16     3
1 2013-01-17     5 
1 2013-01-18     5
1 2013-01-19     2
1 2013-01-20     2 
1 2013-01-21     2

2 个解决方案

#1

How about

  select
  t.id,
  DATE_FORMAT(a.Date,'%Y-%m-%d') as Date
  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
  join test t on a.Date between t.Start_Date and t.End_Date
  order by t.id,Date

DEMO

#2

First, I don't know your specific use case, but this feels ripe for more optimization. However, how about this?

首先,我不知道您的具体用例,但这对于更多优化来说已经成熟。不过,这个怎么样?

 select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where (selected_date between '2012-02-10' and '2012-02-15') OR
      (selected_date between '2012-03-10' and '2012-03-15') OR
      (selected_date between '2012-04-10' and '2012-04-15')

#1