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

hqywork 2007-01-16 12:27:07
比如有一个字段为CreateDateTime,在这个表里有很多的记录,现在想查询出一组记录,符合这样的规则:
第N+1条记录中的CreateDateTime - 第N条记录中的CreateDateTime总是大于一个常数M?
...全文
264 11 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yangala 2007-01-16
用里面的子查询来找到离N+1条记录最近的那条,作为第N条记录,然后用 right join做连接,条件是>M
  • 打赏
  • 举报
回复
yangala 2007-01-16

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
  • 打赏
  • 举报
回复
hqywork 2007-01-16
还是不行呀,那位大哥有正解呀
  • 打赏
  • 举报
回复
marco08 2007-01-16
--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
  • 打赏
  • 举报
回复
中国风 2007-01-16
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分的范围定义就行了



  • 打赏
  • 举报
回复
中国风 2007-01-16
如果记录与时间都是递增的情况下,可以这样写
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分的范围定义就行了
  • 打赏
  • 举报
回复
中国风 2007-01-16
楼主是要两个相邻的时间段还是两个相邻的id?
  • 打赏
  • 举报
回复
xiequanqin 2007-01-16
借用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)

  • 打赏
  • 举报
回复
yangala 2007-01-16

上面的不行,呵呵,用这个吧:

select a.CreateDateTime from 表 a
where not exists
(select 1 from 表 b
where b.CreateDateTime < a.CreateDateTime
and b.CreateDateTime > a.b.CreateDateTime-M )
  • 打赏
  • 举报
回复
marco08 2007-01-16
把表結構, 數據和想要的結果貼出來看看
  • 打赏
  • 举报
回复
yangala 2007-01-16
--倘若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 )
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
申请成为版主
帖子事件
创建了帖子
2007-01-16 12:27
社区公告
暂无公告