select * from yourtable where [time]>=@datevalue
and datediff(second,(select min([time]) from yourtable where [time]>@datevalue),[time])%30=0 and [time]<datevalue1
and datediff(second,(select min([time]) from yourtable where [time]>@datevalue),[time])%30=0 and [time]<datevalue1
select * from track where datediff(n,'2004-6-8 01:00:05',time) %30 =0 and time>='2004-6-8 01:00:05' and time <='2004-6-8 10:25:36'
select * from yourtable where [time]>=@datevalue
and datediff(second,(select min([time]) from yourtable where [time]>@datevalue),[time])%30=0 and [time]<@datevalue1 --@datevalue,datetime型变量,yourtable-->track,不改了
while @begintime<=@endtime
begin
set @begintime=dateadd(minute,@i*30,@begintime)
insert #t select @begintime
set @i=@i+1
end select (select top 1 [time] from track where [time]>=a.stdtime) from #t adrop table #t注:想当然的,没有测试过。楼主要是测试了,记得告诉结果。
create table #t (t datetime)
declare @bt datetime,@dt datetime
set @bt='2004-6-8 12:00:25'
set @dt='2004-6-8 12:01:34'
set @i=1
select @bt=min([time]) from track where [time]>=@bt and [time]<=@dt
while @bt<=@dt
begin
insert into #t select @bt
select @bt=min([time]) from track where [time]>=dateadd(second,30,@bt) and [time]<=@dt --30分钟将second 改为minute
end
select a.* from track a join #t b on a.[time]=b.t
declare @dt1 datetime,@dt2 datetime
select @dt1='2004-6-8 01:00:05'
,@dt2='2004-6-8 10:25:36'--查询
select * from Track
where [time] between @dt1 and @dt2
and datediff(second,@dt1,[time])%1800=0
to:邹大哥,我的问题写错了一个字,应该是“每一分钟有几个记录,而且是没有重复的,"就是说每隔几秒有一条记录。如果隔30分钟没有这个时间点的记录,就取下一条记录。to: poka(poka)
我试过你的语句,好像不行。to:zheninchangjiang(我爱燕子)
我试过你的语句,我将你的语句改进,如下
create table #t (t datetime)
declare @bt datetime,@dt datetime
declare @i int
set @bt='2004-6-8 01:00:05'
set @dt='2004-6-8 10:25:36'
set @i=1
select @bt=min([time]) from track where [time]>=@bt and [time]<=@dt
while @bt<=@dt
begin
insert into #t select @bt
select @bt=min([time]) from track where [time]>=dateadd(minute,@i*30,@bt) and [time]<=@dt --30分钟将second 改为minute
set @i=1
end
select a.* from ai_track a join #t b on a.gpstime=b.t
drop table #t结果能得到我想要的数据。to:victorycyz(中海)
你的语句我已经进行测试,我将你的语句进行改进,如下:
create table #t (stdTime datetime)
declare @i int
declare @bTime datetime
declare @eTime datetime
set @bTime ='2004-6-8 01:00:05'
set @eTime ='2004-6-8 10:25:36'
set @i=0
while dateadd(minute,@i*20,@bTime)<=@eTime
begin
set @bTime=dateadd(minute,@i*20,@bTime)
insert #t select @bTime
set @i=1
end select (select top 1 [time] from track where [time]>=a.stdtime) as ptime from #t adrop table #t结果为我想得到的结果to:all再次多谢各位大哥对小弟的帮助,祝大家快乐!
declare @dt1 datetime,@dt2 datetime
select @dt1='2004-6-8 01:00:05'
,@dt2='2004-6-8 22:25:36'--查询语句
select a.*
from Track a join(
select [time]=min([time])
from Track
where [time] between @dt1 and @dt2
group by datediff(second,@dt1,[time])/1800
)b on a.[time]=b.[time]
FROM
(SELECT DATEDIFF([SECOND],'2004-6-8 01:00:05',[TIME])/1800 AS N, [TIME]
FROM Track
WHERE [TIME] BETWEEN '2004-6-8 01:00:05' AND '2004-6-8 10:25:36') MyView
GROUP BY N
ORDER BY N