SELECT * FROM @T1 a
WHERE EXISTS
(
SELECT 1 FROM @t1
WHERE tid < a.tid AND
deviceid = a.deviceid AND
userID = a.userID AND
abs(DATEDIFF(ss,usertime,a.usertime))<=60
)
--测试过程
declare @T1 table(tID int, userID varchar(4),userTime datetime,deviceid int)
insert into @T1
SELECT 1,'0001','2001-10-10 12:10:00',1
UNION ALL SELECT 2,'0001','2001-10-10 12:10:00',1
UNION ALL SELECT 3,'0001','2001-10-10 12:11:00',1
UNION ALL SELECT 4,'0001','2001-10-10 12:11:00',1
UNION ALL SELECT 5,'0001','2001-10-10 12:12:00',1
UNION ALL SELECT 6,'0001','2001-10-10 12:13:00',1
UNION ALL SELECT 7,'0001','2001-10-10 12:15:00',1
UNION ALL SELECT 8,'0001','2001-10-10 12:15:00',1
--开始查询
SELECT * FROM @T1 a
WHERE EXISTS
(
SELECT 1 FROM @t1
WHERE tid > a.tid AND
deviceid = a.deviceid AND
userID = a.userID AND
abs(DATEDIFF(ss,usertime,a.usertime))<=60
)