如何在 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中文網其他相關文章！