在线等，sql求助~~~~

[带带me]

vcno period
vc001 1
vc001 2
vc001 5
vc001 6
vc001 7
vc002 4
vc004 2
vc002 6
vc005 11
数据如上 ， 取出period连续出现最大期次？
结果为
vc001 3
vc002 1
vc004 1
vc005 1

[sych888]

with t as(select 'vc001' t1,1 p1 from dual union select 'vc001',2 from dual union select 'vc001',5 from dual union select 'vc001',6 from dual union select 'vc001',7 from dual union select 'vc002',4 from dual union select 'vc004',2 from dual union select 'vc002',6 from dual union select 'vc005',11 from dual) select ttt.t1,max(cn) from (select tt.t1,count(*) cn from (SELECT t1,p1-row_number() over(partition by t1 order by p1) rn from t) tt group by tt.t1,tt.rn) ttt group by ttt.t1;

[js14982]

vc001 5 vc001 6 vc001 7 这是算是3次？ 那 vc002 的1次是怎么算出来的？

[z123zjf]

with t(v,p) as(select 'vc001',1 from dual union select 'vc001',2 from dual union select 'vc001',5 from dual union select 'vc001',6 from dual union select 'vc001',7 from dual union select 'vc002',4 from dual union select 'vc004',2 from dual union select 'vc002',6 from dual union select 'vc005',11 from dual) select v, max(cnt) from (select v, pr, count(*) cnt from (select v, p, p - row_number() over(partition by v order by p) pr from t) group by v, pr) group by v order by v