问下sql应该怎么写
tb 表中这样的数据
12011-09-05 00:00:01.000
22011-09-05 00:00:41.000
32011-09-05 00:00:51.000
42011-09-05 00:01:09.000
52011-09-05 00:01:39.000
想查出出样的结果
12011-09-05 00:00:01.000
22011-09-05 00:00:41.000
52011-09-05 00:01:39.000
就是从第一条开始，每大于30秒的第一条记录
我是这样写的，但这sql，会多插一条NULL,NULL进去，如何改写一下，谢谢啦
declare @tm datetime
set @tm='2011-09-05 00:00:01.000'
insert into tb1 values (1,'2011-09-05 00:00:01.000')
while @tm<='2011-09-05 00:01:39.000'
begin
insert into tb1
select min(id),min(tm) from tb where DATEDIFF(ss, @tm , tm)>30
select @tm=min(tm) from tb where DATEDIFF(ss, @tm , tm)>30
end
select * from tb1
[解决办法]
create table tb(id int,dt datetime)
insert into tb select 1,'2011-09-05 00:00:01.000'
insert into tb select 2,'2011-09-05 00:00:41.000'
insert into tb select 3,'2011-09-05 00:00:51.000'
insert into tb select 4,'2011-09-05 00:01:09.000'
insert into tb select 5,'2011-09-05 00:01:39.000'
go
select * into # from tb where id=1
while exists(select 1 from tb where datediff(s,(select max(dt) from #),dt)>30)
insert into # select top 1 * from tb where datediff(s,(select max(dt) from #),dt)>30 order by id
select * from # order by id
/*
id dt
----------- -----------------------
1 2011-09-05 00:00:01.000
2 2011-09-05 00:00:41.000
5 2011-09-05 00:01:39.000
(3 行受影响)
*/
go
drop table tb,#
