从开始日期到结束日期的每个日期的行
问题描述：
我想做的是记录一张看起来像这样的记录:
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
;