分组去掉相邻重复记录
原始数据是这样类型的
create table [tb]([code] varchar,[value] float)
insert [tb]
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 2,4 union all
select 2,4 union all
select 3,6 union all
select 3,6 union all
select 3,6 union all
select 2,4 union all
select 2,4 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 3,5 union all
select 3,5 union all
select 3,5 union all
select 2,2 union all
select 2,2 union all
with a as(
select row_number() over(order by getdate()) a,* from tb)
)
select code,value from a
where a=1
union all
select code,value from a b
where exists(select 1 from a where b.a-1=a
and b.code!=code and b.value!=value)
create table #tb([code] int,[value] float)
insert #tb
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 2,4 union all
select 2,4 union all
select 3,6 union all
select 3,6 union all
select 3,6 union all
select 2,4 union all
select 2,4 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 3,5 union all
select 3,5 union all
select 3,5 union all
select 2,2 union all
select 2,2
;with ceb as
(
select ROW_NUMBER() over(order by getdate()) as id,* from #tb
)
,ceb2 as
(
select a.code,a.value from ceb a where a.id=1
union all
select a.code,a.value from ceb a where exists
(select 1 from ceb b where a.id-1=b.id and b.code!=a.code and b.value!=a.value)
)
select * from ceb2