17,377
社区成员
发帖
与我相关
我的任务
分享
create table shijian(
uuid VARCHAR2(50),
rq_b VARCHAR2(30),
rq_e VARCHAR2(30)
)
;
insert into shijian (UUID, RQ_B, RQ_E)
values ('001', '2010-01-01', '2010-10-31');
insert into shijian (UUID, RQ_B, RQ_E)
values ('002', '2010-08-01', '2010-12-31');
insert into shijian (UUID, RQ_B, RQ_E)
values ('001', '2010-01-01', '2010-10-31');
insert into shijian (UUID, RQ_B, RQ_E)
values ('002', '2010-08-01', '2010-12-31');
select add_months(to_date(substr(rq_b, 1, 7), 'yyyy-mm'),level-1) duration
from shijian,
(select min(rq_b) rq_min, max(rq_b) rq_max from shijian) minmax
where shijian.rq_b = minmax.rq_min
connect by level <= months_between(to_date(substr(rq_e, 1, 7), 'yyyy-mm'),
to_date(substr(rq_b, 1, 7), 'yyyy-mm'))+1
union
select add_months(to_date(substr(rq_b, 1, 7), 'yyyy-mm'),level-1) duration
from shijian,
(select min(rq_b) rq_min, max(rq_b) rq_max from shijian) minmax
where shijian.rq_b = minmax.rq_max
connect by level <= months_between(to_date(substr(rq_e, 1, 7), 'yyyy-mm'),
to_date(substr(rq_b, 1, 7), 'yyyy-mm'))+1;
'001'
, '2010-08-01', '2010-12-31');啊