if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (SJ datetime,SZ numeric(4,2))
insert into #T
select '2006-5-1 00:00:00',23.77 union all
select '2006-5-1 01:00:00',23.79 union all
select '2006-5-1 02:00:00',23.77 union all
select '2006-5-1 03:00:00',23.77 union all
select '2006-5-1 04:00:00',23.77 union all
select '2006-5-1 04:30:00',23.77 union all
select '2006-5-1 04:33:00',23.77 union all
select '2006-5-1 05:00:00',23.77 union all
select '2006-5-1 06:00:00',23.76 union all
select '2006-5-1 07:00:00',24.74 union all
select '2006-5-1 08:00:00',23.73;--> 2005
with
T1 as (select id=row_number()over(order by SJ),* from #T),
T2 as (select gid=(select isnull(max(id),0) from T1 where id<t.id and SZ<>t.SZ),* from T1 as t)
select SJ,SZ from T2 as t where id=(select min(id) from T2 where gid=t.gid) or id=(select max(id) from T2 where gid=t.gid)--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select gid=cast(null as int), id=identity(int,1,1), * into # from #T order by SJ
update # set gid=(select isnull(max(id),0) from # where id<t.id and SZ<>t.SZ) from # as t
select SJ,SZ from # as t where id=(select min(id) from # where gid=t.gid) or id=(select max(id) from # where gid=t.gid)/*
SJ SZ
----------------------- --------
2006-05-01 00:00:00.000 23.77
2006-05-01 01:00:00.000 23.79
2006-05-01 02:00:00.000 23.77
2006-05-01 05:00:00.000 23.77
2006-05-01 06:00:00.000 23.76
2006-05-01 07:00:00.000 24.74
2006-05-01 08:00:00.000 23.73
*/
create table #T (SJ datetime,SZ numeric(4,2))
insert into #T
select '2006-5-1 00:00:00',23.77 union all
select '2006-5-1 01:00:00',23.79 union all
select '2006-5-1 02:00:00',23.77 union all
select '2006-5-1 03:00:00',23.77 union all
select '2006-5-1 04:00:00',23.77 union all
select '2006-5-1 04:30:00',23.77 union all
select '2006-5-1 04:33:00',23.77 union all
select '2006-5-1 05:00:00',23.77 union all
select '2006-5-1 06:00:00',23.76 union all
select '2006-5-1 07:00:00',24.74 union all
select '2006-5-1 08:00:00',23.73;--> 2005
with
T1 as (select id=row_number()over(order by SJ),* from #T),
T2 as (select gid=(select isnull(max(id),0) from T1 where id<t.id and SZ<>t.SZ),* from T1 as t)
select SJ,SZ from T2 as t where id=(select min(id) from T2 where gid=t.gid) or id=(select max(id) from T2 where gid=t.gid)--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select gid=cast(null as int), id=identity(int,1,1), * into # from #T order by SJ
update # set gid=(select isnull(max(id),0) from # where id<t.id and SZ<>t.SZ) from # as t
select SJ,SZ from # as t where id=(select min(id) from # where gid=t.gid) or id=(select max(id) from # where gid=t.gid)/*
SJ SZ
----------------------- --------
2006-05-01 00:00:00.000 23.77
2006-05-01 01:00:00.000 23.79
2006-05-01 02:00:00.000 23.77
2006-05-01 05:00:00.000 23.77
2006-05-01 06:00:00.000 23.76
2006-05-01 07:00:00.000 24.74
2006-05-01 08:00:00.000 23.73
*/
create table #T (SJ datetime,SZ numeric(4,2))
insert into #T
select '2006-5-1 00:00:00',23.77 union all
select '2006-5-1 01:00:00',23.79 union all
select '2006-5-1 02:00:00',23.77 union all
select '2006-5-1 03:00:00',23.77 union all
select '2006-5-1 04:00:00',23.77 union all
select '2006-5-1 04:30:00',23.77 union all
select '2006-5-1 04:33:00',23.77 union all
select '2006-5-1 05:00:00',23.77 union all
select '2006-5-1 06:00:00',23.76 union all
select '2006-5-1 07:00:00',24.74 union all
select '2006-5-1 08:00:00',23.73;
go
select px = identity(int,1,1),* into # from #tselect a.*
from # a
right join # b on a.sz <> b.sz and a.px = b.px+ 1
where a.px is not null
union all
select * from (
select top 1 b.px,b.sj,b.sz
from # a
left join # b on a.sz = b.sz and a.px = b.px+ 1
where b.px is not null
order by b.sj desc) b
drop table #
if object_id('tempdb.dbo.#T') is not null drop table #T
px SJ SZ
----------- ------------------------------------------------------ ------
2 2006-05-01 01:00:00.000 23.79
3 2006-05-01 02:00:00.000 23.77
9 2006-05-01 06:00:00.000 23.76
10 2006-05-01 07:00:00.000 24.74
11 2006-05-01 08:00:00.000 23.73
7 2006-05-01 04:33:00.000 23.77(所影响的行数为 6 行)