34,592
社区成员
发帖
与我相关
我的任务
分享
with cte([编号] ,[日期] ,[时间] ,[姓名] ,[地址]) as(
select 1,'2017-08-02','10:59:00',N'小三',N'地址A' union all
select 2,'2017-08-02','11:01:00',N'小三',N'地址B'
)
select c1.* from cte c1 ,cte c2 where c1.姓名=c2.姓名 AND DATEDIFF(MINUTE, c2.日期+' '+c2.时间, c1.日期+' '+c1.时间) between 1 and 5
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([编号] int,[日期] NVARCHAR(100),[时间] NVARCHAR(100),[姓名] nvarchar(22),[地址] nvarchar(23))
Insert #T
select 1,'2017-08-02','10:59:00',N'小三',N'地址A' union all
select 2,'2017-08-02','11:01:00',N'小三',N'地址B'
Go
--测试数据结束
SELECT a.*
FROM #T a
JOIN ( SELECT b.姓名 ,
MAX(b.时间) AS 时间
FROM #T b
WHERE EXISTS ( SELECT 1
FROM #T c
WHERE b.姓名 = c.姓名
AND DATEDIFF(MINUTE, c.日期+' '+c.时间, b.日期+' '+b.时间) > 0
AND DATEDIFF(MINUTE, c.日期+' '+c.时间, b.日期+' '+b.时间) < 5 )
GROUP BY b.姓名
) t ON t.姓名 = a.姓名
AND t.时间 = a.时间
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
CREATE TABLE #T(id INT,name NVARCHAR(100),registertime DATETIME)
Insert #T
select 1,N'张三','2017-01-01 11:01:01' union all
select 2,N'张三','2017-01-01 11:11:01' union all
select 3,N'张三','2017-01-01 11:15:01' union all
select 4,N'李四','2017-01-01 11:15:01' union all
select 5,N'李四','2017-01-01 11:18:01'
Go
--测试数据结束
SELECT * ,
( SELECT TOP 1
registertime
FROM #T b
WHERE a.name = b.name
AND a.id <> b.id
AND ABS(DATEDIFF(MINUTE, a.registertime, b.registertime))<5
ORDER BY ABS(DATEDIFF(MINUTE, a.registertime, b.registertime))
) AS 最近时间
FROM #T a