declare @a table(a1 int,b1 int)insert @a values (2,20)
insert @a values (3,30)
insert @a values (5,0)
insert @a values (9,0)
insert @a values (10,0)
insert @a values (11,40)
insert @a values (13,0)
insert @a values (15,0)
insert @a values (16,42)select *,0 flag into # from @a
declare @1 int,@2 int,@3 int
select @2=0
update # set
@1=case when b1=0 then 0 else 1 end,
@2=case when @1=@3 then @2 else @2+1 end,
@3=@1,
flag=@2select min(a1) start,max(a1) [end] from # where b1=0 group by flag
drop table #
insert @a values (3,30)
insert @a values (5,0)
insert @a values (9,0)
insert @a values (10,0)
insert @a values (11,40)
insert @a values (13,0)
insert @a values (15,0)
insert @a values (16,42)select *,0 flag into # from @a
declare @1 int,@2 int,@3 int
select @2=0
update # set
@1=case when b1=0 then 0 else 1 end,
@2=case when @1=@3 then @2 else @2+1 end,
@3=@1,
flag=@2select min(a1) start,max(a1) [end] from # where b1=0 group by flag
drop table #
declare @a table(a1 int,b1 int)insert @a values (2,20)
insert @a values (3,30)
insert @a values (5,0)
insert @a values (9,0)
insert @a values (10,0)
insert @a values (11,40)
insert @a values (13,0)
insert @a values (15,0)
insert @a values (16,42)select *,0 flag into #T from @a
declare @1 int,@2 int,@3 int
select @2=0update #T
set @2=flag=(case when b1<> 0 then 0 else ( case @2 when 0 then a1 else @2 end) end)
--select * from #T
select min(a1) start,max(a1) [end] from #T where b1=0 group by flag
drop table #T