SQL 连续销售月数

事例数据
with CET_Table as 
(
select 'A' name,1 as [month],0 as value
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
)


select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
from CET_Table A


结果:

name month value number
A 1 0 1
A 2 1 1
A 3 0 1
A 4 0 2
A 5 1 1
A 6 0 1
A 7 0 2
A 8 0 3
A 9 0 4
A 10 0 5
A 11 1 1
A 12 1 2
B 1 1 1
B 2 0 1
B 3 0 2
B 4 1 1
B 5 1 2
B 6 1 3
B 7 1 4
B 8 1 5
B 9 0 1
B 10 0 2
B 11 0 3
B 12 1 1 


 

注意:数据量大的时候,效率比较低.

 

 

select name as [产品],
max(case when value=1 then cnt else 0 end) as [连续销售的最大月数],
max(case when value=0 then cnt else 0 end) as [连续没有销售的最大月数]
from
(
select name,gid,value,count(1) as cnt from
(
select *,gid=month-(select count(*) from CET_Table where name=t.name and value=t.value and month<t.month) 
from CET_Table t
) a
group by name,gid,value
) b
group by name