如何在 SQL 中高效生成日期范围？

SQL 日期范围生成技巧

在SQL中生成指定范围内的日期列表有多种方法。本文以生成'2010-01-20'到'2010-01-24'的日期序列为例，介绍一种高效的解决方案。

该方法利用子查询生成一个完整的日期集合。子查询通过四个嵌套循环的组合，可以高效生成多达10000天的日期序列，并可轻松扩展至更长的日期范围。

<code class="language-sql">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</code>

然后，我们对生成的日期列表进行过滤，提取目标范围内的日期：

<code class="language-sql">select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (...) as a
) a
where a.Date between '2010-01-20' and '2010-01-24'</code>

这种方法的优势在于：无需循环、存储过程或临时表，高效且可移植到多个数据库平台。生成10000天日期所需时间少于0.01秒，即使生成约100000天的日期，性能依然出色。

以上是如何在 SQL 中高效生成日期范围？的详细内容。更多信息请关注PHP中文网其他相关文章！