select a.*,identity(int,1,1) as aa into #a from a select min(a) as a,c,d from #a a where exists (select 1 from #a b where a.c=b.c and a.d=b.d and (a.aa+1=b.aa or a.aa-1=b.aa)) group by c,d union select a ,c,d from #a a where not exists (select 1 from #a b where a.c=b.c and a.d=b.d and (a.aa+1=b.aa or a.aa-1=b.aa))
drop table a go create table a(a datetime,c int,d int) insert into a select '2007/05/01 12:30',1, 2 union all select '2007/05/01 12:32',1, 2 union all select '2007/05/01 12:38',1, 2 union all select '2007/05/01 12:40',3, 1 union all select '2007/05/01 12:45',2, 2 union all select '2007/05/01 12:46',2, 2 union all select '2007/05/01 12:48',0, 2 union all select '2007/05/01 12:50',2, 2select id=identity(int,1,1),* into #t from a select a,c,d from #t a where not exists(select 1 from #t aa where a.id=aa.id+1 and a.c=aa.c and a.d=aa.d)drop table #t /* a c d ------------------------------------------------------ ----------- ----------- 2007-05-01 12:30:00.000 1 2 2007-05-01 12:40:00.000 3 1 2007-05-01 12:45:00.000 2 2 2007-05-01 12:48:00.000 0 2 2007-05-01 12:50:00.000 2 2(所影响的行数为 5 行) */
select a.*,identity(int,1,1) as aa
into #a
from a select min(a) as a,c,d
from #a a
where exists (select 1 from #a b where a.c=b.c and a.d=b.d and (a.aa+1=b.aa or a.aa-1=b.aa))
group by c,d
union
select a ,c,d
from #a a
where not exists (select 1 from #a b where a.c=b.c and a.d=b.d and (a.aa+1=b.aa or a.aa-1=b.aa))
go
create table a(a datetime,c int,d int)
insert into a
select '2007/05/01 12:30',1, 2
union all select '2007/05/01 12:32',1, 2
union all select '2007/05/01 12:38',1, 2
union all select '2007/05/01 12:40',3, 1
union all select '2007/05/01 12:45',2, 2
union all select '2007/05/01 12:46',2, 2
union all select '2007/05/01 12:48',0, 2
union all select '2007/05/01 12:50',2, 2select id=identity(int,1,1),* into #t from a select a,c,d from #t a
where not exists(select 1 from #t aa where a.id=aa.id+1 and a.c=aa.c and a.d=aa.d)drop table #t
/*
a c d
------------------------------------------------------ ----------- -----------
2007-05-01 12:30:00.000 1 2
2007-05-01 12:40:00.000 3 1
2007-05-01 12:45:00.000 2 2
2007-05-01 12:48:00.000 0 2
2007-05-01 12:50:00.000 2 2(所影响的行数为 5 行)
*/