厉害,受教!!rowid flag
----------- -----------
1 1
2 1
3 1
4 2
5 2
6 2
7 1
8 1如何使用sql语句得到以下结果:
beginid endid flag
1 3 1
4 6 2
7 8 1
----------- -----------
1 1
2 1
3 1
4 2
5 2
6 2
7 1
8 1如何使用sql语句得到以下结果:
beginid endid flag
1 3 1
4 6 2
7 8 1
--2.
select identity(int,1,1) as new_id,
A.id,A.flag into #t1 from t A left join t B on a.flag=B.flag and A.id=B.id-1
where B.id is null
order by A.idselect identity(int,1,1) as new_id,A.id,A.flag into #t2 from t A left join t B on a.flag=B.flag and A.id=B.id+1
where B.id is null
order by A.idselect A.id,B.id,A.flag from #t2 A,#t1 B where A.new_id=B.new_iddrop table #t1,#t2--3.我比较喜欢这方法,这方法在上贴中 zjcxc(邹建) 已经用了。
--在ID列上建CLUSTERED索引
alter table t add NewFlag int
go
declare @i int,@flag int
set @i=0
update t set @i=NewFlag=case when @flag=flag then @i else @i+1 end,@flag=flagselect min(id),max(id),min(flag)
from t group by newflag
order by newflag