TABLE_A
StartDate EndDate CID
7/1 7/10 A
7/11 7/25 B
7/26 7/31 C
TABLE_B
StartDate EndDate Num
7/1 7/5 100
7/6 7/20 200
7/21 7/31 300
希望得到如下结果
StartDate EndDate CID Num
7/1 7/5 A 100
7/6 7/10 A 200
7/11 7/20 B 200
7/21 7/25 B 300
7/26 7/31 C 300
请教各位大仙该如何来写
StartDate EndDate CID
7/1 7/10 A
7/11 7/25 B
7/26 7/31 C
TABLE_B
StartDate EndDate Num
7/1 7/5 100
7/6 7/20 200
7/21 7/31 300
希望得到如下结果
StartDate EndDate CID Num
7/1 7/5 A 100
7/6 7/10 A 200
7/11 7/20 B 200
7/21 7/25 B 300
7/26 7/31 C 300
请教各位大仙该如何来写
给你类似的帖子链接:http://topic.csdn.net/u/20100904/19/c3c58b5a-ac04-4116-bae4-0bba7be5bd37.html
with a as
(
select to_date('2010/07/01','yyyy/mm/dd') as sd
,to_date('2010/07/10','yyyy/mm/dd') as ed,'A' as sid from dual
union all
select to_date('2010/07/11','yyyy/mm/dd') as sd
,to_date('2010/07/25','yyyy/mm/dd') as ed,'B' as sid from dual
union all
select to_date('2010/07/26','yyyy/mm/dd') as sd
,to_date('2010/07/31','yyyy/mm/dd') as ed,'c' as sid from dual
), b as
(
select to_date('2010/07/01','yyyy/mm/dd') as sd
,to_date('2010/07/5','yyyy/mm/dd') as ed,100 as num from dual
union all
select to_date('2010/07/6','yyyy/mm/dd') as sd
,to_date('2010/07/20','yyyy/mm/dd') as ed,200 from dual
union all
select to_date('2010/07/21','yyyy/mm/dd') as sd
,to_date('2010/07/31','yyyy/mm/dd') as ed,300 from dual
)
select greatest(a.sd,b.sd) as sd
,least(a.ed,b.ed) as ed
,a.sid
,b.num
from a,b
where a.sd between b.sd and b.ed or a.ed between b.sd and b.ed
with a as
(
select to_date('2010/07/01','yyyy/mm/dd') as sd
,to_date('2010/07/10','yyyy/mm/dd') as ed,'A' as sid from dual
union all
select to_date('2010/07/11','yyyy/mm/dd') as sd
,to_date('2010/07/25','yyyy/mm/dd') as ed,'B' as sid from dual
union all
select to_date('2010/07/26','yyyy/mm/dd') as sd
,to_date('2010/07/31','yyyy/mm/dd') as ed,'c' as sid from dual
), b as
(
select to_date('2010/07/01','yyyy/mm/dd') as sd
,to_date('2010/07/5','yyyy/mm/dd') as ed,100 as num from dual
union all
select to_date('2010/07/6','yyyy/mm/dd') as sd
,to_date('2010/07/20','yyyy/mm/dd') as ed,200 from dual
union all
select to_date('2010/07/21','yyyy/mm/dd') as sd
,to_date('2010/07/31','yyyy/mm/dd') as ed,300 from dual
)
select greatest(a.sd,b.sd) as sd
,least(a.ed,b.ed) as ed
,a.sid
,b.num
from a,b
where a.sd between b.sd and b.ed or a.ed between b.sd and b.ed