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

问题描述

我想运行一个查询

select ... 作为`date` 介于'2010-01-20' 和'2010-01-24' 之间的天数

并返回如下数据:

<前>天----------2010-01-202010-01-212010-01-222010-01-232010-01-24

解决方案

此解决方案使用无循环、过程或临时表.子查询生成过去 10,000 天的日期，并且可以扩展到任意远的后退或前进.

选择一个.Date从 (选择 curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY 作为日期from(选择0作为联合全选1联合全选2联合全选3联合全选4联合全选5联合全选6联合全选7联合全选8联合全选9)作为交叉联接(选择 0 作为联合全选 1 联合全选 2 联合全选 3 联合全选 4 联合全选 5 联合全选 6 联合全选 7 联合全选 8 联合全选 9) as b交叉联接(选择 0 作为联合全选 1 联合全选 2 联合全选 3 联合全选 4 联合全选 5 联合全选 6 联合全选 7 联合全选 8 联合全选 9) as c交叉联接(选择 0 作为联合全选 1 联合全选 2 联合全选 3 联合全选 4 联合全选 5 联合全选 6 联合全选 7 联合全选 8 联合全选 9) as d) 一种其中 a.2010-01-20"和2010-01-24"之间的日期

输出:

日期----------2010-01-242010-01-232010-01-222010-01-212010-01-20

性能说明

测试一下这里，性能出奇的好:以上查询需要 0.0009 秒.

如果我们扩展子查询以生成大约.100,000 个数字(因此大约 274 年的日期)，它在 0.0458 秒内运行.

顺便说一下，这是一种非常便携的技术，只需稍作调整即可适用于大多数数据库.

返回 1,000 天的 SQL Fiddle 示例

I would like to run a query like

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

And return data like:

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

解决方案

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 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) + (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' 

Output:

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

Notes on Performance

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

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 example returning 1,000 days

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