SQL Server如何做这种记录

[hyc_music1981]

已知表1, 表2, 其中表2中的每两段时间范围都不会有重叠现象, 怎么得到表3, 3个表内容如下:

表1:
2013-01-01 2013-12-31

表2:
2013-03-01 2013-03-31
2013-05-01 2013-05-31

表3:
2013-01-01 2013-02-28
2013-04-01 2013-04-30
2013-06-01 2013-12-31

提供测试SQL如下:

create table #a(date1 datetime, date2 datetime)
create table #b(sd datetime, ed datetime)
GO

insert into #a values('2013-01-01', '2013-12-31')
insert into #b values('2013-03-01', '2013-03-31')
insert into #b values('2013-05-01', '2013-05-31')

[hyc_music1981]

都提前过周末了?

[hyc_music1981]

看来又没人回答了

[szm341]

搞完回家~~

create table #t1(id int identity primary key,dts date,dte date)
create table #t2(id int identity primary key,dts date,dte date)

insert into #t1
select '2013-01-01','2013-12-31'

insert into #t2
select '2013-03-01', '2013-03-31'
union all select '2013-05-01', '2013-05-31'

;with cte as(
select dts,dte From #t1
union all
select dateadd(day,1,dte),dateadd(day,-1,dts) from #t2
)
select a.dts,b.dte from 
(select *,ROW_NUMBER() over(order by dts)rn from cte)a,
(select *,ROW_NUMBER() over(order by dte)rn from cte)b
where a.rn=b.rn

/*
dts        dte
---------- ----------
2013-01-01 2013-02-28
2013-04-01 2013-04-30
2013-06-01 2013-12-31

(3 行受影响)

*/

[hyc_music1981]

谢谢, 功能实现了, 但代码着实看不懂...