如何完成这样的查询？(高分求教)

[hqywork]

比如有一个字段为CreateDateTime,在这个表里有很多的记录，现在想查询出一组记录，符合这样的规则：
第Ｎ＋１条记录中的CreateDateTime - 第Ｎ条记录中的CreateDateTime总是大于一个常数M？

[yangala]

--倘若CreateDateTime是datetime型的话，你做个类型转换就可以了啊
select a.CreateDateTime from 表 a
where not exists
(select 1 from 表 b
where cast(b.CreateDateTime as float) < cast(a.CreateDateTime as float)
and cast(b.CreateDateTime as float) > cast(a.CreateDateTime as float)-M )

[marco08]

把表結構, 數據和想要的結果貼出來看看

[yangala]

上面的不行，呵呵，用这个吧：

select a.CreateDateTime from 表 a
where not exists
(select 1 from 表 b
where b.CreateDateTime < a.CreateDateTime
and b.CreateDateTime > a.b.CreateDateTime-M )

[xiequanqin]

借用marco08(天道酬勤) 的表~

create table T(ID int, CreateDateTime datetime)
insert T select 2, '2007-1-2 10:10:000'
insert T select 4, '2007-1-2 18:11:000'
insert T select 5, '2007-1-4 19:20:000'
insert T select 7, '2007-1-4 14:10:000'
insert T select 10, '2007-1-4 10:16:000'
insert T select 12, '2007-1-6 15:15:000'

select * from T a RIGHT JOIN T b ON b.ID=(select min(ID) FROM T where ID>a.ID)
where DATEDIFF(day,a.CreateDateTime,b.CreateDateTime)>1（>1天，分钟、小时改 DATEDIFF(day,a.CreateDateTime,b.CreateDateTime)>1）

[中国风]

楼主是要两个相邻的时间段还是两个相邻的id?

[中国风]

如果记录与时间都是递增的情况下，可以这样写
select *
from test t
where datediff(mi,(select max(CreateDateTime)from test where id<t.id),CreateDateTime)<60--60分的范围定义就行了
or
datediff(mi,CreateDateTime,(select min(CreateDateTime)from test where id>t.id))<60--60分的范围定义就行了

[中国风]

declare @test table (ID int, CreateDateTime datetime)
insert @test select 2, '2007-1-2 10:10:000'
union all select 4, '2007-1-2 18:11:000'
union all select 5, '2007-1-4 19:20:000'
union all select 7, '2007-1-4 14:10:000'
union all select 10, '2007-1-4 10:16:000'
union all select 12, '2007-1-6 15:15:000'
按时间定义:
select *
from @test t
where datediff(mi,(select max(CreateDateTime)from @test where CreateDateTime<t.CreateDateTime),CreateDateTime)>60--大于60分的范围定义就行了
or
datediff(mi,CreateDateTime,(select min(CreateDateTime)from @test where CreateDateTime>t.CreateDateTime))>60--大于60分的范围定义就行了

[marco08]

--try

create table T(ID int, CreateDateTime datetime)
insert T select 2, '2007-1-2 10:10:000'
insert T select 4, '2007-1-2 18:11:000'
insert T select 5, '2007-1-4 19:20:000'
insert T select 7, '2007-1-4 14:10:000'
insert T select 10, '2007-1-4 10:16:000'
insert T select 12, '2007-1-6 15:15:000'

select * from T as A
where (select top 1 datediff(minute, A.CreateDateTime, CreateDateTime) from T where ID>A.ID order by ID)>10
order by ID

[hqywork]

还是不行呀，那位大哥有正解呀

[yangala]

select a.CreateDateTime from 表 a
right join
(
select top 1 aa.CreateDateTime from 表 aa
where aa.CreateDateTime < a.CreateDateTime
order by aa.CreateDateTime desc
) b
on b.CreateDateTime-a.CreateDateTime>M

[yangala]

用里面的子查询来找到离N+1条记录最近的那条，作为第N条记录，然后用 right join做连接，条件是>M