在Oracle 12c
cross apply上可以使用的子句：
create table e_vents( name varchar2(10), startdate date, enddate date);insert all into e_vents values( 'A', date '2017-12-15', date '2018-01-17' )into e_vents values( 'B', date '2017-12-15', date '2017-12-22' )into e_vents values( 'C', date '2017-12-15', date '2018-05-22' )select null from dual;commit;
select e.name, case when e.startdate > x.s_date then e.startdate else x.s_date end as start_date, case when e.enddate < x.e_date then e.enddate else x.e_date end as end_datefrom e_vents ecross apply ( select trunc( e.startdate, 'mm') + (level-1) * interval '1' month as s_date, trunc( e.startdate + (level) * interval '1' month, 'mm') -1 as e_date from dual connect by level <= months_between( trunc( e.enddate, 'mm'),trunc( e.startdate, 'mm')) + 1) x
NAME START_DATE END_DATE ---------- ---------- ----------A 2017-12-15 2017-12-31A 2018-01-01 2018-01-17B 2017-12-15 2017-12-22C 2017-12-15 2017-12-31C 2018-01-01 2018-01-31C 2018-02-01 2018-02-28C 2018-03-01 2018-03-31C 2018-04-01 2018-04-30C 2018-05-01 2018-05-229 rows selected.
