从开始日期到结束日期的每个日期的行 [英] Row for each date from start date to end date

问题描述

我想做的是记录一张看起来像这样的记录:

What I'm trying to do is take a record that looks like this:

 Start_DT    End_DT     ID
4/5/2013    4/9/2013    1

并将其更改为以下形式:

and change it to look like this:

    DT      ID
4/5/2013    1
4/6/2013    1
4/7/2013    1
4/8/2013    1
4/9/2013    1

它可以用Python完成，但是我不确定SQL Oracle是否可以?我很难完成这项工作.任何帮助将不胜感激.

it can be done in Python but I am not sure if it is possible with SQL Oracle? I am having difficult time making this work. Any help would be appreciated.

谢谢

推荐答案

按级别连接对于这些问题很有用.假设第一个CTE名为" table_DT "是您的表名，因此您可以在其后使用select语句.

connect by level is useful for these problems. suppose the first CTE named "table_DT" is your table name so you can use the select statement after that.

with table_DT as (
    select 
        to_date('4/5/2013','mm/dd/yyyy') as Start_DT, 
        to_date('4/9/2013', 'mm/dd/yyyy') as End_DT, 
        1 as ID
    from dual
)
select 
    Start_DT + (level-1) as DT, 
    ID
from table_DT
connect by level <= End_DT - Start_DT +1
;

这篇关于从开始日期到结束日期的每个日期的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！