比如表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 帐号
结果为：
a 3
b 1
e 2
c,d可以用isnull(max,0)获取