连续销售的最长月数

[yiyishuitian]

事例数据
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 * from CET_Table

结果:

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


这是A和B两种产品的一年的销售情况,每个月都有, 0 代表本月份没有销售,1代表本月份有销售.

现在求,一年中连续没有销售的最大月数,和一年中连续销售的最大月数

比如A产品 连续销售的最大月数为 11月和12月,所以为 2

A产品连续没有销售的最大月数为 6月,7月,8月,9月,10月 为 5


想了两天没有想通应该怎么写句子,希望高手指点.谢谢!

[yiyishuitian]

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

学习后搞出来一个,呵呵^

[Miracle_Sky]

楼上好帅啊！呵呵呵，小弟佩服诶,在您的基础上整理一下···

/*参考楼上代码做如下整理*/ 
 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
)
,A as(
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)
,B as(
select name,max(number)'0',0 as'1' from a   where  value=0  group by name
union all
select name,0 as '-',max(number) as'-' from a   where  value=1  group by name
) 
select name 类型,SUM([0])最大连续未销售月,SUM([1])最大连续销售月 from b  group by name

[yiyishuitian]

另外一高手 百年树人 写的
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

[yhnujm##33]

望见高人，默默绕行

[you_tube]

[Quote=引用 3 楼 的回复:]

另外一高手 百年树人 写的
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,co……
[/Quote]
这么写是有相当大的漏洞在里面的
如果某个月没有录入数据或者数据只有一条，这样的话这个逻辑就是适合了

[you_tube]

这个业务逻辑需求为啥不是跨年的？
如果只是为了实现这种效果
这样写就行

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

[开着拖拉机泡妞]

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查询》）

[百年树人]

我说看着怎么这么眼熟