--创建测试环境 create table A ( start_date varchar(10), end_date varchar(10) ) create table B ( start_date varchar(10), end_date varchar(10) ) insert A select '2005-1-1','2005-1-31' union select '2005-2-1','2005-2-28' union select '2005-3-1','2005-3-31' insert B select '2005-1-10','2005-2-10' union select '2005-2-11','2005-2-20' union select '2005-2-21','2005-3-31' go--查询 select identity(int,1,1) as ID ,dt into # from ( select start_date as dt from B union select end_date from B union select start_date from A union select end_date from A )t order by dt select (case when exists(select 1 from # where convert(char(7),convert(datetime,dt),120) =convert(char(7),convert(datetime,A.dt),120) and ID<A.ID ) and not exists(select 1 from # where convert(char(10),convert(datetime,dt),120) =convert(char(10),dateadd(day,-11,convert(datetime,A.dt)),120) ) then convert(char(10),dateadd(day,1,convert(datetime,A.dt)),120) else convert(char(10),convert(datetime,A.dt),120) end )as start_date ,convert(char(10),convert(datetime,B.dt),120) as end_date from # A join # B on A.ID+1=B.ID and datediff(day,convert(datetime,A.dt),convert(datetime,B.dt))<>1 where exists(select 1 from # where convert(char(7),convert(datetime,dt),120)=convert(char(7),convert(datetime,A.dt),120) and ID>A.ID ) order by A.dt--删除测试环境 drop table # drop table A,B--结果 /* start_date end_date ---------- ---------- 2005-01-01 2005-01-10 2005-01-11 2005-01-31 2005-02-01 2005-02-10 2005-02-11 2005-02-20 2005-02-21 2005-02-28 2005-03-01 2005-03-31(所影响的行数为 6 行)*/
create view A as select start_date as date from 记录一 union all select end_date as date from 记录一 union all select start_date as date from 记录二 union all select end_date as date from 记录二 oder by date ——这样先取出所有日期,再从最小日期开始每取2条,就把第一条作为记录三的第一列,第二条作为记录三的第二列
CREATE TABLE #T1(start_date DATETIME,end_date DATETIME) INSERT INTO #T1 SELECT '2005-1-1','2005-1-31' INSERT INTO #T1 SELECT '2005-2-1','2005-2-28' INSERT INTO #T1 SELECT '2005-3-1','2005-3-31'CREATE TABLE #T2(start_date DATETIME,end_date DATETIME) INSERT INTO #T2 SELECT '2005-1-10','2005-2-10' INSERT INTO #T2 SELECT '2005-2-11','2005-2-20' INSERT INTO #T2 SELECT '2005-2-21','2005-3-31'SELECT C.* FROM (SELECT start_date = A.DATE1, end_date = MIN(B.DATE1) FROM (SELECT start_date AS DATE1 FROM #T1 UNION SELECT end_date FROM #T1 UNION SELECT start_date FROM #T2 UNION SELECT end_date FROM #T2) A, (SELECT start_date AS DATE1 FROM #T1 UNION SELECT end_date FROM #T1 UNION SELECT start_date FROM #T2 UNION SELECT end_date FROM #T2) B WHERE A.DATE1<B.DATE1 GROUP BY A.DATE1) C WHERE DATEDIFF(DD,C.start_date,C.end_date)>1
[create] table s1 (start_date datetime,end_date datetime)[create] table s2 (start_date datetime,end_date datetime)insert s1 select '2005-01-01','2005-01-31' [union] select '2005-02-01','2005-02-28' [union] select '2005-03-01','2005-03-31'insert s2 select '2005-01-10','2005-02-10' [union] select '2005-02-11','2005-02-20' [union] select '2005-02-21','2005-03-31'/*--程序开始---*/select a.start_date as s1,b.start_date as s2 into #1 from s1 a,s2 b [union] select a.start_date as s1,b.end_date as s2 from s1 a,s2 b [union] select b.start_date as s1,a.end_date as s2 from s1 a,s2 b [union] select b.end_date as s1,a.end_date as s2 from s1 a,s2 b [union] select * from s1 [union] select * from s2 select s1,min(s2) as s2 into #2 from #1 where convert(char(7),s1,120)=convert(char(7),s2,120) group by s1update #2 set s1=dateadd(d,1,s1) where s1 in (select a.s1 from #2 a,#2 b where a.s1=b.s2 and a.s1 not in (select max(s1) from #2))select s1,min(s2) as s2 from #2 where s1 not in (select max(s1) from #2) group by s1 drop table #1drop table #2drop table s1drop table s2/*------结果--------*/ s1 s2 ---------------------------- ------------------------------ 2005-01-01 00:00:00.000 2005-01-10 00:00:00.000 2005-01-11 00:00:00.000 2005-01-31 00:00:00.000 2005-02-01 00:00:00.000 2005-02-10 00:00:00.000 2005-02-11 00:00:00.000 2005-02-20 00:00:00.000 2005-02-21 00:00:00.000 2005-02-28 00:00:00.000 2005-03-01 00:00:00.000 2005-03-31 00:00:00.000(所影响的行数为 6 行)因为数据传不上来,我在必须给[create] 和 [union] 打上括号,如果你要验证,那么需要把括号去掉
--创建测试环境 create table A ( start_date datetime, end_date datetime ) create table B ( start_date datetime, end_date datetime ) insert A select '2005-1-1','2005-1-31' union select '2005-2-1','2005-2-28' union select '2005-3-1','2005-3-31' insert B select '2005-1-10','2005-2-10' union select '2005-2-11','2005-2-20' union select '2005-2-21','2005-3-31' go--查询 select identity(int,1,1) as ID ,dt into # from ( select start_date as dt from B union select end_date from B union select start_date from A union select end_date from A )t order by dt select a.id,(case when a.id>1 and exists (select 1 from # c join # d on c.id+1=d.id where datediff(day,c.dt,d.dt)>1 and c.[id]=a.id-1 and d.dt=a.dt) then a.dt+1 else a.dt end),b.dt from # a join # b on a.id+1=b.id where datediff(day,a.dt,b.dt)>1--删除测试环境 drop table # drop table A,B
create table A
(
start_date varchar(10),
end_date varchar(10)
)
create table B
(
start_date varchar(10),
end_date varchar(10)
)
insert A
select '2005-1-1','2005-1-31' union
select '2005-2-1','2005-2-28' union
select '2005-3-1','2005-3-31'
insert B
select '2005-1-10','2005-2-10' union
select '2005-2-11','2005-2-20' union
select '2005-2-21','2005-3-31'
go--查询
select identity(int,1,1) as ID
,dt
into #
from (
select start_date as dt from B
union
select end_date from B
union
select start_date from A
union
select end_date from A
)t
order by dt
select (case when exists(select 1
from #
where convert(char(7),convert(datetime,dt),120)
=convert(char(7),convert(datetime,A.dt),120)
and ID<A.ID
)
and
not exists(select 1
from #
where convert(char(10),convert(datetime,dt),120)
=convert(char(10),dateadd(day,-11,convert(datetime,A.dt)),120)
)
then convert(char(10),dateadd(day,1,convert(datetime,A.dt)),120)
else convert(char(10),convert(datetime,A.dt),120)
end
)as start_date
,convert(char(10),convert(datetime,B.dt),120) as end_date
from # A
join # B on A.ID+1=B.ID and datediff(day,convert(datetime,A.dt),convert(datetime,B.dt))<>1
where exists(select 1
from #
where convert(char(7),convert(datetime,dt),120)=convert(char(7),convert(datetime,A.dt),120)
and ID>A.ID
)
order by A.dt--删除测试环境
drop table #
drop table A,B--结果
/*
start_date end_date
---------- ----------
2005-01-01 2005-01-10
2005-01-11 2005-01-31
2005-02-01 2005-02-10
2005-02-11 2005-02-20
2005-02-21 2005-02-28
2005-03-01 2005-03-31(所影响的行数为 6 行)*/
as
select start_date as date from 记录一
union all
select end_date as date from 记录一
union all
select start_date as date from 记录二
union all
select end_date as date from 记录二
oder by date
——这样先取出所有日期,再从最小日期开始每取2条,就把第一条作为记录三的第一列,第二条作为记录三的第二列
INSERT INTO #T1 SELECT '2005-1-1','2005-1-31'
INSERT INTO #T1 SELECT '2005-2-1','2005-2-28'
INSERT INTO #T1 SELECT '2005-3-1','2005-3-31'CREATE TABLE #T2(start_date DATETIME,end_date DATETIME)
INSERT INTO #T2 SELECT '2005-1-10','2005-2-10'
INSERT INTO #T2 SELECT '2005-2-11','2005-2-20'
INSERT INTO #T2 SELECT '2005-2-21','2005-3-31'SELECT
C.*
FROM
(SELECT
start_date = A.DATE1,
end_date = MIN(B.DATE1)
FROM
(SELECT start_date AS DATE1 FROM #T1
UNION
SELECT end_date FROM #T1
UNION
SELECT start_date FROM #T2
UNION
SELECT end_date FROM #T2) A,
(SELECT start_date AS DATE1 FROM #T1
UNION
SELECT end_date FROM #T1
UNION
SELECT start_date FROM #T2
UNION
SELECT end_date FROM #T2) B
WHERE
A.DATE1<B.DATE1
GROUP BY
A.DATE1) C
WHERE
DATEDIFF(DD,C.start_date,C.end_date)>1
(start_date datetime,end_date datetime)[create] table s2
(start_date datetime,end_date datetime)insert s1
select '2005-01-01','2005-01-31' [union]
select '2005-02-01','2005-02-28' [union]
select '2005-03-01','2005-03-31'insert s2
select '2005-01-10','2005-02-10' [union]
select '2005-02-11','2005-02-20' [union]
select '2005-02-21','2005-03-31'/*--程序开始---*/select a.start_date as s1,b.start_date as s2 into #1 from s1 a,s2 b
[union]
select a.start_date as s1,b.end_date as s2 from s1 a,s2 b
[union]
select b.start_date as s1,a.end_date as s2 from s1 a,s2 b
[union]
select b.end_date as s1,a.end_date as s2 from s1 a,s2 b
[union]
select * from s1
[union]
select * from s2
select s1,min(s2) as s2 into #2 from #1 where convert(char(7),s1,120)=convert(char(7),s2,120)
group by s1update #2 set s1=dateadd(d,1,s1)
where s1 in
(select a.s1 from #2 a,#2 b where a.s1=b.s2 and a.s1 not in (select max(s1) from #2))select s1,min(s2) as s2 from #2 where s1 not in (select max(s1) from #2) group by s1 drop table #1drop table #2drop table s1drop table s2/*------结果--------*/
s1 s2
---------------------------- ------------------------------
2005-01-01 00:00:00.000 2005-01-10 00:00:00.000
2005-01-11 00:00:00.000 2005-01-31 00:00:00.000
2005-02-01 00:00:00.000 2005-02-10 00:00:00.000
2005-02-11 00:00:00.000 2005-02-20 00:00:00.000
2005-02-21 00:00:00.000 2005-02-28 00:00:00.000
2005-03-01 00:00:00.000 2005-03-31 00:00:00.000(所影响的行数为 6 行)因为数据传不上来,我在必须给[create] 和 [union] 打上括号,如果你要验证,那么需要把括号去掉
create table A
(
start_date datetime,
end_date datetime
)
create table B
(
start_date datetime,
end_date datetime
)
insert A
select '2005-1-1','2005-1-31' union
select '2005-2-1','2005-2-28' union
select '2005-3-1','2005-3-31'
insert B
select '2005-1-10','2005-2-10' union
select '2005-2-11','2005-2-20' union
select '2005-2-21','2005-3-31'
go--查询
select identity(int,1,1) as ID
,dt
into #
from (
select start_date as dt from B
union
select end_date from B
union
select start_date from A
union
select end_date from A
)t
order by dt
select a.id,(case when a.id>1 and exists
(select 1 from # c join # d on c.id+1=d.id
where datediff(day,c.dt,d.dt)>1 and c.[id]=a.id-1 and d.dt=a.dt)
then a.dt+1 else a.dt end),b.dt
from # a join # b on a.id+1=b.id where datediff(day,a.dt,b.dt)>1--删除测试环境
drop table #
drop table A,B