有这样的数据列表
ID value
1 1
2 1
3 1
4 3
5 3
6 2
7 2
8 2
9 2
10 1
11 1
12 3
13 2
14 2
15 2
16 2
17 2
18 2
19 3
20 3
21 3
22 3
我想求出第一次连续出现1的次数和第二次连续出现2的次数，想到游标的方法，但是数据量大，耗时长，所以想求大家直接给出sql语句比较好，拜托各位大侠了
------解决方案--------------------
结果是什么样的？
------解决方案--------------------
- SQL code
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 31 22 42 6*/
------解决方案--------------------
- SQL code
create table tb(ID int,v int)insert into tb select 1,1 union all select2,1 union all select3,1 union all select4,3 union all select5,3 union all select6,2 union all select7,2 union all select8,2 union all select9,2 union all select10,1 union all select11,1 union all select12,3 union all select13,2 union all select14,2 union all select15,2 union all select16,2 union all select17,2 union all select18,2 union all select19,3 union all select20,3 union all select21,3 union all select22,3goselect 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.igodrop table tb,#t1,#t2/*v dlt----------- -----------1 32 41 22 6(4 行受影响)*/