我想显示特定记录的两个日期之间的所有日期
这是表格：
ID Start_Date End_Date
1 2013-01-14 2013-01-18
2 2013-02-01 2013-02-04
现在我想获取从日期到日期之间的所有日期。
预期产量
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
指导我为此编写查询，而无需创建任何额外的表。
我已经尝试过以下查询
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'
它的工作记录很好。但我想从表中获取所有日期间隔
更新
我整天坐着6张椅子。因此，一个用户在2013年1月14日至2013年1月18日预订了3个字符，而另一个人在2013年1月17日至2013年1月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
怎么样
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句