Oracle一个表存有一个日期段，怎么根据这个日期段获取该段时间的每天一条数据？

发布网友发布时间：2022-04-08 00:15

共2个回答

热心网友时间：2022-04-08 01:45

为了保证有跨月数据的产生，我在你基础上多加了条数据

create table t1
(id int,
startdate date,
enddate date)

insert into t1 values (1,to_date('2014-07-01','yyyy-mm-dd'),to_date('2014-07-03','yyyy-mm-dd'));
insert into t1 values (2,to_date('2014-08-04','yyyy-mm-dd'),to_date('2014-08-05','yyyy-mm-dd'));
insert into t1 values (3,to_date('2014-07-30','yyyy-mm-dd'),to_date('2014-08-02','yyyy-mm-dd'));

执行

select s1.id,s1.startdate+s2.rn
from
(select t1.id,t1.startdate,t1.enddate-t1.startdate datevalue from t1) s1,
(select rownum-1 rn from al connect by rownum<=(select max(enddate-startdate)+1 from t1)) s2
where s2.rn<=s1.datevalue
order by s1.id,s1.startdate+s2.rn

结果

热心网友时间：2022-04-08 03:03

select t1.id,t2.v_date
from (select to_date('20140101','YYYYMMDD')+rownum as v_date from user_objects where rownum<=1000) as t2,
T1
where t2.v_date between t1.startdate and t1.enddate