1.创建表
create table uuser(
userId integer,
start_dt date,
end_dt date
)
插入数据:
insert into uuser(userId,start_dt,end_dt) values(1001,to_date('2019/04/18','yyyy/mm/dd'),to_date('2019/04/20','yyyy/mm/dd'));
insert into uuser(userId,start_dt,end_dt) values(1002,to_date('2019/04/18','yyyy/mm/dd'),to_date('2019/04/20','yyyy/mm/dd'));
select * from uuser order by userId;
查询结果如图所示:
2.伪列和分级查询的综合使用
ITH TB AS
(SELECT '1001' userId, to_date ('2019-04-18','yyyy/mm/dd') start_dt, date '2019-04-20' end_dt FROM dual
UNION ALL SELECT '1002' userId , date '2019-04-18' , date '2019-04-22' FROM dual
)
select TB.userId,TB.start_dt+level-1
from TB
connect by level<=(TB.end_dt-TB.start_dt+1)
and prior dbms_random.value is not null;