alter table temptb add f intdeclare @i int,@status int
set @i=0
update temptb set @i=f=(case when @status=[status] then @i else @i+1 end),@status=[status]select min(get_time) as get_time,[status] from temptb group by [status],f
set @i=0
update temptb set @i=f=(case when @status=[status] then @i else @i+1 end),@status=[status]select min(get_time) as get_time,[status] from temptb group by [status],f
1-[status] as [status]
into #t
from Tselect a.*
from T a join #t b
on a.get_time=b.statusdrop table #t
1-[status] as [status]
into #t
from Tselect a.*
from T a join #t b
on a.get_time=b.statusdrop table #t
1-[status] as [status]
into #t
from Tselect a.*
from T a join #t b
on a.get_time=b.get_time and a.status=b.status
drop table #t
create table yourtable(get_time datetime,status int)
insert into yourtable
select
'2004-03-16 01:01:00' ,0 union all select
'2004-03-16 01:02:00' ,0 union all select
'2004-03-16 01:03:00' ,0 union all select
'2004-03-16 01:04:00' ,1 union all select
'2004-03-16 01:05:00' ,1 union all select
'2004-03-16 01:06:00' ,1 union all select
'2004-03-16 01:07:00' , 1 union all select
'2004-03-16 01:08:00' , 0 union all select
'2004-03-16 01:09:00' , 0 union all select
'2004-03-16 01:10:00' , 0 union all select
'2004-03-16 01:11:00' , 1 union all select
'2004-03-16 01:12:00' , 1 union all select
'2004-03-16 01:13:00', 1--测试语句
declare @tb table (get_time datetime,status int,Ord int)
insert into @tb select *,0 from yourtabledeclare @i int,@id int
set @i=0update @tb
set @i=(case when @id=status then @i+1 else 1 end),ord=@i,@id=status select identity(int,1,1) as id ,* into #tmp from @tb where ord=1 order by get_time
select get_time, status from #tmp where id>1drop table #tmp
--结果
get_time status
------------------------------------------------------ -----------
2004-03-16 01:04:00.000 1
2004-03-16 01:08:00.000 0
2004-03-16 01:11:00.000 1(3 row(s) affected)
select *
from #t a
where status<>(
select top 1 status from #t
where get_time<a.get_time
order by get_time desc)
create table #t(get_time datetime,status bit)
insert #t select '2004-03-16 01:01:00',0
union all select '2004-03-16 01:02:00',0
union all select '2004-03-16 01:03:00',0
union all select '2004-03-16 01:04:00',1
union all select '2004-03-16 01:05:00',1
union all select '2004-03-16 01:06:00',1
union all select '2004-03-16 01:07:00',1
union all select '2004-03-16 01:08:00',0
union all select '2004-03-16 01:09:00',0
union all select '2004-03-16 01:10:00',0
union all select '2004-03-16 01:11:00',1
union all select '2004-03-16 01:12:00',1
union all select '2004-03-16 01:13:00',1
go--查询
select *
from #t a
where status<>(
select top 1 status from #t
where get_time<a.get_time
order by get_time desc)
go--删除测试
drop table #t/*--测试结果
get_time status
------------------------------------------------------ ------
2004-03-16 01:04:00.000 1
2004-03-16 01:08:00.000 0
2004-03-16 01:11:00.000 1(所影响的行数为 3 行)
--*/
select *
from #t a
where status<>(
select status from #t where get_time=(
select max(get_time) from #t where get_time<a.get_time
))
go
create table #t(get_time datetime,status bit)
insert #t select '2004-03-16 01:01:00',0
union all select '2004-03-16 01:02:00',0
union all select '2004-03-16 01:03:00',0
union all select '2004-03-16 01:04:00',1
union all select '2004-03-16 01:05:00',1
union all select '2004-03-16 01:06:00',1
union all select '2004-03-16 01:07:00',1
union all select '2004-03-16 01:08:00',0
union all select '2004-03-16 01:09:00',0
union all select '2004-03-16 01:10:00',0
union all select '2004-03-16 01:11:00',1
union all select '2004-03-16 01:12:00',1
union all select '2004-03-16 01:13:00',1
go--查询
select *
from #t a
where status<>(
select status from #t where get_time=(
select max(get_time) from #t where get_time<a.get_time
))
go--删除测试
drop table #t/*--测试结果
get_time status
------------------------------------------------------ ------
2004-03-16 01:04:00.000 1
2004-03-16 01:08:00.000 0
2004-03-16 01:11:00.000 1(所影响的行数为 3 行)
--*/
use test--准备测试环境
if object_id('t1') is not null
drop table t1create table t1 (
get_time datetime not null,
status tinyint not null
)
--放入测试数据
insert into t1 values('2004-03-16 01:01:00',0)
insert into t1 values('2004-03-16 01:02:00',0)
insert into t1 values('2004-03-16 01:03:00',0)
insert into t1 values('2004-03-16 01:04:00',1)
insert into t1 values('2004-03-16 01:05:00',1)
insert into t1 values('2004-03-16 01:06:00',1)
insert into t1 values('2004-03-16 01:07:00',1)
insert into t1 values('2004-03-16 01:08:00',0)
insert into t1 values('2004-03-16 01:09:00',0)
insert into t1 values('2004-03-16 01:10:00',1)
insert into t1 values('2004-03-16 01:11:00',0)
insert into t1 values('2004-03-16 01:12:00',0)
insert into t1 values('2004-03-16 01:13:00',0)
insert into t1 values('2004-03-16 01:14:00',1)
insert into t1 values('2004-03-16 01:15:00',1)
--测试过程
select identity(int,1,1) as id,* into t2 from t1
select b.get_time,b.status from t2 a, t2 b where a.id = b.id -1 and a.status <> b.status order by b.get_time
--结果
get_time status
--------------------------------
2004-03-16 01:04:00.000 1
2004-03-16 01:08:00.000 0
2004-03-16 01:10:00.000 1
2004-03-16 01:11:00.000 0
2004-03-16 01:14:00.000 1(5 row(s) affected)