事例数据
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
想了两天没有想通应该怎么写句子,希望高手指点.谢谢!
------解决方案--------------------
这个业务逻辑需求为啥不是跨年的？
如果只是为了实现这种效果
这样写就行
- SQL code
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,valuefrom (select (a.mouth - b.mouth) mouth_val,b.valuecte_mouth a left join CET_Table bon a.mouth -1 = b.mouth) Tgroup by value
------解决方案--------------------
- SQL code
gocreate table #tbl(name varchar(1),[month] int,value int)goinsert #tblselect '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 tas(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) agroup by name/*产品 连续销售的最大月数 连续没有销售的最大月数A 2 5B 5 3*/--听说这种方法效率不错（源自《MSSQL2008技术内幕之T-SQL查询》）