22,210
社区成员
发帖
与我相关
我的任务
分享
(t3<=t1 and t4>=t1) or
(t3<=t2 and t4>=t2) or
(t3>=t1 and t4<=t2) or
(t3<=t1 and t4>=t2)
CREATE TABLE T1
(
id int,
t1 DATETIME,
t2 DATETIME
)
CREATE TABLE T2
(
id int,
t3 DATETIME,
t4 DATETIME
)
INSERT INTO T1
SELECT 1,GETDATE(),GETDATE()+10 union all
SELECT 2,GETDATE(),GETDATE()+10
INSERT INTO T2
SELECT 1,GETDATE()+5,GETDATE()+15 union all
SELECT 3,GETDATE()+15,GETDATE()+20
--找出重叠数据
SELECT A.t1,A.t2,B.t3,B.t4
FROM T1 A join T2 B on A.id=B.ID
WHERE B.t3<=A.t2 AND B.t4>=A.t1
/*
--------------------------------------------------------
t1 t2 t3 t4
2011-01-11 11:44:48.557 2011-01-21 11:44:48.557 2011-01-16 11:44:48.610 2011-01-26 11:44:48.610
*/
t1 between t3 and t4 or
t2 between t3 and t4 or
t3 between t1 and t2 or
t4 between t1 and t2