SQL如何取得到连续数字中的最大累计期数
比如表a
机构数据如下
帐号 期数 状态
a 1 01
a 2 null
a 3 01
a 4 01
a 5 00
a 6 01
a 7 01
a 8 01
b 1 01
b 2 00
求期数连续，状态等于01的最大的连续的期数的和？
返回结果应该是

帐号 max
a 3
b 1


[解决办法]

SQL code

--> 测试数据: @表adeclare @表a table (帐号 varchar(1),期数 int,状态 varchar(2))insert into @表aselect 'a',1,'01' union allselect 'a',2,null union allselect 'a',3,'01' union allselect 'a',4,'01' union allselect 'a',5,'00' union allselect 'a',6,'01' union allselect 'a',7,'01' union allselect 'a',8,'01' union allselect 'b',1,'01' union allselect '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    3b    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 帐号

结果为：
a3
b1
e2

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