从日期范围生成日期 [英] generate days from date range

问题描述

我想运行一个查询，如

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

并返回数据，如：

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

推荐答案

此解决方案使用 no循环，过程或临时表。子查询生成最后一千天的日期，并且可以根据需要扩展到远处或远程。

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last thousand days, and could be extended to go as far back or forward as you wish.

select a.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
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

性能注意事项

一个href =http://www.phpmyadmin.net/home_page/try.php =noreferrer>这里，性能出人意料地很好：上述查询需要0.0009秒。 strong>

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

如果我们扩展子查询以生成约。 100,000个数字（因此约为274年的日期），运行在0.0458秒。

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

顺便提一句，这是一种非常便携的技术，可以与大多数数据库进行微调。

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle示例返回1,000天

这篇关于从日期范围生成日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！