先贴上你想要的结果 ,再解释--> 测试数据1: @A
declare @A table (showTime1 datetime,showTime2 datetime,state varchar(4),id int)
insert into @A
select '2007-01-01','2007-02-24','失效',3 union all
select '2007-02-25','2007-05-12','失效',6 union all
select '2007-05-13','2007-08-23','失效',8 union all
select '2007-08-24','2007-10-19','失效',13 union all
select '2007-10-20','2007-12-25','有效',34 union all
select '2007-12-26',null,'有效',45
--> 测试数据2: @B
declare @B table (showTime1 datetime,showTime2 datetime,state varchar(4),id int)
insert into @B
select '2007-03-12','2007-04-23','失效',25 union all
select '2007-04-24','2007-06-28','失效',56 union all
select '2007-06-29','2007-08-19','失效',37 union all
select '2007-08-20','2007-12-12','有效',35 union all
select '2007-12-13','2007-12-31','有效',67 --> 不用变量了,联接条件已经限定,必须是交集部分。当初考虑变量是left join的情况,现在inner join所以不用:
select
showTime1=
case
when b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999') then b.showTime1
else a.showTime1
end,
showTime2=
case
when isnull(b.showTime2,'9999') <=isnull(a.showTime2,'9999') then b.showTime2
else a.showTime2
end,
state=case when a.state='失效' or b.state='失效' then '失效' else '有效' end ,
ltrim(a.id)+','+ltrim(b.id) as idlist
from @A a inner join @B b
on (b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999'))
or (a.showTime1 between b.showTime1 and isnull(b.showTime2,'9999')) /*
showTime1 showTime2 state idlist
------------------------------------------------------ ------------------------------------------------------ ----- -------------------------
2007-03-12 00:00:00.000 2007-04-23 00:00:00.000 失效 6,25
2007-04-24 00:00:00.000 2007-05-12 00:00:00.000 失效 6,56
2007-05-13 00:00:00.000 2007-06-28 00:00:00.000 失效 8,56
2007-06-29 00:00:00.000 2007-08-19 00:00:00.000 失效 8,37
2007-08-20 00:00:00.000 2007-08-23 00:00:00.000 失效 8,35
2007-08-24 00:00:00.000 2007-10-19 00:00:00.000 失效 13,35
2007-10-20 00:00:00.000 2007-12-12 00:00:00.000 有效 34,35
2007-12-13 00:00:00.000 2007-12-25 00:00:00.000 有效 34,67
2007-12-26 00:00:00.000 2007-12-31 00:00:00.000 有效 45,67(所影响的行数为 9 行)
*/
declare @A table (showTime1 datetime,showTime2 datetime,state varchar(4),id int)
insert into @A
select '2007-01-01','2007-02-24','失效',3 union all
select '2007-02-25','2007-05-12','失效',6 union all
select '2007-05-13','2007-08-23','失效',8 union all
select '2007-08-24','2007-10-19','失效',13 union all
select '2007-10-20','2007-12-25','有效',34 union all
select '2007-12-26',null,'有效',45
--> 测试数据2: @B
declare @B table (showTime1 datetime,showTime2 datetime,state varchar(4),id int)
insert into @B
select '2007-03-12','2007-04-23','失效',25 union all
select '2007-04-24','2007-06-28','失效',56 union all
select '2007-06-29','2007-08-19','失效',37 union all
select '2007-08-20','2007-12-12','有效',35 union all
select '2007-12-13','2007-12-31','有效',67 --> 不用变量了,联接条件已经限定,必须是交集部分。当初考虑变量是left join的情况,现在inner join所以不用:
select
showTime1=
case
when b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999') then b.showTime1
else a.showTime1
end,
showTime2=
case
when isnull(b.showTime2,'9999') <=isnull(a.showTime2,'9999') then b.showTime2
else a.showTime2
end,
state=case when a.state='失效' or b.state='失效' then '失效' else '有效' end ,
ltrim(a.id)+','+ltrim(b.id) as idlist
from @A a inner join @B b
on (b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999'))
or (a.showTime1 between b.showTime1 and isnull(b.showTime2,'9999')) /*
showTime1 showTime2 state idlist
------------------------------------------------------ ------------------------------------------------------ ----- -------------------------
2007-03-12 00:00:00.000 2007-04-23 00:00:00.000 失效 6,25
2007-04-24 00:00:00.000 2007-05-12 00:00:00.000 失效 6,56
2007-05-13 00:00:00.000 2007-06-28 00:00:00.000 失效 8,56
2007-06-29 00:00:00.000 2007-08-19 00:00:00.000 失效 8,37
2007-08-20 00:00:00.000 2007-08-23 00:00:00.000 失效 8,35
2007-08-24 00:00:00.000 2007-10-19 00:00:00.000 失效 13,35
2007-10-20 00:00:00.000 2007-12-12 00:00:00.000 有效 34,35
2007-12-13 00:00:00.000 2007-12-25 00:00:00.000 有效 34,67
2007-12-26 00:00:00.000 2007-12-31 00:00:00.000 有效 45,67(所影响的行数为 9 行)
*/
已经有了,不贴了。解释你是找A和B时间断的公共交集部分,而且你的时间之间是有规律的——连续。将B.showTime1落在A的时间段范围匹配出来(B有多条记录落在A某时间断,就匹配出来多少条A,下同。):
on (b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999'))和A.showTime1落在B的时间段范围匹配出来
or (a.showTime1 between b.showTime1 and isnull(b.showTime2,'9999')) 不知道你理解不,你自己看一看俩表的匹配结果就理解了:select * from @A a inner join @B b
on (b.showTime1 between a.showTime1 and isnull(a.showTime2,'9999'))
or (a.showTime1 between b.showTime1 and isnull(b.showTime2,'9999'))
isnull(a.showTime2,'9999') == isnull(a.showTime2,'9999-01-01')
这个是我懒了,而且当时代码较长。对于1753-9999的4位数字字符串,SQL能隐式转换为当年1月1日的时间类型。我的字段定义为datetime类型,所以转换没有问题。如果是varchar类型,则要写9999-01-01或9999-12-31。