TableA
startDate endDate
4/1 4/15
TableB
startDate endDate
4/10 4/30
想要的结果
4/1 4/9
4/10 4/15
4/16 4/30用sql文可以实现么?
startDate endDate
4/1 4/15
TableB
startDate endDate
4/10 4/30
想要的结果
4/1 4/9
4/10 4/15
4/16 4/30用sql文可以实现么?
SELECT dt,lead(dt)over(order by dt) dt2 FROM(
select startDate dt from TableA
union all
select endDate dt from TableA
union all
select startDate dt from TableB
union all
select endDate dt from TableB)
ORDER BY dt
)
where dt2 is not null;
,t2 AS(SELECT enddate FROM tablea UNION SELECT enddate FROM tableb)
SELECT nvl(startdate,regexp_substr(lg,'^[[:digit:]]+/')||(regexp_replace(lg,'^([[:digit:]]+)/([[:digit:]]+)$','\2')+1)) startdate,
nvl(enddate,regexp_substr(ld,'^[[:digit:]]+/')||(regexp_replace(ld,'^([[:digit:]]+)/([[:digit:]]+)$','\2')-1)) enddate
from(
SELECT A.startdate,b.enddate,lag(enddate)OVER(ORDER BY enddate) lg,
lead(startdate)OVER(ORDER BY startdate) ld
FROM t1 A FULL JOIN t2 b
ON to_date(A.startdate,'mm/dd')<to_date(b.enddate,'mm/dd')
AND NOT EXISTS(SELECT NULL FROM t1 WHERE to_date(startdate,'mm/dd')>to_date(A.startdate,'mm/dd')
AND to_date(startdate,'mm/dd')<to_date(b.enddate,'mm/dd'))
AND NOT EXISTS(SELECT NULL FROM t2 WHERE to_date(enddate,'mm/dd')<to_date(b.enddate,'mm/dd')
and to_date(A.startdate,'mm/dd')<to_date(enddate,'mm/dd')))
with t1 as (
select '4/1' startDate,'4/15' endDate from dual
union all
select '4/10' startDate,'4/30' endDate from dual
-- 自行插入其他数据验证
-- union all
-- select '4/5' startDate,'4/20' endDate from dual
),
t2 as(
select 0 id,to_date(startDate,'mm/dd') dt from t1
union all
select 1 id,to_date(endDate,'mm/dd') dt from t1
order by dt
),
t3 as(
select
max(decode(id,0,dt)) startDate,
max(decode(id,1,dt)) endDate
from t2
group by rownum-id
),
t4 as(
select
(case when startDate is null
then (lag(endDate) over (order by endDate))+1
else startDate end) startDate,
(case when endDate is null
then (lead(startDate) over (order by startDate))-1
else endDate end) endDate
from t3
)
select
to_char(startDate,'mm/dd') startDate,
to_char(endDate,'mm/dd') endDate
from t4
//*
04/01 04/09
04/10 04/15
04/16 04/30
*/