mysql生成时间列表,MySQL从日期范围列表生成每个日期-CSDN博客

I have a query (select * from bla.. bla.. ) that produce result of date range like this :

code | date1 | date2

a | 2016-04-19 | 2016-04-21 |

b | 2016-04-13 | 2016-04-14 |

I want to generate each day of that date range between date1 and date2 like this :

code | date_result

a | 2016-04-19

a | 2016-04-20

a | 2016-04-21

b | 2016-04-13

b | 2016-04-14

I found the example of query that produce each date between two date range like this :

SELECT ADDDATE('2016-04-10', INTERVAL @i:=@i+1 DAY) AS DAY

FROM (

SELECT a.a

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

) a

JOIN (SELECT @i := -1) r1

WHERE

@i < DATEDIFF('2016-04-19', '2016-04-10')

but I cannot implement it with my query :(

解决方案

finally I found the answer to generate dates from date1 to date2 :

select

from (

select t.*, t.date1 + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as tanggal

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

join (

select date1,date2 from mytable

) t

where t.date1 + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY <= t.date2

) a

thanks for your appreciation guys