if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[time] datetime,[C3] int)
insert [tb]
select 1,'2008-11-18 18:29:30',260 union all
select 2,'2008-11-18 18:30:30',260 union all
select 3,'2008-11-18 18:31:30',260 union all
select 4,'2008-11-18 18:40:30',260 union all
select 5,'2008-11-19 08:29:30',260 union all
select 6,'2008-11-19 08:30:30',260 union all
select 7,'2008-11-19 09:11:30',260 union all
select 8,'2008-11-19 09:25:30',260 union all
select 9,'2008-11-19 09:26:30',260select * from [tb] a
where exists(select 1 from tb where id<>a.id and abs(datediff(mi,time,a.time))<=5)--测试结果:
/*
id time C3
----------- ----------------------- -----------
1 2008-11-18 18:29:30.000 260
2 2008-11-18 18:30:30.000 260
3 2008-11-18 18:31:30.000 260
5 2008-11-19 08:29:30.000 260
6 2008-11-19 08:30:30.000 260
8 2008-11-19 09:25:30.000 260
9 2008-11-19 09:26:30.000 260(7 行受影响)*/
go
create table [tb]([id] int,[time] datetime,[C3] int)
insert [tb]
select 1,'2008-11-18 18:29:30',260 union all
select 2,'2008-11-18 18:30:30',260 union all
select 3,'2008-11-18 18:31:30',260 union all
select 4,'2008-11-18 18:40:30',260 union all
select 5,'2008-11-19 08:29:30',260 union all
select 6,'2008-11-19 08:30:30',260 union all
select 7,'2008-11-19 09:11:30',260 union all
select 8,'2008-11-19 09:25:30',260 union all
select 9,'2008-11-19 09:26:30',260select * from [tb] a
where exists(select 1 from tb where id<>a.id and abs(datediff(mi,time,a.time))<=5)--测试结果:
/*
id time C3
----------- ----------------------- -----------
1 2008-11-18 18:29:30.000 260
2 2008-11-18 18:30:30.000 260
3 2008-11-18 18:31:30.000 260
5 2008-11-19 08:29:30.000 260
6 2008-11-19 08:30:30.000 260
8 2008-11-19 09:25:30.000 260
9 2008-11-19 09:26:30.000 260(7 行受影响)*/
from tb a,tb b
where a.id = b.id-1 and abs(datediff(m,a.time,b.time))<5
select m.* from tb m , tb n where m.id = n.id - 1 and datediff(mi , m.time , n.time) <= 5select m.* from tb m , tb n where m.id = n.id - 1 and abs(datediff(mi , m.time , n.time) <= 5)select m.* from tb m where datediff(mi , m.time , (select top 1 time from tb n where n.time > m.time order by time desc)) <= 5
--用datediff函数就行了
不过还是非常感谢各位高手的帮助!给分结贴了!