求一sql文写法

phoenix_zg 2010-09-04 07:23:44

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文可以实现么？

...全文

151 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

minitoy 2010-09-06

打赏
举报

不好说.

xman_78tom 2010-09-04

打赏
举报



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

*/

小灰狼W 2010-09-04

打赏
举报

WITH t1 AS(SELECT startdate FROM tablea UNION SELECT startdate FROM tableb)

,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')))

cyousor 2010-09-04

打赏
举报

[Quote=引用 2 楼 gelyon 的回复:]

SELECT dt, dt2-1 from (
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……
[/Quote]
+1

gelyon 2010-09-04

打赏
举报

SELECT dt, dt2-1 from (
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;