我有一个查询（select * from bla.. bla..），它产生如下所示的日期范围结果：
code | date1 | date2
a | 2016-04-19 | 2016-04-21 |
b | 2016-04-13 | 2016-04-14 |
我想生成 date1 和 date2 之间日期范围的每一天，如下所示：
代码|日期结果
a | 2016-04-19
a | 2016-04-20
a | 2016-04-21
b | 2016-04-13
b | 2016-04-14
我找到了生成两个日期范围之间的每个日期的查询示例，如下所示：
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')
但我无法用我的查询来实现它:(
您可以使用 from_days() 将日期转换为天数
然后与计数表进行内连接（序号从 1 开始）
数字 730485 是 '2000-01-01' 偏移量（ select from_days('2000-01-01') ）
select a.* , from_days(t.tallyid+730485) from
(
select 'a' code , '2016-04-19' date1, '2016-04-21' date2
union all
select 'b'code , '2016-04-13' date1, '2016-04-14' date2
) a
inner join Tally t on t.tallyid between (TO_DAYS(a.date1)-730485) and (TO_DAYS(a.date2)-730485)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
