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
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 (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
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
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