【日期整理，sql】这个sql有点难，求助！！！

[JuckFapan]

问题如下：
已知：
记录一：
start_date end_date
2005-1-1 2005-1-31
2005-2-1 2005-2-28
2005-3-1 2005-3-31
记录二：
2005-1-10 2005-2-10
2005-2-11 2005-2-20
2005-2-21 2005-3-31
要求得到记录三：
2005-1-1 2005-1-10
2005-1-11 2005-1-31
2005-2-1 2005-2-10
2005-2-11 2005-2-20
2005-2-21 2005-2-28
2005-3-1 2005-3-31

也就是将二组记录重新组成不交叉的一组新记录，不知我表达清楚没有

求SQL语句如何写，谢谢！

[wyb0026]

select * from 记录一
union all
select * from 记录二

[wyb0026]

还是没看懂

[JuckFapan]

请看清记录三，并不只是把二组记录简单的合并，谢～

[vivianfdlpw]

--创建测试环境
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 行）

*/

[dapei_712]

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

[JuckFapan]

多谢，偶试试先

[samfeng_2003]

[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 s1

update #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 #1

drop table #2

drop table s1

drop 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] 打上括号，如果你要验证，那么需要把括号去掉

[samfeng_2003]

vivianfdlpw() ,libin_ftsafe(子陌红尘),两位老大的都很高明啊！:)

study……

[wgsasd311]

--创建测试环境
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

[prcgolf]

up

[bugchen888]

复杂.学习.