with cte_mouth(mouth) as 
(
  select 1 union all
  select 2 
  select 3 union all
  select 4
  select 5 union all
  select 6 
  select 7 union all
  select 8
  select 9 union all
  select 10 
  select 11 union all
  select 12 union all
  select 13
)
select  [连续销售的最大月数]= case when value = 1 then max(sum_val) else 0 end ,
,[连续没有销售的最大月数] = case when value = 0 then max(sum_val) else 0 end 
from(
select sum() over(partition by value,mouth_val ) sum_val,value
from (
select (a.mouth - b.mouth) mouth_val,b.value
cte_mouth a left join CET_Table b
on a.mouth -1  = b.mouth
) T
group by value

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

SQL code


go
create table #tbl(
name varchar(1),
[month] int,
value int
)
go
insert #tbl
select 'A',1,0 union all 
select 'A',2,1 union all 
select 'A',3,0 union all 
select 'A',4,0 union all 
select 'A',5,1 union all 
select 'A',6,0 union all 
select 'A',7,0 union all 
select 'A',8,0 union all 
select 'A',9,0 union all 
select 'A',10,0 union all 
select 'A',11,1 union all 
select 'A',12,1 union all 
select 'B',1,1 union all 
select 'B',2,0 union all 
select 'B',3,0 union all 
select 'B',4,1 union all 
select 'B',5,1 union all 
select 'B',6,1 union all 
select 'B',7,1 union all 
select 'B',8,1 union all 
select 'B',9,0 union all 
select 'B',10,0 union all 
select 'B',11,0 union all 
select 'B',12,1


;with t
as(
select *,
id=[month]-ROW_NUMBER()over(partition by name,value order by [month])
from #tbl
)
select name as 产品,
max(case when value=1 then [days] end) as 连续销售的最大月数,
max(case when value=0 then [days] end) as 连续没有销售的最大月数
   from(select name,value,max([month])-min([month])+1 as [days]
from t group by name,id,value) a
group by name
/*
产品    连续销售的最大月数    连续没有销售的最大月数
A    2    5
B    5    3
*/

--听说这种方法效率不错（源自《MSSQL2008技术内幕之T-SQL查询》）