日期:2014-05-18 浏览次数:20681 次
declare @tb table (id int identity, value int); insert into @tb (value) select 1 union all select 1 union all select 1 union all select 3 union all select 3 union all select 2 union all select 2 union all select 2 union all select 2 union all select 1 union all select 1 union all select 3 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all select 3; with t as ( select id,value,ROW_NUMBER() over (partition by value order by id)-id grp from @tb where value in (1,2) ) select value,COUNT(1) cnt from t group by grp,value order by value,MAX(id); /* 1 3 1 2 2 4 2 6 */
------解决方案--------------------
create table tb(ID int,v int) insert into tb select 1,1 union all select 2,1 union all select 3,1 union all select 4,3 union all select 5,3 union all select 6,2 union all select 7,2 union all select 8,2 union all select 9,2 union all select 10,1 union all select 11,1 union all select 12,3 union all select 13,2 union all select 14,2 union all select 15,2 union all select 16,2 union all select 17,2 union all select 18,2 union all select 19,3 union all select 20,3 union all select 21,3 union all select 22,3 go select identity(int,1,1) as i,id,v into #t1 from tb a where not exists(select 1 from tb where v=a.v and id=a.id-1) select identity(int,1,1) as i,id,v into #t2 from tb a where not exists(select 1 from tb where v=a.v and id=a.id+1) select a.v,b.id-a.id+1 as dlt from #t1 a inner join #t2 b on a.i=b.i where a.v<3 order by a.i go drop table tb,#t1,#t2 /* v dlt ----------- ----------- 1 3 2 4 1 2 2 6 (4 行受影响) */