小编典典

从日期范围生成天

mysql

我想运行一个查询

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

并返回如下数据:

天
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

阅读 110

收藏
2020-05-17

共1个答案

小编典典

此解决方案不使用 循环,过程或临时表 。子查询会生成最近10,000天的日期,并且可以扩展为任意向前或向后的日期。

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.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
    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 d
) a
where a.Date between '2010-01-20' and '2010-01-24'

输出:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

性能说明

这里对其进行测试,其性能令人惊讶地良好:
上面的查询花费了0.0009秒。

如果我们扩展子查询以生成近似值。100,000个数字(因此约有274年的日期),运行时间为0.0458秒。

顺便说一句,这是一种非常可移植的技术,该技术可以在大多数数据库中进行细微调整。

SQL Fiddle示例返回1,000天

2020-05-17