--> 测试数据: @表a
declare @表a table (帐号 varchar(1),期数 int,状态 varchar(2))
insert into @表a
select 'a',1,'01' union all
select 'a',2,null union all
select 'a',3,'01' union all
select 'a',4,'01' union all
select 'a',5,'00' union all
select 'a',6,'01' union all
select 'a',7,'01' union all
select 'a',8,'01' union all
select 'b',1,'01' union all
select 'b',2,'00'

;with m1 as
(
select 
row_number() over (partition by 帐号 order by (select 1)) as rid,
row_number() over (partition by 帐号,状态 order by (select 1)) as mid,
* from @表a
)
, m2 as ( select count(1) as cnt,帐号 from m1 group by mid-rid,帐号)
select 帐号,max(cnt) as [max] from m2 group by 帐号

/*
帐号   max
---- -----------
a    3
b    1
*/

------解决方案--------------------

;with m1 as
(
select  
row_number() over (partition by 帐号 order by 期数) as rid,
row_number() over (partition by 帐号,状态 order by 期数) as mid,
* from #a
)
, m2 as ( select count(1) as cnt,帐号 from m1 where m1.状态='01' group by mid-rid,帐号)
select 帐号,max(cnt) as [max] from m2 group by 帐号

结果为：
a	3
b	1
e	2

c,d可以用isnull(max,0)获取