如题,因为数据量比较大,根据我给的例子,大家再给一个高效率的写法
DECLARE @Table1 table(userid int,Tfrom datetime, Tto datetime)
insert into @Table1
select 1,'10/20/2008','10/22/2008'
union all
select 1,'10/20/2008','10/24/2008'
union all
select 6,'10/22/2009','10/25/2009'
union all
select 7,'10/24/2009','10/26/2009'select * from @Table1
DECLARE @Table2 table(userid int,Tfrom datetime)insert into @Table2
select 1,'10/21/2008'
union all
select 2,'11/26/2009'
union all
select 3,'11/28/2009'
union all
select 3,'11/29/2009'select * from @Table2--要求
--1,@Table2表中的Tfrom存在于@Table1中的Tfrom和Tto之间
--2,以及@Table2表中Tfrom不存在于@Table1中的Tfrom和Tto之间的数据--要求的数据如下:
--1 2008-10-21 00:00:00.000 2008-10-20 00:00:00.000 2008-10-22 00:00:00.000
--1 2008-10-21 00:00:00.000 2008-10-20 00:00:00.000 2008-10-24 00:00:00.000
--2 2009-11-26 00:00:00.000 null null
--3 2009-11-28 00:00:00.000 null null
--3 2009-11-29 00:00:00.000 null null
select * from @Table2 t2
WHERE EXISTS (SELECT 1 FROM @Table1 t1 WHERE t2.Tfrom between t1.Tfrom and TTo)
union all
select * from @Table2 t2
WHERE Not EXISTS (SELECT 1 FROM @Table1 t1 WHERE t2.Tfrom between t1.Tfrom and TTo)
DECLARE @Table1 table(userid int,Tfrom datetime, Tto datetime)
insert into @Table1
select 1,'10/20/2008','10/22/2008'
union all
select 1,'10/20/2008','10/24/2008'
union all
select 6,'10/22/2009','10/25/2009'
union all
select 7,'10/24/2009','10/26/2009'select * from @Table1
DECLARE @Table2 table(userid int,Tfrom datetime)insert into @Table2
select 1,'10/21/2008'
union all
select 2,'11/26/2009'
union all
select 3,'11/28/2009'
union all
select 3,'11/29/2009'select * from @Table2--要求
--1,@Table2表中的Tfrom存在于@Table1中的Tfrom和Tto之间
--2,以及@Table2表中Tfrom不存在于@Table1中的Tfrom和Tto之间的数据--要求的数据如下:
--1 2008-10-21 00:00:00.000 2008-10-20 00:00:00.000 2008-10-22 00:00:00.000
--1 2008-10-21 00:00:00.000 2008-10-20 00:00:00.000 2008-10-24 00:00:00.000
--2 2009-11-26 00:00:00.000 null null
--3 2009-11-28 00:00:00.000 null null
--3 2009-11-29 00:00:00.000 null null
select * from @Table2 t2
WHERE EXISTS (SELECT 1 FROM @Table1 t1 WHERE t2.Tfrom between t1.Tfrom and TTo)
union all
select * from @Table2 t2
WHERE Not EXISTS (SELECT 1 FROM @Table1 t1 WHERE t2.Tfrom between t1.Tfrom and TTo)
select * from @Table2 t2 left join @Table1 t1 on t2.tfrom between t1.tfrom and t1.tto
当然在t1的tfrom和tto建立非聚焦索引,在t2.tfrom上面建立唯一索引,也能显著提高效率